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:
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.
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:
When creating your Oracle Text indexes, you have three ways to specify how you want them to be synchronizedselect pnd_index_name, count(*)
from ctx_user_pending
group by pnd_index_name;
- 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
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.create index txt_index on my_table (text_col)
indextype is ctxsys.context
parameters ('sync (every "sysdate+(1/24)")');
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.