Domain Indexes -- 3 : CTXCAT Index

In previous posts in December 2017, I had demonstrated a CONTEXT Index.

A CONTEXT Index is used for full-text retrieval from large pieces of text (or document formats stored in LOBs)

A CTXCAT Index is best suited for small fragments of text that are to be indexed with other relational data.

Before I begin with the CTXCAT index, in addition to the CTXAPP role (that I had granted during the earlier demonstration), the account also needs the CREATE TRIGGER privilege.

SQL> grant ctxapp to ctxuser;

Grant succeeded.

SQL> grant create trigger to ctxuser;

Grant succeeded.

SQL> 

I can now proceed with the CTXUSER demonstration.

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table books
  2  (book_id  integer primary key,
  3   book_title varchar2(250) not null,
  4   book_author varchar2(80),
  5   book_subject varchar2(25),
  6   shelf_id  integer)
  7  /

Table created.

SQL> 
SQL> insert into books values
  2  (1,'A Study In Scarlet','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
  2  (2,'The Sign Of Four','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
  2  (3,'Murder On The Orient Express','Agatha Christie','Mystery',1);

1 row created.

SQL> insert into books values
  2  (4,'A Brief History of Time','Stephen Hawking','Science - Physics',2);

1 row created.

SQL> 
SQL> insert into books values
  2  (5,'2001: A Space Odyssey','Arthur C Clarke','Science Fiction',3);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> 

Next, I specify what is called an Index Set -- which specifies the structured columns that are to be included in the CTXCAT Index.  I then define the CTXCAT Index on the BOOK_TITLE column.

SQL> begin
  2  ctx_ddl.create_index_set('books_set');
  3  ctx_ddl.add_index('books_set','book_subject');
  4  ctx_ddl.add_index('books_set','shelf_id');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> create index books_title_index
  2  on books (book_title)
  3  indextype is ctxsys.ctxcat
  4  parameters ('index set books_set')
  5  /

Index created.

SQL> 

Now, I can use the Index to query the table, using the CATSEARCH clause instead of the CONTAINS clause. My query includes both BOOK_TITLE and SHELF_ID

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'History','shelf_id=1') > 0
  4  /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'History','shelf_id>1') > 0
  4  /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT      SHELF_ID
------------------------- ----------
A Brief History of Time
Stephen Hawking
Science - Physics     2


SQL> 

The CTXCAT Index that I built on BOOK_TITLE also includes BOOK_SUBJECT and SHELF_ID as indexed columns by virtue of the INDEX_SET called "BOOKS_SET".

Now, I add another row and verify if I need to Sync the index (as I had to do with the CONTEXT Index earlier).

SQL> insert into books
  2  values 
  3  (6,'The Selfish Gene','Richard Dawkins','Evolution',2);

1 row created.

SQL> commit;
SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'Gene','book_subject > ''S'' ') > 0
  4  /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'Gene','book_subject > ''E'' ') > 0
  4  /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT      SHELF_ID
------------------------- ----------
The Selfish Gene
Richard Dawkins
Evolution      2


SQL> 

Note, specifically, how I could use the BOOK_SUBJECT in the query as if looking up a separate index on BOOK_SUBJECT.
The new book was included in the index without a call to CTX_DDL.SYNC_INDEX as would be required for the CONTEXT IndexType.

The portion of the query that is on the BOOK_TITLE column does a Text search on this column but the portions on BOOK_SUBJECT an SHELF_ID behave as with regular indexes.


(I know  that some readers will dispute the subject categorization "Evolution"  but I deliberately threw that in so that I  could show a query that uses a predicate filter not on "Science").

.
.
.