Faster Primary Key creation or enabling

Today, I have seen an application running a script to enable primary key constraint for a long time.   The question is how to make it faster.  In Data warehousing systems, it is normal that we disable Constraints, drop indexes and re-enable them after loading data.  So, making them faster is very important as the other jobs are dependent on the initial data load.  

I know this is nothing new for a DBAs, but many developers tends to over look this.

Here is my test case, initially prepared a new table with 7 million records in it.

SQL> create sequence my_num;

SQL> create table my_table as select my_num.nextval, OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,

OBJECT_TYPE,CREATED   from dba_objects;

SQL> insert into my_table select my_num.nextval,OWNER,OBJECT_NAME,OBJECT_ID,

138236 rows created.

SQL> commit ;

SQL> select count(*) from my_table;


Now my_table got 7.2 million records, good enough to show slow primary key creation 🙂 

SQL> alter table my_table add constraint pk_my_Pkey primary key  (NEXTVAL,OBJECT_ID);

Table altered.

Elapsed: 00:06:35.15

The normal primary key creation took 6 minutes and 35.15 seconds.  To create primary key it took 6.35 minutes!, I felt that is way too high.  So, how to improve the constraint creation timings.  The primary key creation to got two steps

  • A unique index creation
  • Enabling the constraint

   The maximum time took was to create the index not to enable the primary key.  If we make the index creation faster, all set.  So, I decided to break the constraint creation into 2 parts a) Index creation b) Enabling primary key.  Considering the type of tables – daily data load, can be re-created easily etc we need not keep recovery is in mind – if we lose the table we will create it.  Also, we have enough CPU resources while other jobs are waiting to complete the data load.

  • As the system resources under utilised, I will use enough parallelism
  • As the table can be re-crated easily, I will disable redo generation while index creation. 

 With the above assumtpions in hand, I had created the index and primary key as follows.

SQL> create index my_index on my_table (NEXTVAL,OBJECT_ID) NOLOGGING parallel 8;

Index created.

Elapsed: 00:02:06.75

SQL> alter table my_table enable constraint pk_my_Pkey using index my_index;

Table altered.

Elapsed: 00:00:05.02

Perfect, the constraint creation completed more efficiently with in 2.06.83 minutes, means 33% of the actual time!

  1. Chandra
    April 15, 2014 at 7:24 am

    Good topic

  2. March 26, 2015 at 5:26 pm

    Great idea!!!

  3. Feng
    July 18, 2016 at 9:19 pm

    A Primary Key constraint and a Clustered Index is not really the same in SQL Server. By default SQL Server enforces the Primary Key constraint with a Unique Clustered Index. But if you want you could instead enforce it with a Unique Non-Clustered Index. But that approach wouldn’t really make sense as a default, because you need to have specific problems to justify that approach.

    • July 19, 2016 at 6:26 am

      Not sure about SQL Server as I have tested this approach in Oracle. Sorry, I should have mentioned about it.

  4. August 14, 2017 at 2:24 pm

    Have you tested this with dropping the index and recreating it instead of merely enabling it?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: