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!