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

上一篇 / 下一篇 2007-02-12 00:00:00 / 个人分类：Oracle

实际上，主键的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.