An Oracle blog about Oracle Text Index

  • January 6, 2014

Datastore triggers in 12c

Roger Ford
Product Manager

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
  :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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.