Skip to main content

Correct Search Implementation on Columns having Context Index

Karthik C, Senior Database Engineer at Trigent, Certified Oracle Expert
Karthik C, Senior Database Engineer at Trigent, Certified Oracle Expert

I regularly find myself explaining to my team members, how to implement correct search on columns having context Index. So, I  thought of penning this piece. In this post, I’ll start from ground-zero explaining CLOB, Context, and a code snippet that will help database developers implement the “titled” problem without a hitch.  

Let’s Start – Understanding CLOB

Character Large Object (CLOB) is a data type used in databases to store data up to 4 GB. CLOB columns store locators that reference the location of the actual CLOB value. Depending on the column properties you specify when you create the table, and depending the size of the CLOB, actual CLOB values are stored either in the table row (inline) or outside of the table row (out-of-line)

What is Context Index?

A special type of index called Context Index is used on CLOB/VARCHAR columns to help better perform searches, but an improper usage of Context Index can result in incorrect result. Using an example, we will look at the proper usage of Context Index.

How to use Context Index?

Assume there exists a table ‘clob_table’ with two fields, ‘name’ with data type varchar and ‘clob_col’ with data type CLOB.

CREATE TABLE clob_table (name VARCHAR2 (10), clob_col CLOB);

Assume following in the content of ‘clob_table’ table

Nameclob_col
A1V1 V2 V3 V4 V5

 

Executed the following command to index the CLOB column

CREATE INDEX ix_context_index ON clob_table (clob_col)

INDEXTYPE IS ctxsys.context PARAMETERS (‘SYNC (ON COMMIT)’);

Requirement

  • Update CLOB column ‘çlob_col’ to ‘V6 V7 V8 V9 V10’ for record with name ‘A1’
  • On the updated record check if the value ‘V6’ is present in ‘clob_col’.
    • If found display the clob_col value
    • Else display ‘Record not found’

Code Snippet

DECLARE 
    text_list           VARCHAR2(100); 
    v_clob_output         CLOB;
BEGIN
    text_list := 'V6 V7 V8 V9 V10';
   
    UPDATE clob_table ct 
          SET ct.clob_col = text_list 
     WHERE name = 'A1';
   
    
 SELECT clob_col
     INTO v_clob_output  
     FROM clob_table
     WHERE contains (clob_col,'V6')>0;

  DBMS_OUTPUT.PUT_LINE (v_clob_output); 
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE ('Record not found');  
  END;

 

When this code is executed the result will be ‘Record not found’ since the updated detail is not available until it is committed explicitly. So, if one wants to process the updated CLOB data the result will NOT be correct.

What is the solution?

Use TRANSACTIONAL command

TRANSACTIONAL helps search on documents or data immediately after they are inserted or updated. If a text index is created with TRANSACTIONAL enabled then in addition to processing the synchronized rowids already in the index, the CONTAINS operator will process unsynchronized rowids as well. Oracle Text does in-memory indexing of unsynchronized rowids and processes the query against the in-memory index.

So, after a creation of context index for the CLOB or VARCHAR columns, the index has to be altered with parameters ‘replace transactional’. This ensures that the updated data will be present for processing though not committed in the database.

ALTER INDEX ix_context_index REBUILD PARAMETERS (‘replace transactional’);

Or the index can be dropped and recreated with transactional parameter

CREATE INDEX ix_context_index1 ON clob_table (name)

INDEXTYPE IS ctxsys.context PARAMETERS (‘SYNC (ON COMMIT) replace transactional’);

After altering or creating the index if the same code is executed the result will be the updated CLOB value ‘V6 V7 V8 V9 V10

Did you find this post on implementing correct search, interesting?  Do you have any ideas you would like to share—and how are you implementing them? Write to me at karthik_c@trigent.com