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
"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