Creating multiple indexes
- From: Nick Pierpoint <mail@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 16 Nov 2010 21:32:37 +0000
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.
Thanks.
Nick.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
