Re: Creating indexes takes too long

"Hari Krishna Dara" <hari_newsgroups_at_yahoo.com> wrote in message news:16dcd029.0303121556.36a5ce88_at_posting.google.com...
> 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.
>

Hi Hara,

The biggest overheads in creating an index are the read of the base table and the subsequent sorting involved.

With regard to reading the table, the parameter db_file_multiblock_read_count is paramount (see archives for much discussion on this, also see Steve Adam's site www.ixora.com.au for good info). You may also want to look at parallel options and ensure the base table is suitably "striped" to minimize I/O related bottlenecks.

With regard to the sort, ensure you have a healthy sized sort_area_size to minimize a disk sort if possible (which can be set at the session level). Or better still, look at the pga_aggregate_target if on 9i.

Considering the size of your tables, I think there's definitely scope for improvement ...

Good Luck

Richard Received on Thu Mar 13 2003 - 07:15:04 CST