Datastore triggers in 12c
By Roger Ford on Jan 06, 2014
If you want to create an Oracle Text CONTEXT index on multiple columns, you would normally use a MULTI_COLUMN_DATASTORE or USER_DATASTORE to bring in the various columns for indexing.
This works well, but the kernel doesn't know which columns you've used in the index. As far as the kernel is concerned, you have created the index on a specific column, and it will only update the index when the contents of that column change.
Hence it is common to create a trigger something like the following:
CREATE OR REPLACE TRIGGER customer_update_trg
BEFORE UPDATE OF lastname ON customers
FOR EACH ROW
:new.firstname := :new.firstname;
In this case, the index was created on the firtname column, but the multi_column_datastore fetches in both lastname and firstname. The trigger ensures that whenever lastname is changed, we force an update of firstname in order to reindex the row.
However, things have changed in 12c. As a fix for bug 14155722, triggers check the "before" and "after" state of any referenced column values, and if the column has not changed, then no index updates take place. So the trigger above won't cause an index update if only lastname has changed.
The original behavior can be restored by an alter session or alter system operation, as follows:
alter session set "_fix_control"='14155722:OFF'
alter system set "_fix_control"='14155722:OFF';
If done as an alter session, it must be done before the update operation which invokes the trigger, and will need ALTER SESSION privilege. If done as alter system it will apply globally, but must be run by a DBA user. It can also be put into the database initialization file, either by adding "scope=spfile" to the alter system command, or if a pfile is in use by adding it explicitly there.