Re: Creating multiple indexes

From: Hemant K Chitale <>
Date: Wed, 17 Nov 2010 23:18:38 +0800
Message-ID: <>

Although each CREATE has to do a FullTableScan, it only "extracts" the column(s) that is interested in.
So, each of the CREATEs has to read each block "privately" (although the blocks may be cached in any of the multiple intervening layers).

ParallelQuery does direct reads into the PGA making the buffers "non-sharable" so Oracle ensures that no two PQSlaves read the same blocks.

Hemant K Chitale
sent from my smartphone

On Nov 17, 2010 5:33 AM, "Nick Pierpoint" <> wrote:

Hello all.

I'm guessing this has come up before but I couldn't find it in the archives so here goes... I'm trying to find the reason why there isn't a mechanism to create multiple indexes at the same time - a "create indexes" statement or similar.

Say I have a huge table and I want to create indexes on a dozen or so columns. I'd fire off several sequential create index statements and go off and boil the kettle.

Each create index needs to scan through every row in the table to form the index.

i.e. 10 indexes = 10 full scans.

I'd have thought an obvious optimisation would be to scan the table once and index the 10 columns at the same time. Wouldn't you?

create indexes on mytable (

   ix_mytable_cola (cola),
   ix_mytable_colb (colb),
   ix_mytable_colc (colc)


My particular requirement is building indexes after recreating reporting cubes in a data warehouse or updating indexes after dropping before a data load.

I know I can speed things up by partitioning the base table and creating the index in parallel. I could also fire off each create index simultaneously in separate sessions and hope the database buffer cache saved the day, but this seems sub-optimal.

I compare it to creating multiple hash maps on a array of data. If you were to code creating 10 hash maps of the contents of 10 columns - would you loop through the table rows 10 times or would you loop through once and create 10 hash maps at once?

Any ideas or comments most welcome.




-- Received on Wed Nov 17 2010 - 09:18:38 CST

Original text of this message