Oracle Text provides the SEPARATE_OFFSETS option for improving the query performance for the CONTEXT indexes that extensively use IO operations, and whose queries are mainly single-word or Boolean queries.
The SEPARATE_OFFSETS option creates a different postings list structure for the tokens of type TEXT. Instead of interspersing docids, frequencies, info-length (length of the offsets information), and the offsets in the postings list, the SEPARATE_OFFSETS option stores all the docids and the frequencies together at the beginning of the postings list, and all the info-lengths and the offsets at the end of the postings list. The header at the beginning of the posting contains the information about the boundary points between the docids and the offsets. This separation of the docids and the offsets reduces the time for the queries to read the data, thus improving the query response time.
The performance of the SEPARATE_OFFSETS option is best realized when it is used in conjunction with the BIG_IO option, and is used for the tokens with very long posting.
To create a CONTEXT index with the SEPARATE_OFFSETS index option, first create a basic storage preference by setting the value of its SEPARATE_OFFSETS storage attribute to T, 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 SEPARATE_OFFSETS storage attribute to T:
exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'T');
To disable the SEPARATE_OFFSETS option, update the existing storage preference (mystore) by setting the value of its SEPARATE_OFFSETS storage attribute to F, and then rebuild the index.
exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'F');
alter index idx rebuild('replace storage mystore');
Do not use replace metadata operation to disable the SEPARATE_OFFSETS index option, as it can leave the index in an inconsistent state.
To enable the SEPARATE_OFFSETS option for a partitioned index without rebuilding the index, modify the basic storage preference by setting the value of its SEPARATE_OFFSETS storage attribute to T, replace the global index metadata using ctx_ddl.replace_index_metadata, and then call optimize_index in REBUILD mode for each of the partitions of the partitioned index table.
The following example enables the SEPARATE_OFFSETS option for the partitioned index idx:
exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'T');
exec ctx_ddl.replace_index_metadata('idx', 'replace storage mystore');
exec ctx_ddl.optimize_index('idx', 'rebuild', part_name=>'part1');