An often asked question is how to synchronize an Oracle Text Context index (A ctxcat index is automatically synchronized) , or otherwise asked, when does an insert/update/delete on the column(s) which is indexed come available for a search. There are more options depending on the version:
- Manual with ctx_ddl.sync_index (8 and above)
- With a job calling ctx_ddl.sync_index (8 and above)
- Synchronize through ‘Sync ‘ (10 and above)
- Manual with ‘Alter index’
Manual with ctx_ddl.sync_index
This is the oldest method. You call the procedure with the name of the index and it will sync the index. There is an option for a second parameter, which is the memory available for the synchronization. If there are a lot of updates to do, it is the best to set here some value. Otherwise the default is the value found in ctx_parameters for default_index_memory.
Ctx_ddl.sync_index called in a job
The manual update cost a lot of management, when must I do it and so on. Therefore it is better to schedule the synchronization. This can be done with dbms_job or in newer versions of the Oracle database with dbms_scheduler. By using these you don’t have less to manage yourself. With this job you can self design when the jobs needs to run.
Synchronize through ‘Sync’
Since 10g it is possible by creating the index to set the characteristics of a synchronization. This can be done by setting the sync option within the parameters section. There are 3 options:
- Manual: You still have to call ctx_ddl.sync_index
- Every ‘interval’: with this option you create a job (just as the second here above mentioned option). “interval” has the same syntax as just by the dbms_scheduler package. For this option the user needs ‘Create job’ rights.
- on Commit: This option will at every commit sync the index. Use this option only if you don’t have too many changes on the columns of the index. The transaction will wait till the index is synchronized.
Manual with ‘Alter index’
This is the less used option, because the whole index will be rebuild. Because of the complete rebuild, it cost time and resources. An advantage is that the index also is optimised afterwards. Use this option only if you need to make changes to the index itself, as some setting of parameters, or a new tablespace. Otherwise use one of the above.
Synchronization and when
It is possible to see which indexes needs to be synchronized. There is the view CTX_PENDING. In this view are the indexes with the rowids of the records who need to be synchronized. So you can possible query this view and see which indexes needs a sync. This can of course also be scheduled.
After an optimisation an index is not optimised, meaning the index can be bigger than necessary is. But about that I will talk later.