1 2 Previous Next 19 Replies Latest reply on Mar 28, 2011 8:40 PM by EdStevens

    Creating Primary Key

    Ganesh Srivatsav
      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
          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
            when you create PK contraint it create automaticaly INDEX
            so create INDEX is not requiered
            • 3. Re: Creating Primary Key
              59640
              How long does it take?
              • 4. Re: Creating Primary Key
                Mark D Powell
                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
                  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
                    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
                      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
                        How many rows in the table?
                        • 9. Re: Creating Primary Key
                          Ganesh Srivatsav
                          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
                            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
                              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
                                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
                                  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
                                    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