关于分区表与唯一索引,以下是一点测试记录。
非分区字段不能创建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.