Add primary key in parallel

By default a PK is added seraily and parallel cannot be specified - so you would add a PK as below

alter table rds.EMA_MARK
  add constraint PK_EMA_MARK primary key (TRADE_DATE_KEY, DELIVERY_DATE_KEY, BOOK_KEY, MARKET_KEY, BUY_SELL); 


This was taking 90 minutes

So instead we do:

alter table rds.EMA_MARK
add constraint PK_EMA_MARK primary key (TRADE_DATE_KEY, DELIVERY_DATE_KEY, BOOK_KEY, MARKET_KEY, BUY_SELL) disable;

 To just create the definition but not actually create it

create unique index PK_EMA_MARK on rds.EMA_MARK (TRADE_DATE_KEY, DELIVERY_DATE_KEY, BOOK_KEY, MARKET_KEY, BUY_SELL) local parallel 8 nologging;

to add a unique index on the columns we want taking 11 minutes

Then activate the primary key which is instant

alter table rds.EMA_MARK enable primary key;

so we saved 80 minutes by doing it that way.

Beware though - index is now parallel 8 which may not be what you want and in may need altering to noparallel.

3 comments:

  1. very good...
    this can apply the partition table?

    ReplyDelete
  2. Hi,
    I think the test above was for a partitioned table (can't 100% remember - i think that's why it has the local clause in the one statement).

    I can't see why it wouldn't work though - the theory is the same.

    Regards,
    Rich

    ReplyDelete
    Replies
    1. I use this very approach on very large partitioned tables (TB range) in a data warehouse except I use regular indexes on the PK columns so that I can set them to unusable before each data load.

      Delete