eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 曲曲折折后的满座高朋 - 记与孟静之小聚 | Blog首页 | David.Guo的DBA招聘启示 地点广州 »

关于分区表与唯一索引的测试记录

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

非分区字段不能创建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.


历史上的今天...
    >> 2016-03-11文章:
    >> 2011-03-11文章:
    >> 2008-03-11文章:
    >> 2007-03-11文章:

无觅

By eygle on 2009-03-11 20:53 | Comments (0) | FAQ | 2225 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com