This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Mar 28, 2011 1:40 PM by EdStevens RSS

Creating Primary Key

Ganesh Srivatsav Guru
Currently Being Moderated
I am trying to create primary key on a table, which is large - 18GB
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
It is taking for ever. I have gathered statistics on the table, but still no luck.

Is there any way to get this done faster?

Regards,

G.
  • 1. Re: Creating Primary Key
    758358 Pro
    Currently Being Moderated
    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
    /
    Thanks
    Paul
  • 2. Re: Creating Primary Key
    ChrisPaccagnini Newbie
    Currently Being Moderated
    when you create PK contraint it create automaticaly INDEX
    so create INDEX is not requiered
  • 3. Re: Creating Primary Key
    59640 Newbie
    Currently Being Moderated
    How long does it take?
  • 4. Re: Creating Primary Key
    Mark D Powell Guru
    Currently Being Moderated
    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
  • 5. Re: Creating Primary Key
    Ganesh Srivatsav Guru
    Currently Being Moderated
    Hi,

    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.

    any Ideas?
  • 6. Re: Creating Primary Key
    758358 Pro
    Currently Being Moderated
    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.
  • 7. Re: Creating Primary Key
    Ganesh Srivatsav Guru
    Currently Being Moderated
    When I try to create the unique index itself It is taking very long
    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
    started this almost 25 mins back.

    G.
  • 8. Re: Creating Primary Key
    758358 Pro
    Currently Being Moderated
    How many rows in the table?
  • 9. Re: Creating Primary Key
    Ganesh Srivatsav Guru
    Currently Being Moderated
    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

    Thanks

    G.
  • 10. Re: Creating Primary Key
    USER101 Explorer
    Currently Being Moderated
    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
  • 11. Re: Creating Primary Key
    758358 Pro
    Currently Being Moderated
    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.

    Thanks
    Paul
  • 12. Re: Creating Primary Key
    Ganesh Srivatsav Guru
    Currently Being Moderated
    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.

    Any Ideas?

    G.
  • 13. Re: Creating Primary Key
    ErikaWhittome Newbie
    Currently Being Moderated
    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
  • 14. Re: Creating Primary Key
    adnanKaysar Explorer
    Currently Being Moderated
    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.

    regards
    adnankaysar
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points