Creating indexes takes too long

Hi Everyone,

We are trying to support a functionality on both MSSQL and Oracle equally well. During our performance tests using exactly same data on both, we find that there are some severe bottlenecks on Oracle compared to MSSQL, out of which index creation is one. We have some medium size tables with 30,000 to 500,000 records with about 25 to 50 fields and the index include 5 to 10 fields each (and no additional clauses such as ASC or DESC). The cumulative time spent by MSSQL in creating all the indexes is negligeble compared to the entire run
(1min. or so out of 15min.), but in oracle it is pretty significant
(about 15min out of 45min). Our aim is to optimize oracle to bring the
time down to at least 15min to match that of MSSQL, but as you can see the index creation itself is same as the entire MSSQL run in time. The server hardware is pretty comparable in size and speed (in fact the Solaris machine for Oracle is better than the win2k for MSSQL). I am not a DBA, but I am hoping that there is some tuning that we need to do to reduce the time taken for index creation. I would appreciate if anyone can give information on how to reduce the timings or where to look for more information.

Thank you,
Hari Received on Wed Mar 12 2003 - 17:56:04 CST