Context index creation performance on large table
- From: "Sandeep Dubey" <dubey.sandeep@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 18 Mar 2008 12:20:23 -0400
Hi,
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?
Regards
Sandeep
Other related posts:
- » Context index creation performance on large table