Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger

Question

TLDR: The CTXCAT index is supposed to be transactional, but Before Insert triggers that modify the indexed column don't seem to induce an update.

---

I'm running on Oracle 9i.
(Actually, I'm running on 12c, but building for 9i.)

I have a table like so:

------------------------------------------
| Names                                  |
|----------------------------------------|
| first_name | last_name | compound_name |
|----------------------------------------|
| Joe        | Smith     | JOE SMITH     |
| ...        | ...       | ...           |
------------------------------------------

I'm using Oracle Text (available on 9i) with a CTXCAT index on the compound_name field. CTXCAT is a transactional index, meaning that it updates any time there is a change to whatever it's indexing. This is in contrast to a CONTEXT index, which requires a manual or periodic sync command issued to it, at which point the index updates.

Index creation code: (forgive the capitalization)

BEGIN
  ctx_ddl.create_preference   ('MY_WL', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute       ('MY_WL', 'substring_index',  'YES');
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_index',     'YES');
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_min_length',  1);
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_max_length',  7);
END;

begin
  CTX_DDL.CREATE_INDEX_SET('MY_IS');
  CTX_DDL.ADD_INDEX('MY_IS', 'is_active');
end;

-- Creating the ctxcat index uses the owner's default tablespace.
ALTER USER test DEFAULT TABLESPACE misc;

CREATE INDEX test.names_idx on test.names (name_compound)
              indextype is ctxsys.ctxcat
              parameters ('WORDLIST MY_WL
                           INDEX SET MY_IS');

I never make changes directly to the compound_name column. Instead, I have a Before Insert Or Update trigger, that composes that value out of first_name and last_name. The trigger looks like this:

create or replace trigger test.names_compound_trg
              before
                insert or
                update of first_name, last_name
              ON test.names
                 FOR EACH ROW
   begin
      select ( 
        UPPER(
              NVL2(:new.first_name,   :new.first_name || ' ',   null) ||
              NVL2(:new.last_name,    :new.last_name,           null)
        )
      ) INTO :new.compound_name FROM sys.dual;
   end;
/

You can see that any change to the first_name or last_name fields updates the compound_name field with a capitalized, concatenated string. The trigger capitalizes it because Oracle 9i does not support case-insensitive indexes. To get a case-insensitive search, I uppercase the compound_name and in my query I uppercase the search value.

Now, the problem:

Executing this SQL command does not cause my index to update:

update test.names set first_name = 'george', last_name = null where ...;

But executing this SQL command will:

update test.names set 
  first_name = 'george', 
  last_name = null, 
  compound_name = 'george' 
where ...;

The test.names_compound_trg trigger works properly and populates the compound_name field alright. But it seems as though, even though CTXCAT is supposed to be transactional, it's not smart enough to detect that I've manipulated the :new.compound_name value in my trigger. In the tests I've done so far, only a direct manipulation of that field with an update or insert query will trigger an update to the index.

Any solutions to this? I would like to more or less keep the set up I have, with the compound_name field being populated by triggers rather than in my DAO layer, but I need to force the CTXCAT index to update itself whenever that field changes, even if the trigger is changing it.

Alternative methods of handling this are welcome as well, but I'd prefer a simple tweak or annotation or something to just get this working.

Thanks!

Answer 1

Aha! I've found the answer. Talk about an edge case.

First, I found this post from 2007, where someone says:

...the AFTER trigger for synchronizing the CTXCAT index on [column] is not firing (since my update statement does not include the indexed column).

...

Oracle, would it not be better to generate the CTXCAT trigger to examine the :old and :new values in the indexed column, rather than using a check on (if updating([column]))?

Over a year later, someone replied:

I fixed this issue by adding additional UPDATING (first_name and last_name) conditions in the DR$table_name trigger.

Here is part of the modified trigger.

if (inserting or updating('LAST_NAME_FIRST') or updating('FIRST_NAME') or updating('LAST_NAME')) then reindex := TRUE;

Hm, so the CTXCAT index uses a trigger to know when it needs to update the index for a particular entry. All I need to do is tweak the trigger and recompile it so it does what I want.

To get the content of the trigger:

SELECT text
  FROM   user_source
  WHERE  name = 'DR$NAMES_IDXTC'
  AND    type = 'TRIGGER'
  ORDER  BY line;

I copied this into Sublime, prettified it, and found this (simplified):

TRIGGER "TEST"."DR$NAMES_IDXTC" AFTER
INSERT
OR
UPDATE ON "TEST"."NAMES"
FOR EACH ROW DECLARE REINDEX boolean := FALSE;

BEGIN 

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME") THEN REINDEX := TRUE;
END IF;

...

END;

You can see that the 12c Oracle Text version's AFTER trigger does actually compare the :new and :old values of the indexed column to see if it needs to update--not the case back in 2008.

So...if I'm updating the :new value in my BEFORE trigger, that should be reflected in the AFTER trigger, and the comparison would kick off an update to the index. What gives?

Well, here are the two SQL statements I was using:

update test.names set 
  first_name = 'Skye', 
  last_name = 'Fillingim'
  where ... ;

update test.names set
  first_name = null,
  last_name = null
  where ... ;

(Slightly different than what I said in my question, I apologize.)

The effect here is that, each time I used one of these statements, either the :old.compound_name or :new.compound_name would be null. So when we get to this condition:

OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"

We are doing an equality comparison against a null, which evaluates to UNKNOWN. Hence, no index update.

This is actually an extreme edge case, because you have to be indirectly modifying :new.column via a trigger, and either :new or :old must be null. I would never have discovered it it I hadn't used those exact SQL statements.

So, we have a slight update to the trigger:

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"
    OR (:new."COMPOUND_NAME" IS NULL AND :old."COMPOUND_NAME" IS NOT NULL)
    OR (:new."COMPOUND_NAME" IS NOT NULL AND :old."COMPOUND_NAME" IS NULL)
    ) THEN REINDEX := TRUE;

And then just stick CREATE OR REPLACE in front of the old trigger, recompile it, and everything works perfectly.