A model is only as good as its predictions on data it has not seen. Evaluating a classification model means scoring held-out rows and comparing predictions with the truth. DBMS_DATA_MINING computes the standard measures: COMPUTE_CONFUSION_MATRIX counts right and wrong predictions per class and returns the accuracy, and COMPUTE_ROC returns the area under the ROC curve.
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
dbms_data_mining.apply(model, data_table, case_id_column, result_table); dbms_data_mining.compute_confusion_matrix(accuracy_out, apply_result_table_name, target_table_name, case_id_column_name, target_column_name, confusion_matrix_table_name, score_column_name, score_criterion_column_name); dbms_data_mining.compute_roc(roc_area_under_curve_out, ..., positive_target_value, ...);
The examples use the ML_TICKETS view and the CABIN_CLASS model from How to Explain Predictions with PREDICTION_DETAILS in Oracle. The model was trained without the tickets whose IDs are divisible by 5, which are kept for testing.
A Confusion Matrix in Plain SQL
Example:
-- the tickets that the model did not see: ticket_id divisible by 5 select cabin as actual, prediction(cabin_class using *) as predicted, count(*) as tickets from ml_tickets where mod(ticket_id, 5) = 0 group by cabin, prediction(cabin_class using *) order by 1, 2;
Output:
ACTUAL PREDICTED TICKETS ___________ ____________ __________ BUSINESS BUSINESS 33 BUSINESS ECONOMY 13 ECONOMY BUSINESS 9 ECONOMY ECONOMY 158
Grouping actual against predicted cabin gives the confusion matrix directly: 33 and 158 tickets are right, 13 business tickets were predicted economy, and 9 economy tickets business.
Accuracy and ROC with DBMS_DATA_MINING
Example:
exec dbms_data_mining.apply('CABIN_CLASS', 'TEST_TICKETS', 'TICKET_ID', 'TEST_SCORES')
declare
v_accuracy number;
v_auc number;
begin
dbms_data_mining.compute_confusion_matrix(
accuracy => v_accuracy,
apply_result_table_name => 'TEST_SCORES',
target_table_name => 'TEST_TICKETS',
case_id_column_name => 'TICKET_ID',
target_column_name => 'CABIN',
confusion_matrix_table_name => 'TEST_MATRIX',
score_column_name => 'PREDICTION',
score_criterion_column_name => 'PROBABILITY');
dbms_data_mining.compute_roc(
roc_area_under_curve => v_auc,
apply_result_table_name => 'TEST_SCORES',
target_table_name => 'TEST_TICKETS',
case_id_column_name => 'TICKET_ID',
target_column_name => 'CABIN',
roc_table_name => 'TEST_ROC',
positive_target_value => 'BUSINESS',
score_column_name => 'PREDICTION',
score_criterion_column_name => 'PROBABILITY');
dbms_output.put_line('Accuracy: ' || round(v_accuracy, 3)
|| ', area under ROC: ' || round(v_auc, 3));
end;
/
select actual_target_value, predicted_target_value, value from test_matrix order by 1, 2;Output:
PL/SQL procedure successfully completed. Accuracy: .897, area under ROC: .915 PL/SQL procedure successfully completed. ACTUAL_TARGET_VALUE PREDICTED_TARGET_VALUE VALUE ______________________ _________________________ ________ BUSINESS BUSINESS 33 BUSINESS ECONOMY 13 ECONOMY BUSINESS 9 ECONOMY ECONOMY 158
APPLY scores the test tickets into TEST_SCORES. The confusion matrix matches the SQL one, with an accuracy of 0.897, 191 of 213 right. The area under the ROC curve, 0.915, measures how well the business probability ranks business tickets above economy ones, regardless of any cutoff. The example drops its tables and view afterward.
Things to Know
- Always evaluate on rows the model did not train on, or the numbers look better than reality.
- With uneven classes, accuracy alone misleads; look at the errors per class in the matrix.
- COMPUTE_LIFT gives the lift chart, another way to judge how well a model ranks positive cases.
Related Guides
- How to Use Cost-Sensitive Predictions in Oracle (cost matrix)
- How to Get All Class Probabilities with PREDICTION_SET
Conclusion
Evaluate classification models on held-out rows: score them with APPLY, then compute the confusion matrix and accuracy, and the area under the ROC curve. Read the errors per class, not just the overall accuracy.
