关于分区表与唯一索引的测试记录
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.
By eygle on 2009-03-11 20:53 |
Comments (0) |
FAQ | 2225 |