Re: Speeding up Index Creation - question

Rory Skelly wrote:
>
> Greetings from an Oracle DBA-in-the-making
>
> My question has to do with speeding up the process of creating indexes.
>
> I have several tables which contain between 1 and 4 million rows each.
> I am creating indexes on single columns and in some cases on 2 columns.
> Oracle seem to take an inordinate amount of time to create these indexes -
> compared to loading the data for instance.
>
> Example: On a table with 4 million rows it took Oracle 25 minutes to create
> an index on a single column which is varchar2(4).
>
> Is this normal??
>
> The Oracle documentation offers the following 2 suggestions for decreasing
> time to create indexes but I haven't seen any benefit from either:
>
> 1. I have tried bumping up the SORT_AREA SIZE to 2M then to 10M then
> 2. to 20M with no noticeable difference.
>
> 2. I have also tried creating a new TEMP Tablespace of 200M with extents
> that are 10M - this also showed no performance increase.
>
> Any help would be appreciated
>
> Regards
>
> Rory Skelly
> skellyr_at_kgh.kari.net

Rory:

I'm sure you probably already did this, but just in case? Did you alter the user creating the index to use the new temp tablespace? Is the new tablespace on a
different physical disk than the index is located?

You don't mention what version of Oracle 7 you are using but later versions have an unrecoverable clause which does not write records to the redo logs. And should
significantly reduce the time to creation. The downside of this is this index
cannot be recovered until after the next backup.

ie: create index indexname on tablename (column1) unrecoverable;

On tables of this size the sort_area_size will have little impact.

Hope this helps,

John Hough
Database Administrator
NCI Infosystems, Inc. Received on Wed Feb 05 1997 - 00:00:00 CST