« 曲曲折折后的满座高朋 - 记与孟静之小聚 | Blog首页 | David.Guo的DBA招聘启示 地点广州 »
关于分区表与唯一索引的测试记录
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2009/03/partition_unique_index.html
关于分区表与唯一索引,以下是一点测试记录。链接:https://www.eygle.com/archives/2009/03/partition_unique_index.html
非分区字段不能创建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.
历史上的今天...
>> 2017-03-11文章:
>> 2016-03-11文章:
>> 2011-03-11文章:
>> 2008-03-11文章:
>> 2007-03-11文章:
By eygle on 2009-03-11 20:53 | Comments (0) | FAQ | 2225 |