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
| Name | clob_col |
|---|---|
| A1 | V1 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
