Data Mining Model Management: Query user_mining_models View

When we build models in an Oracle database, all those models are database objects that can be queried using SQL. Thus, we can find out the critical information about the models. One of the most useful view is user_mining_models which contains the following information about models:

 MODEL_NAME
 MINING_FUNCTION
 ALGORITHM
 CREATION_DATE
 BUILD_DURATION
 MODEL_SIZE
 COMMENTS

For example, the following query shows models that I have built in my schema.

SQL> select model_name, MINING_FUNCTION, ALGORITHM from user_mining_models;

MODEL_NAME         MINING_FUNCTION                ALGORITHM
------------------ ------------------------------ ------------------------------
ABCOC1M            CLUSTERING                     O_CLUSTER
NB1021             CLASSIFICATION                 NAIVE_BAYES
SVM1029            CLASSIFICATION                 SUPPORT_VECTOR_MACHINES
AR1029             ASSOCIATION_RULES              APRIORI_ASSOCIATION_RULES
AI0929             ATTRIBUTE_IMPORTANCE           MINIMUM_DESCRIPTION_LENGTH
DT1029             CLASSIFICATION                 DECISION_TREE
GLM1031A           CLASSIFICATION                 DECISION_TREE
GLM1031B           CLASSIFICATION                 DECISION_TREE
GLM1031C           CLASSIFICATION                 DECISION_TREE
GLM1031E           CLASSIFICATION                 GENERALIZED_LINEAR_MODEL
KM1031C            CLUSTERING                     KMEANS
OC_SH_CLUS_SAMPLE  CLUSTERING                     O_CLUSTER
KM1211             CLUSTERING                     KMEANS
KM_MODEL_TRY1      CLUSTERING                     KMEANS
GLM0115            CLASSIFICATION                 GENERALIZED_LINEAR_MODEL
KM_MODEL           CLUSTERING                     KMEANS
SVD0119            FEATURE_EXTRACTION             NONNEGATIVE_MATRIX_FACTOR
TMSVD1             FEATURE_EXTRACTION             SINGULAR_VALUE_DECOMP

The following "group by" query summarize types of models that I have built.

SQL> select MINING_FUNCTION, ALGORITHM, count(*) from user_mining_models
 group by MINING_FUNCTION, ALGORITHM order by MINING_FUNCTION, ALGORITHM;

MINING_FUNCTION                ALGORITHM                        COUNT(*)
------------------------------ ------------------------------ ----------
ASSOCIATION_RULES              APRIORI_ASSOCIATION_RULES               1
ATTRIBUTE_IMPORTANCE           MINIMUM_DESCRIPTION_LENGTH              1
CLASSIFICATION                 DECISION_TREE                           4
CLASSIFICATION                 GENERALIZED_LINEAR_MODEL                2
CLASSIFICATION                 NAIVE_BAYES                             1
CLASSIFICATION                 SUPPORT_VECTOR_MACHINES                 1
CLUSTERING                     KMEANS                                  4
CLUSTERING                     O_CLUSTER                               2
FEATURE_EXTRACTION             NONNEGATIVE_MATRIX_FACTOR               1
FEATURE_EXTRACTION             SINGULAR_VALUE_DECOMP                   1

In summary, models are database objects that can be queried using SQL. This provides an efficient ways to manage many models in our database.We can retrieve our models by names, mining functions (classification, regression, etc.), algorithms, build date, comments, etc.