March 11, 2009
关于分区表与唯一索引的测试记录
作者:eygle
出处:http://blog.eygle.com
关于分区表与唯一索引，以下是一点测试记录。非分区字段不能创建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.
Posted by eygle at 8:53 PM | Comments (0)