Context index creation performance on large table
Date: Tue, 18 Mar 2008 12:20:23 -0400
Platform: Oracle 10g, Linux on a 4 cpu box
I am creating a text index on a 65 million row 8-hash partitioned table. Total 8k blocks for the table is 999424. Average clob column size is 300 bytes.
I am creating a text index as:
create index tidx_item_markers on items(marker_details)
indextype is ctxsys.context
parameters ('memory 128m') parallel 8
Index creation is painfully slow. Query from v$session_longops shows:
SQL>select opname, totalwork, units, time_remaining from v$session_longops where time_remaining <>0;
(output formatted to show properly)
Rowid Range Scan,1990624,Blocks,798966 Rowid Range Scan,1863664,Blocks,741880 Rowid Range Scan,1867744,Blocks,830822 Rowid Range Scan,1236960,Blocks,498835 Rowid Range Scan,1343454,Blocks,417863 Rowid Range Scan,2142192,Blocks,858864
When total blocks in the table are 999424, why Oracle is showing more than double the blocks to scan through for six parallel process? It is scanning approx 13 times the table blocks. The table data was never deleted. With this speed it will take more than 9 days to create index.
How can I speed up the index creation? Has anyone similar experience of creating context index on a 65 million row table. How much time it takes to do so?
SandeepReceived on Tue Mar 18 2008 - 11:20:23 CDT