Re: Creating indexes takes too long

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Wed, 12 Mar 2003 18:07:23 -0800
Message-ID: <3E6FE7DB.DCA4A79@exesolutions.com>

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

CREATE TABLE test (

f1 NUMBER,
f2 NUMBER,
f3 NUMBER,
f4 NUMBER,

f5 NUMBER);

BEGIN
   FOR i IN 1..30000
   LOOP

      INSERT INTO test VALUES
      (i, i+1, i, i+2, i);

   END LOOP;
   COMMIT;
END;
/

SET TIMING ON CREATE INDEX ix_test ON
test (f1, f2, f3, f4, f5);

Elapsed: 00:00:04.07

On an IBM ThinkPad 600X with 500MHz PIII and 512MB RAM running Oracle 9.2.0.1.

That's 4 seconds. I find your timing on SQL Server abominable too.

If it is taking you minutes ... it is because you need to hire some Oracle

talent to help you properly install, configure, and work with Oracle.

Daniel Morgan Received on Wed Mar 12 2003 - 20:07:23 CST