Text Index with BIG_IO Option is Slow to Build

(Doc ID 2353321.1)

Last updated on JANUARY 25, 2018

Applies to:

Oracle Text - Version to [Release 12.1]
Information in this document applies to any platform.


On, rebuilding partitions of a Text index now takes a day when it used to take only an hour.

When looking at the executing process, it spends a long time on an operation named "CTX Index Optimization" on the $I table:

CTX Index Optimization
Table Scan: DATA.DR#DATA_IDX_01_I30010$I: 1955816 out of 1955816 Blocks done



The BIG_IO attribute was added to the storage preference for the index:

ctx_ddl.create_preference('DATA_IDX_01_1_STOR', 'BASIC_STORAGE');
ctx_ddl.set_attribute('DATA_IDX_01_1_STOR','r_table_clause','LOB (data) STORE AS (cache)');
ctx_ddl.set_attribute('DATA_IDX_01_1_STOR','i_index_clause','COMPRESS 2');



Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms