"ALTER TABLE xxx add constraint primary key" takes long long time!
872581Jul 8 2011 — edited Jul 8 2011Hi. all.
I want to create pk index on a 20 giga byte table.
The expected index size is 7 giga bytes.
The following is what I did :
-----------
1. create unique index (parallel 32, nologging )
CREATE UNIQUE INDEX SVC_CNTR_BY_CMSN_PAY_INFO_PK ON SVC_CNTR_BY_CMSN_PAY_INFO
(CMSN_ACTRSL_YM, SVC_CNTR_NO, CMSN_CD, CMSN_PAY_ORGN_ID, OPEN_ORGN_ID)
NOLOGGING PARALLEL 32 TABLESPACE IDX_RDS_CRCL
/
--> finished in 6 minute
2. alter index logging noparallel
ALTER INDEX SVC_CNTR_BY_CMSN_PAY_INFO_PK LOGGING NOPARALLEL
/
3. add pk constraint on the table
ALTER TABLE SVC_CNTR_BY_CMSN_PAY_INFO ADD (
CONSTRAINT SVC_CNTR_BY_CMSN_PAY_INFO_PK PRIMARY KEY
(CMSN_ACTRSL_YM, SVC_CNTR_NO, CMSN_CD, CMSN_PAY_ORGN_ID, OPEN_ORGN_ID))
/
--> takes very long time..
-------------
How can I speed up the "add pk constraint" on the table?
Is there any other way to create unique index and pk constraint on the table more efficiently??
Thanks in advance.
Best Regards.