继续上一篇的测试。前面提到了用户创建的索引和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优先选择索引列的个数和列的顺序与约束一致的。