Home » RDBMS Server » Performance Tuning » Index Creation with Parallel Option (Oracle 10g Rel 2)
Index Creation with Parallel Option [message #350349] Wed, 24 September 2008 15:41 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I have created indexes with parallel Degree 6 or 8 and they were
fast on 400 million rows table, (it took around 20-30 minutes )

But now there is a requirement to create a Unique deferred Constraint which will create a NON Unique index

I am using this

ALTER TABLE PAYMENTS ADD ( CONSTRAINT UNI_PYM_TRANSID_PYMT_SEQ  UNIQUE (TRANS_ID, PYMT_SEQ) deferrable initially deferred)  ;


This statement will take forever on a 400 mill rows table, since internally it's creating a index and here i cann't apply Parallel Degree for Index Creation,

Is there a way i can speed up the above constraint creation.


Thanks

Re: Index Creation with Parallel Option [message #350398 is a reply to message #350349] Wed, 24 September 2008 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First create the index then the constraint.

Regards
Michel
Re: Index Creation with Parallel Option [message #350459 is a reply to message #350349] Thu, 25 September 2008 02:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you KNOW that your current data is valid, you can create the constraint with ENABLE NOVALIDATE specified. This means the constraint will only be applied to new data, or data that changes.
Re: Index Creation with Parallel Option [message #350592 is a reply to message #350459] Thu, 25 September 2008 09:09 Go to previous message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Michel and JR

I will try both options and see the time difference.


Thanks Again
Previous Topic: Improve in-memory sort performance
Next Topic: Slow Execution Stored Procedure with cursors
Goto Forum:
  


Current Time: Sun Dec 04 22:56:04 CST 2016

Total time taken to generate the page: 0.05724 seconds