Faster way of creating a unique index

[Chintz]

I am trying to create a unique index on a table that has 220994866 rows. In the create index if I add the parallel clause will that be faster? Also, if that is a more faster and efficient way please give me details on the exact syntax and also the pros and cons of using it.

Thanks,
Chintz

[marist89]

Maybe yes, maybe no. Do you have multiple CPUs? Is your disk throughput adequate for n threads?

[gandolf989]

Is the table partitioned? That should make it easier to create an index in parallel. Also depending on your application, you should get better response times with a partitioned table.

I would hope that you would have more than one CPU on your server.

[davey23uk]

nologging and unrecoverable

[Mr.Hanky]

Truncate the table, then build the index.....


well,.........at least it will be faster!

[gandolf989]

Originally posted by Mr.Hanky
Truncate the table, then build the index.....


well,.........at least it will be faster!

Wow, you are really playing Mr. Helpful today.

[marist89]

Originally posted by gandolf989
... may ... get better response times with a partitioned table.

[Chintz]

Thanks for you replies.
No, the table is not partitioned. Yes there are multiple CPUs on the server. Currently, I am creating the index with the parameters nologging, parallel 4.

Chintz

[Mr.Hanky]

Originally posted by gandolf989
Wow, you are really playing Mr. Helpful today.

Hey, if he listened to me he'd be done by now.


Outside of parallel and...
ALTER SESSION SET SORT_AREA_SIZE=???????

I am out of suggestions.

[mike9]

I also think that increasing the sort_area_size to a MAXIMUM would help a lot. But don't set it to large, because with paralllel 4 your query will use up to 4 times the value set by sort_area_size.

Also in case your DB is running with WORKAREA_SIZE_POLICY=AUTO you have to set it to MANUAL on session level, otherwise the parametre sort_area_size will have no impact.