分区表主键索引分区的前提是包含分区字段或本身就是分区字段

最新推荐文章于 2023-02-02 11:02:56 发布

原创最新推荐文章于 2023-02-02 11:02:56 发布 · 3.1k 阅读

CC 4.0 BY-SA版权

本文通过实例演示了在Oracle数据库中尝试将全局索引转换为本地分区索引时遇到的问题，特别是当主键未包含分区字段时的情况。文章详细记录了创建分区表、插入数据、建立主键约束的过程，并展示了在尝试使用不含分区字段的主键建立本地索引时出现的错误。

场景：为了日常维护方便，同事将一大表全局索引都转为LOCAL本地索引，但由于主键不包含分区字段导致不能转成LOCAL属性；

测试验证如下：

SQL> create table pt
  2   (
  3          id number,
  4          object_id number,
  5          object_name varchar2(40)
  6   )
  7   partition by range(object_id)
  8   (
  9    partition T1 values less than (100) ,
 10    partition T2 values less than (10000) ,
 11    partition T3 values less than (maxvalue) 
 12    );
  
Table created.

SQL> 
SQL> 
SQL> create sequence seq01 start with 1  increment by 1 ;

Sequence created.

SQL> insert into pt select seq01.nextval ,object_id,object_name from dba_objects where object_id is not null;

75216 rows created.

SQL> commit;

Commit complete.

SQL>  alter table pt add constraint pk_pt_id primary key(id) using index local;       
 alter table pt add constraint pk_pt_id primary key(id) using index local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index


SQL> alter table pt add constraint pk_pt_id primary key(id,object_id) using index local;

Table altered.

SQL> 
SQL> alter table pt drop primary key;

Table altered.

SQL> alter table pt add constraint pk_pt_object_id primary key(object_id) using index local;

Table altered.