怎么把分区表的主键(非分区字段)建成local索引

Itpub的一个网友提问：怎么把分区表的主键(非分区字段)建成local索引。

实际上，主键的index，如果没有包含分区键，不能是local的，因为无法执行唯一性约束的检查，所以必须是global的。

create">SYS@ning>create table test_local(id int, dt date,name varchar2(20))
2 partition by range(dt)
3 (partition p1 values less than(to_date('2005/01/01','yyyy/mm/dd')),
4 partition p2 values less than(to_date('2006/01/01','yyyy/mm/dd')),
5 partition p3 values less than(to_date('2007/01/01','yyyy/mm/dd')));

Table created.

create">SYS@ning>create index ix_test_local on (id) local;
create index ix_test_local on (id) local
*
ERROR at line 1:
ORA-00903: invalid table name

create">SYS@ning>create index ix_test_local on test_local(id) local;

Index created.

alter">SYS@ning>alter table test_local add primary key(id) using index ix_test_local;
alter table test_local add primary key(id) using index ix_test_local
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

drop">SYS@ning>drop index ix_test_local;

Index dropped.

create">SYS@ning>create index ix_test_local on test_local(id) global;

Index created.

alter">SYS@ning>alter table test_local add primary key(id) using index ix_test_local;

Table altered.

drop">SYS@ning>drop index ix_test_local;

Index dropped.

create">SYS@ning>create index ix_test_local on test_local(id,dt) local;

Index created.

alter">SYS@ning>alter table test_local add primary key(id,dt) using index ix_test_local;

Table altered.

所以，解决方法，要么使用global index，要么主键包含分区键。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50283/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50283/