This content has been marked as final. Show 19 replies
You coudl try creating the index itself first using parallelism then create the constraint afterwards which should indicate to oracle it can re-use the existing index e.g.
CREATE UNIQUE INDEX PK_WIP_HIST_ERROR ON DCS2000.TBL_WIP_HIST_ERROR ("PARENT_ID","CLAIM_NO", "WIP_ERROR_KEY") PARALLEL 8 TABLESPACE PRODIX / ALTER INDEX PK_WIP_HIST_ERROR noparallel / ALTER TABLE DCS2000.TBL_WIP_HIST_ERROR ADD CONSTRAINT PK_WIP_HIST_ERROR PRIMARY KEY ("PARENT_ID","CLAIM_NO","WIP_ERROR_KEY") USING INDEX PK_WIP_HIST_ERROR /
when you create PK contraint it create automaticaly INDEX
so create INDEX is not requiered
How long does it take?
One way to speed up an index build is to increase the sort_area_size for the session performing the create index/alter index rebuild. Depending on the database memory management in use you may first need to alter the session to use a manual work area policy.
alter session set sort_area_size = 100000000;
HTH -- Mark D Powell --
edit You can do this and use the PQO option as recommended in another post. With 18G using both options would probably be good but keep in mind your total memory limitations.
Edited by: Mark D Powell on Mar 9, 2010 6:27 AM
Really apologize for posting this without proper information.
The table is partitioned on PARENT_ID (list) and have only one partition right now though.
I am using Oracle 10g v 10.2.0.4
It is not taking long time, but it is taking for ever. I have waited for 4 hours, but no luck.
I'd definitely recommend using the parallel index creation approach...the straight constraint creation will be serial so much slower if you have a multi-processor machine.
When I try to create the unique index itself It is taking very long
started this almost 25 mins back.
SID SEQ# EVENT WAIT_TIM SECONDS_IN_WAIT STATE 1020 19960 direct path read -1 0 WAITED SHORT TIME 1021 4883 PX Deq: Execute Reply 0 0 WAITING 1029 18185 PX Deq: Table Q Normal 0 0 WAITING 1039 27230 PX Deq: Table Q Normal 0 0 WAITING 1065 19634 PX Deq Credit: need buffer -1 0 WAITED SHORT TIME 1072 16284 PX Deq Credit: need buffer -1 0 WAITED SHORT TIME 1078 43423 PX Deq: Table Q Normal 0 0 WAITING
How many rows in the table?
number of rows 128610470
and please note that the table is partitioned on PARENT_ID list and have only one partition now , PARENT_ID=1
Check in v$session_longops view. That should give you an idea as to what it's doing.. If it is slow sort performance, then could be due to two passs sorting to temp.. Post the contents here
Running it PARALLEL will definitely speed up index creation bit I have seen strange plans afterwords, even if you set to NOPARALLEL
Creating an index on an 128 million row table is never going to be fast, parallelism is a recognised way of speeding these sort of operations up, I'd say you will get much better than your previous 4 hours + when creating it in parallel.
The problem is I have to make these kinds of operations on similar tables.
What is the best approach for that?
Removing the data tempororily and performing these operations?
These are about 50 tables that needs to get processed.
For a table with 128 million records try the "enable novalidate" eg:
ALTER TABLE "DCS2000"."TBL_WIP_HIST_ERROR"
ADD CONSTRAINT "PK_WIP_HIST_ERROR" PRIMARY KEY ("PARENT_ID","CLAIM_NO", "WIP_ERROR_KEY")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 209715200 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PRODIX" ENABLE NOVALIDATE
it will take time as you are creating Constraint to a large table with "enable validate"( by default) with COMPUTE STATISTICS , try compute the statistics later
it is best practice to create pk constraint to a large table with DISABLE NOVALIDATE, the enable it.