oracle唯一本地索引,oracle 分区本地唯一索引规则测试

关于分区表与唯一索引，以下是一点测试记录。

非分区字段不能创建Local Unique索引：

SQL> connect eygle/eygle

Connected.

SQL> CREATE TABLE docinfo(

2  docid    NUMBER ,

3  docsz    NUMBER ,

4  CONSTRAINT uk_docid UNIQUE(docid) USING INDEX LOCAL)

5  PARTITION BY RANGE(docsz)

6  (PARTITION p1 VALUES LESS THAN (100),

7  PARTITION p2 VALUES LESS THAN(MAXVALUE));

CREATE TABLE docinfo(

*

ERROR at line 1:

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

当然此时可以创建全局Unique索引：

SQL> CREATE TABLE docinfo(

2  docid    NUMBER ,

3  docsz    NUMBER ,

4  CONSTRAINT uk_docid UNIQUE(docid) USING  INDEX )

5  PARTITION BY RANGE(docsz)

6  (PARTITION p1 VALUES LESS THAN (100),

7  PARTITION p2 VALUES LESS THAN(MAXVALUE));

Table created.

可以在分区字段上创建Local Unique索引：

SQL> CREATE TABLE docinfo(

2  docid    NUMBER ,

3  docsz    NUMBER ,

4  CONSTRAINT uk_docsz UNIQUE(docsz) USING  INDEX LOCAL)

5  PARTITION BY RANGE(docsz)

6  (PARTITION p1 VALUES LESS THAN (100),

7  PARTITION p2 VALUES LESS THAN(MAXVALUE));

Table created.

SQL> drop table docinfo;

Table dropped.

或者可以创建包含分区字段的联合Unique索引：

SQL> CREATE TABLE docinfo(

2  docid    NUMBER ,

3  docsz    NUMBER ,

4  CONSTRAINT uk_docsz UNIQUE(docid,docsz) USING  INDEX LOCAL)

5  PARTITION BY RANGE(docsz)

6  (PARTITION p1 VALUES LESS THAN (100),

7  PARTITION p2 VALUES LESS THAN(MAXVALUE));

Table created.

SQL> drop table docinfo;

Table dropped.