ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 主键与索引(二)

主键与索引(二)

原创 Linux操作系统 作者:yangtingkun 时间:2011-06-10 23:59:17 0 删除 编辑

刚刚看完文档中这部分的描述,简单总结一下。如无特别说明,这里的主键泛指主键和唯一约束。

这一篇介绍通过USING INDEX的方法改变主键创建的索引的默认行为。

主键与索引(一):http://yangtingkun.itpub.net/post/468/519116

 

 

继续上一篇的测试。前面提到了用户创建的索引和Oracle自动创建的索引被其他约束使用时出现了差异。

下面尝试通过USING INDEX来强制ID上的唯一约束来使用这个索引:

SQL> DROP TABLE T PURGE;

表已删除。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30), PRIMARY KEY (ID, NAME));

表已创建。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007419                    P SYS_C007419

SQL> ALTER TABLE T ADD UNIQUE (ID) USING INDEX SYS_C007419;
ALTER TABLE T ADD UNIQUE (ID) USING INDEX SYS_C007419
*
1 行出现错误:
ORA-14196:
指定的索引不能用于强制约束条件。

SQL> SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
  2  FROM USER_IND_COLUMNS
  3  WHERE INDEX_NAME = 'SYS_C007419';

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
SYS_C007419                    ID                                           1
SYS_C007419                    NAME                                         2

可以看到,这里出现了错误。而事实上,这种差异并非是自动创建索引和用户创建索引的区别,而是唯一索引和非唯一索引的区别。

对于Oracle而言,唯一索引被用来作为主键约束,要求索引所有列都属于主键列,而不允许唯一索引的前缀列来支持其他唯一约束,而非唯一索引则没有这个限制,这在上一篇文章的例子中已经看到了。

关于唯一索引和非唯一索引对主键的支持,可以参考metalink文档ID 577253.1

SQL> DROP TABLE T PURGE;

表已删除。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE UNIQUE INDEX IND_T_ID_NAME ON T(ID, NAME);

索引已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID) USING INDEX IND_T_ID_NAME;
ALTER TABLE T ADD PRIMARY KEY (ID) USING INDEX IND_T_ID_NAME
*
1 行出现错误:
ORA-14196:
指定的索引不能用于强制约束条件。


SQL> ALTER TABLE T ADD PRIMARY KEY (ID, NAME) USING INDEX IND_T_ID_NAME;

表已更改。

因此,可以得到结论,Oracle在创建约束的时候默认会使用已有的索引,当已有索引不满足条件,或者用户明确指定的情况下,会新建索引。

而如果想要改变Oracle的默认行为,需要明确指定USING INDEX的方式:

SQL> DROP TABLE T PURGE;

表已删除。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE INDEX IND_T_ID_NAME ON T (ID, NAME);

索引已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID, NAME)
2 USING INDEX (CREATE INDEX IND_T_NAME_ID ON T(NAME, ID));

表已更改。

SQL> ALTER TABLE T ADD UNIQUE (ID) USING INDEX TABLESPACE TEST;

表已更改。

SQL> ALTER TABLE T ADD UNIQUE (NAME, ID);

表已更改。

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'T';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007422                    P IND_T_NAME_ID
SYS_C007441                    U SYS_C007441
SYS_C007443                    U IND_T_NAME_ID

SQL> SELECT INDEX_NAME, UNIQUENESS, TABLESPACE_NAME
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME = 'T';

INDEX_NAME                     UNIQUENES TABLESPACE_NAME
------------------------------ --------- --------------------------
SYS_C007441                    UNIQUE    TEST
IND_T_NAME_ID                  NONUNIQUE USERS
IND_T_ID_NAME                  NONUNIQUE USERS

这里给出了几种USING INDEX创建新索引的例子,而最后一个添加约束的语句说明,当多个满足条件的索引存在时,Oracle优先选择索引列的个数和列的顺序与约束一致的。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-697643/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10524511