/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 5
ORACLE MACHINE LEARNING (OML) FOUNDATIONS
=================================================================
Oracle Machine Learning (OML) allows machine learning models
to be created directly inside the Oracle database.
Advantages
β’ No data movement outside the database
β’ Highly scalable for large datasets
β’ Uses SQL and PL/SQL
β’ Integrated with Oracle Autonomous Database
Main Components
1 DBMS_DATA_MINING package
2 SQL prediction functions
3 Model training tables
4 Model application queries
Common Mining Functions
CLASSIFICATION
REGRESSION
CLUSTERING
ANOMALY DETECTION
ASSOCIATION RULES
TIME SERIES
===============================================================*/
/*===============================================================
SECTION 1
VIEW AVAILABLE DATA MINING ALGORITHMS
===============================================================*/
SELECT *
FROM ALL_MINING_ALGORITHMS;
/*===============================================================
SECTION 2
MODEL SETTINGS TABLE
===============================================================
Many Oracle ML models require a settings table.
This table defines which algorithm is used.
===============================================================*/
DROP TABLE dt_settings PURGE;
CREATE TABLE dt_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
/*===============================================================
DECISION TREE ALGORITHM SETTING
===============================================================*/
INSERT INTO dt_settings VALUES
(
'ALGO_NAME',
'ALGO_DECISION_TREE'
);
COMMIT;
/*===============================================================
SECTION 3
TRAINING DATASET
===============================================================
We will use customer_data created in PART 1.
Goal
Predict whether customer subscribes
to a service.
Columns
customer_id
age
income
subscribed (target column)
===============================================================*/
SELECT * FROM customer_data;
/*===============================================================
SECTION 4
DROP MODEL IF EXISTS
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('CUSTOMER_SUBSCRIPTION_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
SECTION 5
CREATE MACHINE LEARNING MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'CUSTOMER_SUBSCRIPTION_MODEL',
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => 'CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
target_column_name => 'SUBSCRIBED',
settings_table_name => 'DT_SETTINGS'
);
END;
/
/*===============================================================
VERIFY MODEL CREATION
===============================================================*/
SELECT model_name,
mining_function,
algorithm
FROM user_mining_models;
/*===============================================================
SECTION 6
MODEL DETAILS
===============================================================*/
SELECT *
FROM user_mining_models;
/*===============================================================
SECTION 7
PREDICT USING MODEL
===============================================================
Oracle provides SQL prediction functions.
PREDICTION
PREDICTION_PROBABILITY
PREDICTION_SET
===============================================================*/
SELECT
customer_id,
age,
income,
PREDICTION
(
CUSTOMER_SUBSCRIPTION_MODEL
USING age,income
) AS predicted_subscription
FROM new_customer_data;
/*===============================================================
SECTION 8
PREDICTION PROBABILITY
===============================================================*/
SELECT
customer_id,
PREDICTION_PROBABILITY
(
CUSTOMER_SUBSCRIPTION_MODEL,
'YES'
USING age,income
) AS probability_yes
FROM new_customer_data;
/*===============================================================
SECTION 9
PREDICTION DETAILS
===============================================================*/
SELECT
customer_id,
PREDICTION_SET
(
CUSTOMER_SUBSCRIPTION_MODEL
USING age,income
) prediction_details
FROM new_customer_data;
/*===============================================================
SECTION 10
MODEL ATTRIBUTE IMPORTANCE
===============================================================
Shows which attributes influence predictions.
===============================================================*/
SELECT *
FROM TABLE
(
DBMS_DATA_MINING.GET_MODEL_DETAILS_DT
('CUSTOMER_SUBSCRIPTION_MODEL')
);
/*===============================================================
SECTION 11
MODEL METADATA
===============================================================*/
SELECT *
FROM USER_MINING_MODEL_ATTRIBUTES
WHERE MODEL_NAME='CUSTOMER_SUBSCRIPTION_MODEL';
/*===============================================================
SECTION 12
DROP MODEL (OPTIONAL)
===============================================================*/
-- BEGIN
-- DBMS_DATA_MINING.DROP_MODEL('CUSTOMER_SUBSCRIPTION_MODEL');
-- END;
-- /
/*===============================================================
END OF PART 5
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/