Improved Response Time using STAGE_ITAB Option of CONTEXT Index

Oracle Text provides the STAGE_ITAB option for improving the query performance for the CONTEXT indexes that extensively use DML operations for near real-time indexing.

When the STAGE_ITAB index option is not used, whenever a new document is added to the CONTEXT index, SYNC_INDEX is called to make the documents searchable. This creates new rows in the $I table, thus increasing the fragmentation in the $I table. This leads to the deterioration of the query performance.

When the STAGE_ITAB index option is enabled, the information about the new documents is stored in the $G staging table, and not in the $I table. This ensures that the $I table does not get fragmented, and thus not deteriorating the query performance.

When the STAGE_ITAB index option is enabled, the $H b-tree index is also created on the $G table. The $G table and $H b-tree index are equivalent to the $I table and $X b-tree index.

Use the MERGE optimization mode to optimize the rows present in the $G table and move them to the $I table.

To create a CONTEXT index with the STAGE_ITAB index option, first create a basic storage preference by setting the value of its STAGE_ITAB storage attribute to YES, and then specify this storage preference while creating the CONTEXT index.

The following example creates a basic storage preference mystore and sets the value of its STAGE_ITAB storage attribute to YES:

exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'STAGE_ITAB', 'YES');

You can also enable the STAGE_ITAB index option for an existing non-partitioned CONTEXT index by using the rebuild option of the ALTER INDEX statement.

alter index IDX rebuild parameters('replace storage mystore');

To disable the STAGE_ITAB option for a non-partitioned CONTEXT index, update the existing storage preference (mystore) by setting the value of its STAGE_ITAB storage attribute to NO, and then rebuild the index.

exec ctx_ddl.set_attribute('mystore', 'STAGE_ITAB', 'NO');
alter index idx rebuild('replace storage mystore');

This operation runs the optimization process using the MERGE optimization mode and then drops the $G table.

The rebuild option of the ALTER INDEX statement does not work with the partitioned CONTEXT index for enabling and disabling the STAGE_ITAB option.

The following example enables the STAGE_ITAB option for the partitioned CONTEXT index idx:

alter index idx parameters('add stage_itab');

The following example disables the STAGE_ITAB option for the partitioned CONTEXT index idx:

alter index idx parameters('remove stage_itab');