How to Explain Predictions with PREDICTION_DETAILS in Oracle

Oracle Machine Learning builds models inside the database and scores rows with SQL functions such as PREDICTION. A prediction is more useful when you know why it was made. PREDICTION_DETAILS returns, for one row, the attributes that weighed most in its prediction, with their values and weights, so a result can be explained to the people who act on it.

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_details(model [, class_value [, top_n]] using *)    -- returns XML
prediction(model using *)
prediction_probability(model [, class_value] using *)

USING * passes every column of the row to the model by name. Building models needs the CREATE MINING MODEL privilege.

Training Data and a Model

ML_TICKETS describes each ticket with the traveller's loyalty tier and points, how many days ahead it was booked, the distance, the number of legs, and the traveller's age. The target is the cabin.

Example:

create or replace view ml_tickets as
select t.ticket_id,
       t.cabin,
       nvl(json_value(c.loyalty, '$.tier'), 'None')              as tier,
       nvl(json_value(c.loyalty, '$.points' returning number), 0) as points,
       trunc(cast(f.scheduled_departure as date) - cast(b.booked_at as date)) as days_ahead,
       r.distance_km,
       (select count(*) from tickets t2 where t2.booking_id = t.booking_id) as legs,
       trunc(months_between(date '2026-03-15', c.date_of_birth) / 12)   as age
from   tickets t
join   bookings b  on b.booking_id = t.booking_id
join   customers c on c.customer_id = b.customer_id
join   flights f   on f.flight_id = t.flight_id
join   routes r    on r.route_id = f.route_id;

select * from ml_tickets where ticket_id <= 4;

Output:

View ML_TICKETS created.

   TICKET_ID CABIN      TIER       POINTS    DAYS_AHEAD    DISTANCE_KM    LEGS    AGE
____________ __________ _______ _________ _____________ ______________ _______ ______
           1 ECONOMY    Blue         3303            74           4844       1     56
           4 ECONOMY    Blue         3296            65           7979       1     54
           3 ECONOMY    Blue         3694            86           1137       2     30
           2 ECONOMY    Blue         3694            68           1137       2     30

A decision tree learns to predict the cabin from four in five tickets; the fifth, with ticket IDs divisible by 5, is kept for testing.

Example:

declare
  v_settings dbms_data_mining.setting_list;
begin
  v_settings('ALGO_NAME')     := 'ALGO_DECISION_TREE';
  v_settings('PREP_AUTO')     := 'ON';
  v_settings('TREE_TERM_MAX_DEPTH') := '5';
  dbms_data_mining.create_model2(
    model_name          => 'CABIN_CLASS',
    mining_function     => 'CLASSIFICATION',
    data_query          => 'select * from ml_tickets where mod(ticket_id, 5) <> 0',
    set_list            => v_settings,
    case_id_column_name => 'TICKET_ID',
    target_column_name  => 'CABIN');
end;
/
select model_name, mining_function, algorithm, build_duration, model_size
from   user_mining_models
where  model_name = 'CABIN_CLASS';

Output:

PL/SQL procedure successfully completed.

MODEL_NAME     MINING_FUNCTION    ALGORITHM           BUILD_DURATION    MODEL_SIZE
______________ __________________ ________________ _________________ _____________
CABIN_CLASS    CLASSIFICATION     DECISION_TREE                    1           829

Predict and Score

Example:

select ticket_id, tier, days_ahead, cabin as actual,
       prediction(cabin_class using *)                      as predicted,
       round(prediction_probability(cabin_class using *), 3) as probability,
       round(prediction_probability(cabin_class, 'BUSINESS' using *), 3) as p_business
from   ml_tickets
where  ticket_id in (10, 395, 540, 765, 850, 1005)
order  by ticket_id;

Output:

   TICKET_ID TIER           DAYS_AHEAD ACTUAL      PREDICTED       PROBABILITY    P_BUSINESS
____________ ___________ _____________ ___________ ____________ ______________ _____________
          10 Silver                 72 ECONOMY     ECONOMY                   1             0
         395 Platinum               17 ECONOMY     BUSINESS               0.83          0.83
         540 Silver                  3 BUSINESS    ECONOMY               0.723         0.277
         765 Platinum               20 BUSINESS    BUSINESS               0.83          0.83
         850 Platinum               26 BUSINESS    ECONOMY               0.769         0.231
        1005 Gold                    5 ECONOMY     BUSINESS               0.83          0.83

6 rows selected.

PREDICTION returns the most likely cabin and PREDICTION_PROBABILITY its probability, or the probability of a named class. Tickets 540 and 850 were business but predicted economy, and 395 and 1005 the other way round.

Why Business?

Example:

select ticket_id,
       xmlserialize(content prediction_details(cabin_class, 'BUSINESS', 2 using *) indent)
         as details
from   ml_tickets
where  ticket_id = 765;

Output:

   TICKET_ID DETAILS
____________ ___________________________________________________________________________
         765 <Details algorithm="Decision Tree" class="BUSINESS" node="5">
               <Attribute name="DAYS_AHEAD" actualValue="20" weight=".29" rank="1"/>
               <Attribute name="POINTS" actualValue="179303" weight=".278" rank="2"/>
             </Details>

For ticket 765, the tree predicts business mainly because the ticket was booked only 20 days ahead, and second because of the traveller's 179,303 loyalty points. Each attribute has a weight and a rank.

The Top Reason as a Column

Example:

-- the single strongest reason for each business prediction, as columns
select ticket_id,
       xmlcast(xmlquery('/Details/Attribute[1]/@name'
                        passing prediction_details(cabin_class using *)
                        returning content) as varchar2(30)) as top_reason
from   ml_tickets
where  ticket_id in (395, 765, 1005);

Output:

   TICKET_ID TOP_REASON
____________ _____________
         395 DAYS_AHEAD
         765 DAYS_AHEAD
        1005 DAYS_AHEAD

XMLQUERY reads the name of the first attribute in the details, so the top reason becomes an ordinary column for each predicted ticket. The example builds its own copy of the view and model and drops them afterward.

Things to Know

• The view ML_TICKETS and the model CABIN_CLASS are reused in other machine learning guides; drop them with DBMS_DATA_MINING.DROP_MODEL('CABIN_CLASS') and DROP VIEW when done.
• Weights are relative to the prediction for that row; compare ranks within a row, not weights across rows.
• PREDICTION_DETAILS works for classification, regression, and anomaly detection models.

Related Guides

• How to Use Machine Learning on Embeddings in Oracle Database
• Oracle XMLCAST Function

Conclusion

PREDICTION_DETAILS explains each prediction of an Oracle Machine Learning model with the attributes that mattered most, their values, and weights. Return it as XML, or pull out the top reasons as columns, to show why a row got its prediction.