主键与索引（二）

最新推荐文章于 2022-02-10 17:08:59 发布

ckawt40802

最新推荐文章于 2022-02-10 17:08:59 发布

阅读量308

点赞数

文章标签：数据库

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

这一篇介绍通过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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-697643/