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,
DATA_OBJECT_ID,OBJECT_TYPE,CREATED from my_table;  2

138236 rows created.

SQL> commit ;

SQL> select count(*) from my_table;

  COUNT(*)
———-
   7282377

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!