Oracle 关于在分区表上重新创建主键的问题

当我们在分区表上重新创建复合主键时，创建的唯一索引会发生什么情况，请注意，我们正在使用 “启用novalidate” 子句重新创建，以便不检查现有行的有效性。

“视情况而定”;)

有两件事需要考虑:

-如何创建主键索引
-是否提供 (keep | drop) 索引子句

如果您依赖于隐式索引创建，那么当您删除约束时，索引将同时进行:

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 

但是，如果您事先创建了索引并指示Oracle数据库使用该索引，则删除主键将使索引保持不变:

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 

您可以通过在删除PK时添加 (keep | drop) 索引子句来更改此行为:

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 

无论您是否使用分区表，都会发生这种情况。

在重新创建PK时，您还需要考虑PK列上是否存在索引。如果没有，那么Oracle数据库将为您创建一个唯一的索引。因此，指定 “启用novalidate” 不会为您节省很多。它仍然需要做的工作来建立索引:

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

即使您使用了 “启用novalidate”，也要超过4分钟才能添加PK

但是!

如果您的表在开始时具有PK列的索引，则Oracle数据库可以使用该索引，从而再次绕过验证:

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

注意: 它不一定是主键的唯一索引才能使用它:

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  

但如果你的表是分区的，它必须是一个全局索引:

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