In my last post, we set the groundwork for extracting specific tokens from a white list using a CTXRULE index. In this post, we will populate a table with the extracted tokens and produce a case table suitable for clustering with Oracle Data Mining.

Our corpus of documents will be stored in a database table that is defined as

create table documents(id NUMBER, text VARCHAR2(4000));

However, any suitable Oracle Text-accepted data type can be used for the text.

We then create a table to contain the extracted tokens. The id column contains the unique identifier (or case id) of the document. The token column contains the extracted token. Note that a given document many have many tokens, so there will be one row per token for a given document.

create table extracted_tokens (id NUMBER, token VARCHAR2(4000));

The next step is to iterate over the documents and extract the matching tokens using the index and insert them into our token table. We use the MATCHES function for matching the query_string from my_thesaurus_rules with the text.

DECLARE
    cursor c2 is
      select id, text
      from documents;
BEGIN
    for r_c2 in c2 loop
       insert into extracted_tokens
         select r_c2.id id, main_term token
         from my_thesaurus_rules
         where matches(query_string,
                       r_c2.text)>0;
    end loop;
END;

Now that we have the tokens, we can compute the term frequency - inverse document frequency (TF-IDF) for each token of each document.

create table extracted_tokens_tfidf as
  with num_docs as (select count(distinct id) doc_cnt
                    from extracted_tokens),
       tf       as (select a.id, a.token,
                           a.token_cnt/b.num_tokens token_freq
                    from 
                      (select id, token, count(*) token_cnt
                       from extracted_tokens
                       group by id, token) a,
                      (select id, count(*) num_tokens
                       from extracted_tokens
                       group by id) b
                    where a.id=b.id),
       doc_freq as (select token, count(*) overall_token_cnt
                    from extracted_tokens
                    group by token)
  select tf.id, tf.token,
         token_freq * ln(doc_cnt/df.overall_token_cnt) tf_idf
  from num_docs,
       tf,
       doc_freq df
  where df.token=tf.token;

From the WITH clause, the num_docs query simply counts the number of documents in the corpus. The tf query computes the term (token) frequency by computing the number of times each token appears in a document and divides that by the number of tokens found in the document. The doc_req query counts the number of times the token appears overall in the corpus. In the SELECT clause, we compute the tf_idf.

Next, we create the nested table required to produce one record per case, where a case corresponds to an individual document. Here, we COLLECT all the tokens for a given document into the nested column extracted_tokens_tfidf_1.

CREATE TABLE extracted_tokens_tfidf_nt
             NESTED TABLE extracted_tokens_tfidf_1
                 STORE AS extracted_tokens_tfidf_tab AS
             select id,
                    cast(collect(DM_NESTED_NUMERICAL(token,tf_idf)) as DM_NESTED_NUMERICALS) extracted_tokens_tfidf_1
             from extracted_tokens_tfidf
             group by id;
 
To build the clustering model, we create a settings table and then insert the various settings. Most notable are the number of clusters (20), using cosine distance which is better for text, turning off auto data preparation since the values are ready for mining, the number of iterations (20) to get a better model, and the split criterion of size for clusters that are roughly balanced in number of cases assigned.

CREATE TABLE km_settings (setting_name  VARCHAR2(30), setting_value VARCHAR2(30));

BEGIN
  INSERT INTO km_settings (setting_name, setting_value) VALUES
    VALUES (dbms_data_mining.clus_num_clusters, 20);
  INSERT INTO km_settings (setting_name, setting_value)
    VALUES (dbms_data_mining.kmns_distance, dbms_data_mining.kmns_cosine);
  INSERT INTO km_settings (setting_name, setting_value) VALUES
    VALUES (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_off);
  INSERT INTO km_settings (setting_name, setting_value) VALUES
    VALUES (dbms_data_mining.kmns_iterations,20);
  INSERT INTO km_settings (setting_name, setting_value) VALUES
    VALUES (dbms_data_mining.kmns_split_criterion,dbms_data_mining.kmns_size);
  COMMIT;
END;

With this in place, we can now build the clustering model.

BEGIN
    DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'TEXT_CLUSTERING_MODEL',
    mining_function     => dbms_data_mining.clustering,
    data_table_name     => 'extracted_tokens_tfidf_nt',
    case_id_column_name => 'id',
    settings_table_name => 'km_settings');
END;

To generate cluster names from this model, check out my earlier post on that topic.