6 Replies Latest reply: May 8, 2012 1:20 PM by User13512691-Oracle RSS

    Best Practise for Add primary key in DWH

    User13512691-Oracle
      Hi,

      I have a question and need you suggestions

      Version - 11.2.0.3

      Table size - 1.5TB

      I have a DWH table which is huge. This table is partitioned and now i want to add constraint on this table. So what are the best approach for this to fasten the process of adding a primary key constraint?

      I'm think of

      1) create unique index
      2) add primary key using above index

      Here step 1 i can do it in parallel and expolite full power of machine. But i'm not to much sure above second step. Since step 2 you cannot do in parallel.

      It seems second step would validate each and every row in table for adding primary key constraint. NOVALIDATE also would not be used because in case of PK it has to validate every row. Using RELY also wont help much to increase performance.

      I want to know your experiences on this and help what would be best way to achieve this? Think of table size more then 10TB then what would be solution
        • 1. Re: Best Practise for Add primary key in DWH
          AliD
          AFAIK once the index is there, creating the primary key should be instant. The index already forces the validation of the data. I assume the said index is created on the PK columns.

          Also give bitmap index a try. It could surprise you with its size and performance.
          • 2. Re: Best Practise for Add primary key in DWH
            User13512691-Oracle
            AliD wrote:
            AFAIK once the index is there, creating the primary key should be instant.
            This is what i though initially but not going to happen.
            The index already forces the validation of the data. I assume the said index is created on the PK columns.
            Yes unique index is created on key which i suppose to be primary key
            >
            Also give bitmap index a try. It could surprise you with its size and performance.
            The columns which i suppose to add is having hugh numbers of distinct rows, so i dont think this would be feasible to create bitmap
            • 3. Re: Best Practise for Add primary key in DWH
              User13512691-Oracle
              give me some light guys :)
              • 4. Re: Best Practise for Add primary key in DWH
                912595
                Yes,

                I think will will validate rows before adding primary key.
                Even if you have already existing unique index. Please try and update us
                • 5. Re: Best Practise for Add primary key in DWH
                  rp0428
                  >
                  It seems second step would validate each and every row in table for adding primary key constraint.
                  >
                  Only if the column(s) are nullable that comprise the primary key.

                  If the columns are NOT NULL then no validation is required once the unique index has been created.

                  Bitmap indexes cannot be used to enforce primary key constraints.

                  NOTE: Performance of the partitioned table may be affected since you will now have a global index that did not exist before. Make sure you have taken that into account.

                  That is because for any DML that affects the primary key (primarily INSERT/DELETE) the global index has to be updated. This includes loading of partitions using INSERT or using partition exchange. Such loads cannot use direct-path.

                  The only way that can be avoided is if the primary key columns are included in the partition key. Since you have an existing partitioned table that may not be the case.
                  • 6. Re: Best Practise for Add primary key in DWH
                    User13512691-Oracle
                    rp0428 wrote:
                    >
                    It seems second step would validate each and every row in table for adding primary key constraint.
                    >
                    Only if the column(s) are nullable that comprise the primary key.
                    OK
                    >
                    If the columns are NOT NULL then no validation is required once the unique index has been created.
                    OK, the column was not defined as NOT NULL, so maybe thats why upon adding a constraint on existing local partition index its checking for NULL values.
                    Bitmap indexes cannot be used to enforce primary key constraints.
                    Aware of that
                    NOTE: Performance of the partitioned table may be affected since you will now have a global index that did not exist before. Make sure you have taken that into account.
                    I didn't create GLOBAL index. I create a unique partition index using LOCAL keyword which means its a local partitioned index.
                    That is because for any DML that affects the primary key (primarily INSERT/DELETE) the global index has to be updated. This includes loading of partitions using INSERT or using partition exchange. Such loads cannot use direct-path.
                    Same as above
                    The only way that can be avoided is if the primary key columns are included in the partition key. Since you have an existing partitioned table that may not be the case.
                    Yes my partition key is in leading column of primary key.

                    Now since you have said oracle will check the nullable values while adding the constraint, so how can i skip this validation? Do i need to create a not null constraint first or how?