Oracle Data Mining – from SQL and PL/SQL


During one of the sessions on last week’s ODTUG 2006 conference, I saw a very interesting demo by Keith Laker. He discussed the problem of having several dozens of dimensions or descriptors for a particular fact and utter uselessness of a report with more than 40 or 50 qualifiers. Like an overview of salesrecords with per customer information such as name, birthdate, gender, zipcode, number of pets, political affiliation, job, health history, favorite author et. Each one of these pieces of information may be meaningful, but when all presented in a report, it becomes impossible to extract meaningful information. So he suggested an approach for determining which of all these qualifiers are the most useful ones and only including those in the report.....

Using Oracle Data Mining PL/SQL API – embedded in the Oracle 10gR2 database – we can easily find out which dimensions are important ones. In his demonstration, he extracted the top 5 attributes from a set of over 50. These top five together determined the fact for more than 95 % and therefore the analysis could focus on only these attributes.

The PL/SQL package to use is called DBMS_PREDICTIVE_ANALYTICS. An introduction can be found here. You can the EXPLAIN procedure the name of a Table or View and the name of a target column – the column (fact) whose value you are interested in and for which you would like to know which of the other columns have the largest determining contribution. The results of the EXPLAIN are written to a results table:

column_name               VARCHAR2(30)
explanatory_value NUMBER

From this table, we can easily find the columns with the largest contribution in determining the target column’s value. 

For a very accessible introduction to the use of Data Mining from simple SQL queries, read Marcos Campos’ great article: The SQL of Analytics 1 – Data Mining. He shows first how existing Data Mining Models can easily be incorporated into normal SQL queries using the PREDICTION_PROBABILITY operator. With this operator, we can apply a pre-created Data Mining Model to every record evaluated in the SQL query and filter it based on the result of applying the model. He continues to demonstrate how the Data Mining model itself can be created, using pretty straightforward PL/SQL APIs. Data Mining can easily be included in our ‘normal’ applications to further refine our queries and support decision processes.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

Comments are closed.