This discussion is archived
6 Replies Latest reply: May 8, 2012 11:20 AM by user13512691 RSS

Best Practise for Add primary key in DWH

user13512691 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    give me some light guys :)
  • 4. Re: Best Practise for Add primary key in DWH
    912595 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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?

Legend

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