Multi-Column CTX Index (Stupid Hack Trick)

The Problem:

Well I thought, that with the Multi_Column_DataStore, Oracle got it right but apparently they only got it half-right. In our previous example I created a multi-column CTX index on Subject_Headings that consisted of the columns Subject_Headings and Medical_Subject_Headings. When Subject_Hedings was updated everything worked as it should. The CTX_User_Pending table showed that there were rows that needed to be synced. However, when the second column, Medical_Subject_Headings, was updated the CTX_User_Pending table did not show any pending updates so the changes were not able to be found when searching. You would think that the Oracle CTX system would be able to keep track of the columns included in a multi-column datastore but, alas no. After looking on the Internet and through the Oracle Documentation it became clear that the only way to get arround this was to use a "Stupid Hack Trick."

The Hack:

The way to get around this involves all the steps from the previous post with 3 extra/different steps.

1. You will need to add an extra "dummy" column that you will user to create the CTX index on. This will be the column that you will use in the contains() clause, I called mine "Subject_CTX_Search."
2. You need to build the CTX index using this "dummy" column and not either one of the subject columns
3. You will need to create an update trigger on the table that checks to see if either of the underlying subject fields have been altered. If a(the) field(s) have been altered then you need to change the value of the "dummy" column so that the CTX system will see that there is an index change pending and can process it with the sync_index() procedure.

The Example:

First we will create the table, fill it with some data, build our CTX index on the "dummy" column and finally create the trigger to keep the index in sync.

--
-- Drop the table if it exists and then create it.
--
Begin
  Execute Immediate 'drop table Scott_Items';
Exception
  When Others Then Null;
End;
/
Create Table Scott_Items
(
  pk_Item Number(11) Not Null,
  Title VarChar2(100),
  Subject_Headings VarChar2(70),
  Medical_Subject_Headings VarChar2(70),
  Subject_CTX_Search VarChar2(1)
);
/
Insert Into Scott_Items (pk_Item, Title, Subject_Headings, Medical_Subject_Headings)
Values(1, 'Title 1', '1. HEART 2.REFERENCE', '1.CARDIOVASCULAR DISEASES.');
/
Insert Into Scott_Items (pk_Item, Title, Subject_Headings, Medical_Subject_Headings)
Values(2, 'Title 2', '1. MAGNETIC RESONANCE IMAGING. 2. RADIOLOGICTECHNOLOGISTS.', 'MAGNETIC RESONANCE IMAGING.');
/
Insert Into Scott_Items (pk_Item, Title, Subject_Headings, Medical_Subject_Headings)
Values(3, 'Title 3', '1. SUBSTANCE ABUSE--PATHOPHYSIOLOGY. 2. DOPAMINE--PHYSIOLOGICAL EFFECT', '1. NEUROPHYSIOLOGY. 2.HEART EFFECT');
/
Insert Into Scott_Items (pk_Item, Title, Subject_Headings, Medical_Subject_Headings)
Values(4, 'Title 4', '1.SPORTS 2.BASKETBALL 3.CELTICS 4. REFERENCE', '');
/
Begin
  Begin
    ctx_ddl.drop_preference('two_column_subject');
  Exception
    When Others Then Null;
  End;

  ctx_ddl.create_preference('two_column_subject',
                            'MULTI_COLUMN_DATASTORE');

  ctx_ddl.set_attribute('two_column_subject',
                        'COLUMNS',
                        'Subject_Headings, Medical_Subject_Headings');
End;
/
--
-- Drop existing CTX index, if it exists nad create a new one
--
Begin
  Execute Immediate 'drop INDEX G_CTX_SUBJECT_IDX';
Exception
  When Others Then Null;
End;
/
CREATE INDEX G_CTX_SUBJECT_IDX ON Scott_Items(Subject_CTX_Search)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('DataStore Falcon.two_column_subject');
/
Create Or Replace Trigger Falcon.tu_Items_CTX_Sync
Before Update
On Falcon.Scott_Items
Referencing New As New Old As Old
For Each Row
Declare
Begin
  If Nvl(:Old.Subject_Headings, 'A') !=
     Nvl(:New.Subject_Headings, 'B') Or
     Nvl(:Old.Medical_Subject_Headings, 'A') !=
     Nvl(:New.Medical_Subject_Headings, 'B') Then
     --
     -- Change dummy CTX column to force CTX sync (stupid hack trick)
     --
     If :New.Subject_CTX_Search = 'A' Then :New.Subject_CTX_Search := 'B';
     Else :New.Subject_CTX_Search := 'A';
     End If;
  End If;
End;
/

Now we are ready to run a couple of tests:

Select Title From Scott_Items
Where Contains(Subject_CTX_Search, '{HEART}')>0;

TITLE
------------------
Title 1
Title 3

Update Scott_Items
Set Subject_Headings = Subject_Headings  ' 3.SCOTT'
Where pk_Item = 1;
/

Select Title From Scott_Items
Where Contains(Subject_CTX_Search, '{SCOTT}')>0;

No Results

--
-- We "should" have 1 pending change
--
select pnd_index_name , count(*)
From ctx_user_pending
group by pnd_index_name;

PND_INDEX_NAME COUNT(*)
------------------- --------
G_CTX_SUBJECT_IDX   1

--
-- Sync the index
--
Exec ctx_ddl.sync_index(idx_name => 'G_CTX_SUBJECT_IDX');

Select Title From Scott_Items
Where Contains(Subject_CTX_Search, '{SCOTT}')>0;

TITLE
------------------
Title 1

Update Scott_Items
Set Medical_Subject_Headings = 'TIGER'
Where pk_Item = 4;

Select Title From Scott_Items
Where Contains(Subject_CTX_Search, '{TIGER}')>0;

No Results

--
-- We "should" have 1 pending change
--
select pnd_index_name , count(*)
From ctx_user_pending
Group by pnd_index_name;

PND_INDEX_NAME COUNT(*)
------------------- --------
G_CTX_SUBJECT_IDX   1

--
-- Sync the index
--
Exec ctx_ddl.sync_index(idx_name => 'G_CTX_SUBJECT_IDX');

Select Title From Scott_Items
Where Contains(Subject_CTX_Search, '{TIGER}')>0;

TITLE
------------------
Title 4

Final Thoughts:

The only redeeming factor of this hack is that the "dummy" column only needs to be set to VarChar2(1) so it won't take up as much space as concatenating the column values and storing them in the table. What makes it ugly though is that when someone who doesn't know about multi-column CTX indexes looks at your code to maintain it they may be confused as to why anyone in their right mind would create a CTX index on a VarChar2(1) column. My only advice is to comment your stored procedures liberally so that anyone looking at your code knows that is going on and to suffix your "dummy" column with "_CTX_Search" or something similar to alert the next developer that this column is a CTX searchable column. You might even think of using a column comment to document the usage of the VarChar2(1) CTX Search column.

Comment On Column Scott_Items.Subject_Ctx_Search Is
'This is used as a placeholder so the CTX index can search the two subject clauses
(Subject_Headings and Medical_Subject_Headings)
using the two_column_subject MULTI_COLUMN_DATASTORE';