Re: IOT secondary index creation can't use parallel (30hours)

From: Jonathan Lewis <>
Date: Sat, 21 Jan 2012 15:32:13 -0000
Message-ID: <63DAD722A94347749860F0917AB6492D_at_Primary>

I just ran a quick test on and and found that ran serially if I tried to do parallel and online. The test, with the same script, crashed with an ORA-00600 error. (online alone was okay, parallel alone was okay)

Two ideas that may explain your observations:

  1. The rebuild in didn't run online
  2. Parallel online crashes in **some** cases - so Oracle disabled the combination in the upgrade


Jonathan Lewis
Oracle Core (Apress 2011)

  • Original Message ----- From: "Eagle Fan" <> To: <> Sent: Wednesday, January 18, 2012 1:22 PM Subject: IOT secondary index creation can't use parallel (30hours)

I created IOT secondary index using parallel 4 online option on two databases. One database is 10203 version and another one is 11202 version.

On 10203 version, the SQL finished in 20 minutes, but on 11202 version, it used 30 hours. The two databases have same table/index structure and both of them are about 100GB.

On 10203 database, it used PK index fast full scan with parallel 4 as expected. But on 11202 database, it didn't use parallel index fast full scan, it used single thread db file sequential read on PK index. That's why it took 30 hours.

I don't know why on 11202 version, the secondary IOT index creation used less effecient db file sequential read instead of index fast full scan.

Is there any way to make it faster?

The SQL is like this:

a,b,c)tablespace index01 parallel 4 ONLINE;

Thanks in advance.


Eagle Fan


No virus found in this message.
Checked by AVG -
Version: 2012.0.1901 / Virus Database: 2109/4749 - Release Date: 01/17/12

-- Received on Sat Jan 21 2012 - 09:32:13 CST

Original text of this message