Do any databases allow multiple indexes on the same table to be created simultaneously?

I'm pretty sure this can't be done in Oracle, but I'd love to be proved wrong...

Say I have a huge table in with lots of columns and I want to create indexes on a dozen or so columns. Using Oracle, 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.

You'd think 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)
);

So obvious that there must be a great reason why it's not there.

Any ideas?

I could f开发者_Python百科ire off each create index simultaneously in separate sessions and hope the database buffer cache saved the day, but seems like a long shot.

EDIT

I didn't get a definitive answer so I asked the same question on Oracle-L:

http://www.freelists.org/post/oracle-l/Creating-multiple-indexes

General consensus was that it isn't available but would perhaps be a useful feature. Most useful response was from David Aldridge who suggested that if the create index statements were all kicked off at the same time then Oracle would 'do the right thing'.

---

I don't believe it's possible in Oracle, or any other DBMS. However in Oracle you can speed up index creation using options like PARALLEL and NOLOGGING.

PARALLEL allows you to parallelize the processing onto N other CPUS.

NOLOGGING forgoes writing to the redo log (which may not be for you).

CREATE INDEX some_idx
   ON a_table(col1, col2, col3)
PARALLEL 3
NOLOGGING;

---

The answer is no for Oracle and according to my research it is also no for DB2. I doubt any others have that feature.

---

In your example, you had mutiple single-column indexes, so the following suggestion does not apply here. But I wanted to point it out anyway since it IS an example of how to cut down on index creation time in certain cases.

When the table rows are physically sorted in the same order as the index you are building, you may specify the "NOSORT" option in your create index statement. That way Oracle does not have to sort the rows during the index creation (which is a killer when the sort spills to disk).

This is typically useful when you create an empty table (or CTAS), insert the rows in some specific order (enforced by order by), and then directly create an index using the same column order as your order by statement.

---

Noticed this blog post by David Aldridge from March 2008...

http://oraclesponge.wordpress.com/2008/03/26/how-to-create-multiple-indexes-in-a-single-ddl-statement/