How to Score Without a Saved Model in Oracle (dynamic scoring)

Building, storing, and maintaining a model is worth it for predictions you make every day. For a quick question, Oracle Machine Learning functions also have an analytic form: with FOR target and an OVER clause, the query builds a temporary model from its own rows, scores them, and discards the model when it ends.

Code for This Guide

The main examples are in the examples/machine-learning folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

prediction(for target_column using col1, col2, ...) over ()
prediction_probability(for target_column using ...) over ()
cluster_id(into n using ...) over ()
feature_id(into n using ...) over ()

OVER () uses all rows of the query; OVER (PARTITION BY ...) builds a separate model per partition.

The example uses the ML_TICKETS view from How to Explain Predictions with PREDICTION_DETAILS in Oracle.

Predict the Cabin from Three Columns

Example:

-- no model: the analytic form builds a temporary model for the query
select ticket_id, cabin,
       prediction(for cabin using tier, days_ahead, distance_km) over () as predicted
from   ml_tickets
where  ticket_id in (10, 395, 765, 850);

Output:

   TICKET_ID CABIN       PREDICTED
____________ ___________ ____________
          10 ECONOMY     ECONOMY
         850 BUSINESS    BUSINESS
         765 BUSINESS    BUSINESS
         395 ECONOMY     ECONOMY

No model is named: the query trains one on the tickets it reads, from tier, days ahead, and distance, and predicts the cabin of four tickets. Here all four predictions match the actual cabin, including 395 and 850, which the stored decision tree got wrong.

Things to Know

• The model is built from the rows the query reads after its WHERE clause, so filter carefully: here it trained on only four rows and simply learned them.
• Dynamic models are rebuilt on every run, so they suit ad hoc analysis, not production scoring of many rows.
• Rows with a NULL target are scored but not used for training, which lets one query train on known rows and predict unknown ones.

Related Guides

• How to Explain Predictions with PREDICTION_DETAILS in Oracle
• How to Detect Anomalies with One-Class SVM in Oracle

Conclusion

The analytic forms of PREDICTION, CLUSTER_ID, and FEATURE_ID build a temporary model inside a query. Use them for quick, ad hoc analysis, and train stored models for predictions you rely on.