Search My Oracle Blog

Custom Search

22 April, 2018

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").

.
.
.




1 comment:

Anonymous said...

Hi Hemanth Sir, I have been following your blog for years now and you blogposts are awesome!!

I would like to request you to write something on XMLINDEX like when should it be used compared to Context/CTXCAT and how to implement it.

When one should be using one type of indexing over another.

Thanks in advance.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com