Oracle database query

Predictive Queries in Oracle Database 12c
October 27, 2022 – 09:10 am
Oracle SQL Profiler - Oracle Query Optimization Tool

PQFigure1By Brendan Tierney, Oracle Ace Director


One of the new SQL features introduced into the Oracle 12c Database is the ability to create on the fly predictive models for your data. All you need to do is to write a SQL query that will build a predictive model and will then apply this model to your data. This is all done in one step and does not require you have knowledge of its inner works. The predictive models that are built during the execution of the Predictive Query only exists while the query is being run. When the query is finished all the models and associated settings are deleted. These are called transient models.

One of the confusing aspects of the new feature is that is goes by many names. In the documentation it can be found under On-the-Fly Models, and sometimes it is called Dynamic Queries. This feature is also part of the Oracle Data Miner tool that is part of SQL and here it goes by the name of Predictive Queries.

On-the-Fly Models = Dynamic Queries = Predictive Queries. Yes that is a bit confusing. From talking to some of the Oracle folks is seems that the name Predictive Queries might be the name they will be using going forward.

What are Predictive Queries And Why Would You Need Them?

Predictive Queries enable you to build and score data quickly using the in-database data mining algorithms, without the complexity of needing to understand the required settings and fine-tuning of the models. All models created during the execution of the Predictive Query will not exist once the Predictive Query is finished executing. You cannot inspect or tune the algorithms being used or the models that are generated. So you have to trust what Oracle is doing behind the scenes. If you are a Data Scientist type of person you will typically want to tune the models, so this approach is maybe not for you. But if you want to very quickly build models and score your data then Predictive Queries is something you should consider.

PQFigure2A major advantage of using Predictive Queries is that you can partition the data so that predictive models can be build specific to each partition. What this will do, is that it will divide your data into the relevant partitions and will then create a predictive model specific to that partition and then score the data in the partition. Typically for most data mining tools you will have to specifically define the creation of the data subset for the partition, define how to build the model and then run the model to score the data. All of these steps have to be manually defined. By defining a partition in the Predictive Query all of these work will be done automatically for you. There are two major advantages to this. The first is that as new data partitions (i.e. a new value exists for the partition attribute) are created the Predictive Query will automatically pick this up and do all the work for you. The second is that Predictive Queries allows you to use the Parallel Query option to speed up the process of scoring the data. This will be particularly useful when you are working with Big Data.

Using Predictive Queries is very similar to using the PREDICT procedure in the DMBS_PREDICTIVE_ANALYTICS PL/SQL package, except that instead of using PL/SQL, Predictive Queries uses SQL.

PQFigure3There are two ways you can go about creating your predictive queries. The first option is to write a SQL statement to prompt the database to use the in-database data mining algorithms. The second (and much easier) option is to use the Predictive Queries nodes in the Oracle Data Miner tool.

The following sections of this article will illustrate how to build Predictive Queries using Oracle Data Miner and using SQL.

Building Predictive Queries Using Oracle Data Miner

The Predictive Queries option in the Oracle Data Miner GUI, that is part of SQL Developer, will only become visible when you are connected to an Oracle 12c Database. You will see the Predictive Queries section under the Components windows, as shown in Figure 1.

Figure 1 : Predictive Queries section of the Components Window

There are four types of Predictive Query Nodes available in Oracle Data Miner.

Predictive Queries Node


Anomaly Detection Node

The input data set will be used to build an anomaly model and this model will then be applied to the same input data set.

Clustering Node

PQFigure4Clusters will be automatically detected and the input data set will be returned with an assigned cluster for each record.

Feature Extraction Query

This can be used to automatically identify and extract key features from the input data set.

Prediction Query

The Predictive Query node can be used to perform classification or regression on the input data set. For classification the target attribute needs to have a character data type. For Regression the data type should be numeric.

To illustrate how to build and use a Predictive Query node in the Oracle Data Miner tool, the following example will use the Predictive Query node to build and apply a Classification model.

To create a Data Source and Predictive Query nodes on your workflow you need to:

1. Create and Define a Data Source Node for the table that contains the data. The Data Source Node is located in the Data section of the Components window.

2. Create the Predictive Query node on your workflow.

3. Connect the Data Source node to the Predictive Query node. To do this right click on the Data Source node, select Connect from the menu, move the mouse to the Predictive Query node and click the mouse again. A gray arrow will now appear between the two nodes. This means they are connected.

To define the settings for the Predictive Query node you will need to:

1. Open the Predictive Query node by double clicking on the node.

2. The Edit Predictive Query node window will open. This window have 4 tabs/sections that allows you to define the Predictions to be made, how the data will be Partitioned (and hence the number of Predictive Query models that will be created), the Input list of attributes and the Output list of attributes.

3. Define the Predictions.

a) Define the Case ID for the input data set. The Case ID can be the primary key or some attribute that gives uniqueness to each attribute. The Case ID can be left null, but by defining the Case ID allows for the repeatability of the process and outputs. In our sample data set the Case ID is CUST_ID.

b) Define the target attribute for the data set. Click on the green plus icon to open the Add Target window. In this window select the required Target attribute. In our data set this is the AFFINITY_CARD attribute.

c) Define the Data Mining Type. This will be either Classification or Regression. To define the Data Mining Type you will need to click on the icon under the Data Mining Type column. A drop down list will appear that lists Categorical or Numerical. In our scenario we want to perform Classification so you will need to select Categorical.

What is the meaning of izuku? How to get cancer? how to call a helper function inside of another function Oh what a night lyrics meaning? How to take care of a bamboo plant? How to drop an engine into a car tips? Safety tips when building a computer? What are delts? What language does hungary speak? What is the world's record for the longest time someone has held their breath underwater? How to transfer photos from iphone to iphone? Tips on how can we reduce text in a powerpoint presentation? How to take off acrylics without acetone? What does variation mean in skateboarding tricks? How to win mancala? What is the meaning of hydrology? What are dude wipes? What does pos stand for? Tricks on how to orgasm quick? What does 999 mean? How to make cilantro lime rice? How to bet on horses? What are arteries? How effective are exhaust tips? Negotiating tips and tricks when buying a new car? How to kiss a boy? What is a z pack? At what age do fingerprints wear off tips? How to get skateboard tricks to suck up? What does hbd mean in text? What does the golden buzzer mean on america's got talent? What does ave maria mean? What is the meaning of bee? What does preceding mean? What does lucifer look like? How to play total annihilation tips? Where do you find meaning in your life? What is the meaning of modernity? How to do pen tricks soni? What does upbeat mean? How to use google calendar? How not to die? What is the meaning of inadmissible? What is the spiritual meaning of seeing 1010? What are the basic units of the metric system? What is the average penis size? How to treat pleurisy? What does the clown emoji mean? How to whitewash wood? What is the meaning of 501 c 3? How to edit text in pdf? What are reactants? What does ear infection look like? How to tell if someone is scamming you online? What are the differences? What is the meaning of the root tele? What does beau mean? What is a fault? What is a covid cough like? How to make hair bows? What are ginger shots good for? What does psa mean in text? How long does it take to boil potatoes? what is the sun microsystems incorporated ssv helper class How to tighten skin? when is community helper week 2017 Why are the tips of my big toe numb? How to tell if your dog has fleas? What are blessings? How to make your snap score go up? What does misogynist mean? How to get rid of dog gland smell? How to get rid of hickeys? What does meta stand for? What does 💕 mean from a girl? How to know if your water broke? What does 2x mean? What does rickrolled mean? How to grow your hair faster men? What does revere mean? What does jigga boo mean? What are you sinking about? How to make a loading page css css tricks? How to build a fire pit? What does indited mean? What does cabron mean in spanish slang? What is the meaning of raged? How to block email? What are power chains? What does grwm stand for? What is the meaning of tbt on instagram? How to stop coughing fits? How to make homemade cornbread? How to treat a spider bite? What are the odds game? How to heal shin splints fast? Tips for counseling a couple where there is an alcohol problem? How to write a persuasive essay? What does mishka mean? movie about students who write to anne frank's helper What does coughing up blood mean? What is the meaning of k in money? what are helper molecules What does it mean to die of consumption? What does hydrangea look like in winter? Tips for how to make my wife squirt? What is the most common birthday? Hummingbird meaning when you see one? What is the meaning of friendship to you? dragalia lost why use helper What does it mean when you dream about water? Where to buy a shakespeare 7 foot spinning rod tips? How to get a deeper voice? How to do dipping powder nails with tips? What are nootropics? What does coalition mean? Who has rick lax made tricks for? How to make a slideshow with music and pictures for free? Tips on how to not overeat? What are the skiis with both cuved up tips called? Tips for behavioral economics when writing a paper? What do you do for a living meaning? What does enable cash card mean? What is a trans man? What is telemetry? freecodecamp how to call a helper function What does deus ex machina mean? What are examples of values? Tips for how to manage large groups? How to get rid of powdery mildew? handlebars how to register helper How to cancel dashpass? What does dvd stand for? What are corrective lenses? How to store mushrooms in fridge? What does ily mean on snapchat? Tips on how to play the division? How are tips taxed? How to watch paw patrol movie? How to cook beet greens? Why does my left ear ring spiritual meaning? What does purpose mean? How to recover deleted photos? What does shallow mean? How to play mahjong tips? What does mijo mean in english? What is the meaning of decline in hindi? How to use discord? What is terminal velocity? What does variance mean in statistics? what is slack helper? What is the meaning of total eclipse of the heart? How to go from blonde to dark with blonde tips? What is the meaning of thank you letter? What does zealous mean? Tricks to hold babies free hand when breast feeding football position? How to create an event on facebook? How to make extra money fast? What does revolving mean? How to draw a horse head? Ttp:// What is the meaning of i do? how to install plexbmc helper 17.3 Reddit how to start doing skate tricks? What is arugula? How to break a fever? How to cook tortellini? Tips on how to quit alcohol? What does arthritis in the spine look like? What does ferrous mean? What does abode mean? how to set input type to html helper How to get snap streak back? What is the meaning of hilarious in urdu? Tips on how to study for vocabulary part for hesi exam? How to stop crackling in ears? What is latinx? What is the song zombie by the cranberries meaning? Tips for bringing home a rescue dog when you have a cat? What does name on card mean? what is the helper verb in portuguese perfect tenses What does mean in r? What does 007 mean? How do i remove acrylic tips? What is logic? How meaning in texting? What causes temporary purple color on tips of fingers? What is ssid mean? What is the meaning of foi? What is the meaning of incontinence? I meant how to do tricks the other word? How to make love like an englishman? How much tips do you have to claim? How to screenshot on chrome? What is the meaning of marigolds? How to wash jeans? Tips for how to choose good clothes everyday? How to die painlessly? How to be a stripper? flywithlua how to use vfr autopilot helper How to block a call? What does ne14abj meaning? What are some tips for add as an adult? What length tips for circular knitting needles? What time does nathan chen skate today? What is the meaning of masquerade? How to become a real estate developer? What are mennonites? what is xcode helper What does age of consent mean? how to remove macleane 4 helper from mac How to increase volume on iphone? Tricks on how to get dust out carved areas on furniture? What country does tips? How to make facial hair grow faster? What does dreaming about snakes mean? How to wire a doorbell? Tips on how to make yourself sleep on shrooms? What does the praying mantis symbolize? What does ~ mean? When the music's over meaning? What time does bass pro close? How can a person know everything at 18 meaning? What does sigma mean? what is the helper of a superhero called What is the full meaning of news? How to clean microwave? Who sings the theme to new tricks? What are shingles for roof? What is the meaning of independent variables in science? How to get a replacement birth certificate? What to expect from middle school to high school tips for teenagers? What does cling mean? How to bathe a cat? How to find porn on youtube? How to stain a fence? What does it mean when you cry while making love? What are the tips of a fork called? How to remove windows 8.1 tips? how to call highway helper mn How much could i get for 2007 corvette exhaust pipes and mufflers and chrome tips used? What does it mean when imessage doesn t say delivered? What are the symptoms of 2 3 weeks pregnant? What is the meaning of somnambulism? What does a crematory look like? what is the purpose of a recursive helper method How to get honey in minecraft? How to connect apple watch? How to putt tips? How long do gel tips last? What does a silverfish look like? How to cook a pork shoulder? Tricks to getting your 11 month old to sleep when you put them down in the crib? How to teach a goat tricks for beginners? What is the meaning of pus? How to catch fruit flies? What is the hebrew meaning of yahweh? How to delay your period? What is meaning of auspicious? How to get your w2 online? How to monetize instagram? What are the three rivers in pittsburgh? What is the darknet meaning? How to root red tips? What does the term pneumatic tires mean? What does tnr mean? What does lt mean on a car? Yale resume tips for when you are creating a job for yourself?
Related Posts