Oracle Text provides the BIG_IO option for improving the query performance for the CONTEXT indexes that extensively use IO operations. The query performance improvement is mainly for data stored on rotating disks, and not for data stored on solid state disks.
A CONTEXT index with the BIG_IO option enabled creates token type pairs with one LOB (large object datatype) for each unique token text. Thus, tokens with the same text but different token types correspond to different rows in the $I table.
The indexes with the BIG_IO option enabled should have the token LOBs created as SecureFile LOBs. In the SecureFile lobs, the data is stored sequentially in multiple blocks. This improves the response time of the queries, as the queries can now perform longer sequential reads instead of performing many short reads.
Using SecureFiles requires the COMPATIBLE setting to be 11.0 or higher. In addition, the LOB must be created on an automatic segment space management (ASSM) tablespace. When migrating from the existing text indexes to SecureFiles, an ASSM tablespace should be used. To help in migrating the existing indexes to SecureFiles lobs, ALTER INDEX REBUILD is extended to provide the replacement of storage preferences that only affect the $I table without performing reindex.
To create a CONTEXT index with the BIG_IO index option, first create a basic storage preference by setting the value of its BIG_IO 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 BIG_IO storage attribute to YES:
exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'YES');
To disable the BIG_IO option, update the existing storage preference (mystore) by setting the value of its BIG_IO storage attribute to NO, and then rebuild the index.
exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'NO');
alter index idx rebuild('replace storage mystore');
Do not use replace metadata operation to disable the BIG_IO index option, as it can leave the index in an inconsistent state.
To enable the BIG_IO option for a partitioned index without rebuilding the index, modify the basic storage preference by setting the value of its BIG_IO storage attribute to YES, 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 BIG_IO option for the partitioned index idx:
exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'YES');
exec ctx_ddl.replace_index_metadata('idx', 'replace storage mystore');
exec ctx_ddl.optimize_index('idx', 'rebuild', part_name=>'part1');
If a procedure modifies the existing index tables with only the BIG_IO option enabled, then it will not result in reindexing of the data.