Re: Creating indexes takes too long

On Wed, 12 Mar 2003 15:56:04 +0000, Hari Krishna Dara wrote:

> 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

Oh dear. Look: Oracle isn't SQl Server, and attempts to make them do the same thing are just doomed to disaster. Never mind that a 10-column index, representing about 20% of the largest tables you have, sounds like distinctly dodgy design in the first place.

But if you must go down this appalling road, then ask yourself what Oracle is doing that SQL Server isn't: generating redo, perhaps? Generating undo? Sorting in memory? Or sorting on disk?

Try using the 'create index NOLOGGING' syntax to keep the redo to a minimum. Look at your sort_area_size to make sure the sort needed to populate the index isn't hitting disk.

Start analyzing what waits are happening on the database during the index builds (welcome to Statspack).

If you don't know how to run statspack, find out (tahiti.oracle.com). But if you don't know how to interpret statspack, then go to www.oraperf.com and get it analyzed for free there.

Regards
HJR Received on Thu Mar 13 2003 - 05:37:51 CST