Context index creation performance on large table

From: Sandeep Dubey <>
Date: Tue, 18 Mar 2008 12:20:23 -0400
Message-ID: <>


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?



Received on Tue Mar 18 2008 - 11:20:23 CDT

Original text of this message