23 April, 2018

Domain Indexes -- 4 : CTXRULE Index

I have earlier provided simple demonstrations of CONTEXT and CTXCAT Indexes.

A CTXRULE Index can be used to build a Document Classification application.  This involves indexing a table of "queries" that define the classification.  Queries use the MATCHES clause.
(Note : Like the CONTEXT Index, a call to SYNC_INDEX is required before the rows are indexed).

SQL> create table common_query_classes
  2  (classification varchar2(64),
  3   query_text varchar2(4000));

Table created.

SQL> create index query_class_index
  2  on common_query_classes (query_text)
  3  indextype is ctxsys.ctxrule
  4  /

Index created.

SQL> 
SQL> insert into common_query_classes
  2  values ('Players','Gavaskar OR Tendulkar OR Chappell OR Imran OR Botham');

1 row created.

SQL> insert into common_query_classes
  2  values ('Grounds','Brabourne OR Wankhede OR Lords');

1 row created.

SQL> commit;

Commit complete.

SQL> 


Note that the query predicates are divided by the OR.  They are NOT listed in Alphabetical order.

Now, I test a few queries :

SQL> exec ctx_ddl.sync_index('QUERY_CLASS_INDEX');

PL/SQL procedure successfully completed.

SQL> 
SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text,'Tendulkar is a Player at Brabourne') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Grounds
Players

SQL> 
SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text,'Botham') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Players

SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text, 'Kohli is a Player at Wankhede') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Grounds

SQL> 


Note that, since Kohli is not in the Players list, the last query doesn't return the Classification "Players".
.
.
.

No comments: