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 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

On 12.1.0.2, 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
"DATA"."DR#DATA_IDX_01_I30010$I
Table Scan: DATA.DR#DATA_IDX_01_I30010$I: 1955816 out of 1955816 Blocks done

 

Changes

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','small_r_row','T');
ctx_ddl.set_attribute('DATA_IDX_01_1_STOR','big_io','YES');
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');

 

Cause

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