3 ways to synchronize your Oracle Text indexes

One of my favourite features of the Oracle database is Oracle Text.  In this blog post I'll discuss different approaches to the synchronisation of Oracle Text indexes.

Oracle Text indexes are synchronized via a queue of pending updates.  Whenever the rows upon which the index is built are inserted or changed, those rows are added to the queue.  This queue is then processed according to the synchronization method that you defined at index creation time.  This means that the synchronization is independent of your transaction,  i.e. your transaction completes without waiting for the index synchronization.

It is important to note that the index is not fully recreated when synchronized but is incrementally updated.  Therefore, updates or insertions of a few rows will be synchronised very quickly but an update or insertion of, let's say, 800'000 rows will take significantly longer to synchronize.

You can view the queue of pending rows using the ctx_user_pending view.

To see how many rows are pending for each index:

select pnd_index_name, count(*)
  from ctx_user_pending
 group by pnd_index_name;

When creating your Oracle Text indexes, you have three ways to specify how you want them to be synchronized

• manually (this is the default if no syncing method is specified)

create index txt_index on my_table (text_col)
indextype is ctxsys.context;

• on commit - this will start synchronizing immediately after the transaction is committed

create index txt_index on my_table (text_col)
indextype is ctxsys.context
parameters ('sync (on commit) ');

• at regular intervals (needs CREATE JOB) privilege - this example syncs hourly

create index txt_index on my_table (text_col)
indextype is ctxsys.context
parameters ('sync (every "sysdate+(1/24)")');

Each of these methods has its advantages and disadvantages.  For example, if you can live with an index that is synchronized daily and want to minimise the database load during working hours, maybe you might consider syncing at a regular daily interval.

Similarly, if you have a lightish load and just have a few changes at a time, then maybe an "on commit" synchronization is the right one for you.  In my experience, the majority of Oracle Text applications have (sometimes wrongly) opted for this.

I hope that this has explained these three options in a quick and simple way.  The well-written Oracle Text Developer's Guide contains a lot of detail on how to manage your Oracle Text indexes.