martedì 23 giugno 2015

Oracle Tips: creating a Primary Key over a big big table

I am currently working on an Exadata machine and trying to add a Primary Key (henceforth, PK) to a table that currently stores 629 milions of rows.
First attempt, I have simply executed the following statement:


alter table TAB add primary key (col_1, col_2, ... , col_n );


Big fail! The query run for more than 24 hours. It did not complete and I had to kill it manually.
Second attempt. Use the DISABLE NOVALIDATE option.

alter table TAB add constraint "PK_<name>" primary key  (col_1, col_2, ... , col_n ) DISABLE NOVALIDATE;

The creation itself is fast. The issue comes when you enable the key. It took forever again and I had to abort the process.
I finally decided to  adopt the CREATE INDEX strategy. Basically, you first create an index with the columns you want to be part of the primary key.

create index MYIDX on TAB (col_1, col_2, ... , col_n ) PARALLEL 16;

And then use the index to be your PK. 

alter table TAB add constraint "PK_<name>" primary key  (col_1, col_2, ... , col_n ) using index MYIDX ;

This took minutes instead of hours and it really saved me!