Skip to Main Content
  • Questions
  • Question on recreating a primary key on a partitioned table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Thejas.

Asked: March 08, 2017 - 1:17 am UTC

Last updated: March 08, 2017 - 11:38 am UTC

Version: Oracle exadata

Viewed 1000+ times

You Asked

What happens to the unique index that gets created when we recreate a primary key which is composite on a partitioned table, please note that we are using the "enable novalidate" clause to recreate so that existing rows are not checked for the validity of the new primary key created

and Chris said...

"It depends" ;)

There are two things to consider:

- How you created the primary key index
- Whether you provide a (keep|drop) index clause

If you relied on implicit index creation, then when you drop the constraint the index will go at the same time:

drop table t purge;
create table t (
  x int not null constraint pk primary key
);

select index_name, uniqueness from user_indexes
where  table_name = 'T';

INDEX_NAME  UNIQUENESS  
PK          UNIQUE     

alter table t drop primary key;

select index_name, uniqueness from user_indexes
where  table_name = 'T';

 0 rows selected 


But if you created the index beforehand and instructed Oracle Database to use that, dropping the primary key leaves the index intact:

drop table t purge;
create table t (
  x int not null
);

create unique index i on t(x);
alter table t add constraint pk primary key (x) using index i;

select index_name, uniqueness from user_indexes
where  table_name = 'T';

INDEX_NAME  UNIQUENESS  
I           UNIQUE  

alter table t drop primary key;

select index_name, uniqueness from user_indexes
where  table_name = 'T';

INDEX_NAME  UNIQUENESS  
I           UNIQUE 


You can change this behaviour by adding the (keep|drop) index clause when you remove the PK:

drop table t purge;
create table t (
  x int not null constraint pk primary key
);

alter table t drop primary key keep index;

select index_name, uniqueness from user_indexes
where  table_name = 'T';

INDEX_NAME  UNIQUENESS  
PK          UNIQUE 

drop table t purge;
create table t (
  x int not null
);

create unique index i on t(x);
alter table t add constraint pk primary key (x) using index i;

alter table t drop primary key drop index;

select index_name, uniqueness from user_indexes
where  table_name = 'T';

 0 rows selected 


This happens whether you use a partitioned table or not.

When it comes to recreating the PK, you also need to consider whether there are existing indexes on the PK column(s). If there isn't, then Oracle Database will create a unique index for you. So specifying "enable novalidate" won't save you much. It still has to do the work to build the index:

set timing on
drop table t purge;
create table t as
with rws as (
  select level x from dual connect by level <= 10000
)
  select rownum x from rws, rws;

Elapsed: 00:01:13.168

alter table t add constraint pk primary key (x) enable novalidate;

Elapsed: 00:04:08.367


That's over 4 minutes to add the PK, even though you used "enable novalidate"

But!

If your table has an index with the PK column(s) at the start, Oracle Database can use that, bypassing the validation again:

alter table t drop primary key keep index;

Elapsed: 00:00:00.165

alter table t add constraint pk primary key (x) enable novalidate;

Elapsed: 00:00:00.178


Note: it doesn't have to be a unique index for the primary key to use it:

drop table t purge;
create table t (
  x int not null,
  y int
);

create index i on t(x, y);
alter table t add constraint pk primary key (x) enable novalidate;

select index_name, uniqueness from user_indexes
where  table_name = 'T';

INDEX_NAME  UNIQUENESS  
I           NONUNIQUE  


But if your table is partitioned, it does have to be a global index:

drop table t purge;
create table t (
  x int not null,
 y int
) partition by range (y) (
  partition p0 values less than (1)
);

create index i on t(x, y) local;
alter table t add constraint pk primary key (x) enable novalidate;

select index_name, uniqueness, partitioned from user_indexes
where  table_name = 'T';

INDEX_NAME  UNIQUENESS  PARTITIONED  
I           NONUNIQUE   YES          
PK          UNIQUE      NO   

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thejas HM, March 09, 2017 - 1:14 am UTC

Thanks....it was indeed helpful....

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.