Oracle索引梳理系列（七）- Oracle唯一索引、普通索引及约束的关系

2022-03-23 23:25:53

版权声明：本文发布于http://www.cnblogs.com/yumiko/，版权由Yumiko_sunny所有，欢迎转载。转载时，请在文章明显位置注明原文链接。若在未经作者同意的情况下，将本文内容用于商业用途，将保留追究其法律责任的权利。如果有问题，请以邮箱方式联系作者（793113046@qq.com）。

Oracle唯一索引、普通索引及约束的关系

在总结索引扫描类型前（不同于前面总结的五大类索引类型，索引类型主要是索引类别的划分，而索引扫描类型是索引在进行索引扫描时的具体方法），需要了解唯一索引、非唯一索引（普通索引）以及约束的关系。这是因为对于索引扫描类型的具体探讨上，需要根据“唯一索引”、“非唯一索引（普通索引）”以及“约束”，这三个概念的具体情况，进行具体说明，因此优先进行总结。

1、唯一索引与普通索引的概述

对于索引，如b-tree索引，可以根据具体的情况，可以创建唯一索引（create unique index）或者普通索引（create index）。

默认情况下，通过create index 创建的索引，属于非唯一索引。

2、唯一索引与普通索引的区别

对于唯一索引与普通索引，最大的区别在于：

1）对于存在唯一索引的索引列，该列相当于增加了唯一约束。既该列的列值必须唯一，null值除外。

2）对于存在唯一索引的索引列，该列的列值可以为空。但主键约束（约束列值唯一且非空）的列不能有空值。

示例：

准备两张结构、数据相同的表test_normal、test_primary，然后针对empno列，分别添加普通索引和唯一索引，观察约束情况。

--查看两张表上的约束情况，此时两张表无任何约束
Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

no rows selected

--为表test_normal的empno列，创建普通索引

Yumiko@Sunny >create index ind_test_normal_empno on test_normal(empno);

Index created.

--为表test_primary的empno列，创建唯一索引

Yumiko@Sunny >create unique index ind_test_primary_empno on test_primary(empno);

Index created.

--通过视图user_indexes，确认两个创建索引的唯一性
--可以看到不加unique创建的索引属于非唯一的b-tree索引（index_type列未列出，此时应显示为normal）
--而加unique参数的索引属于唯一索引

Yumiko@Sunny >select index_name,table_name,UNIQUENESS from user_indexes where table_name  like 'TEST_%';

INDEX_NAME                     TABLE_NAME                     UNIQUENES

------------------------------ ------------------------------ ---------

IND_TEST_PRIMARY_EMPNO         TEST_PRIMARY                   UNIQUE

IND_TEST_NORMAL_EMPNO          TEST_NORMAL                    NONUNIQUE

--查看此时的两张表的约束情况，未发现明显的约束建立
Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

no rows selected

--下面通过数据操作，验证创建唯一索引，对于列值的产生了唯一约束

--查询创建普通索引的表test_normal的第一行数据，并插入相同的数据行信息
Yumiko@Sunny >select * from TEST_NORMAL where rownum=1;

     EMPNO ENAME      JOB              SAL

---------- ---------- --------- ----------

      7369 SMITH      CLERK            800

Yumiko@Sunny >insert into TEST_NORMAL values(7369,'aaa','aaaaa',1000);

1 row created.

--此时数据插入成功

--查询创建唯一索引的表test_primary的第一行数据，同样插入相同的数据行信息

Yumiko@Sunny >select * from TEST_PRIMARY where rownum=1;

     EMPNO ENAME      JOB              SAL

---------- ---------- --------- ----------

      7369 SMITH      CLERK            800

Yumiko@Sunny >insert into TEST_PRIMARY values(7369,'aaa','aaaaa',1000);

insert into TEST_PRIMARY values(7369,'aaa','aaaaa',1000)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.IND_TEST_PRIMARY_EMPNO) violated
--此时插入数据失败，并报ora-00001唯一键约束冲突的错误
--此处证明了，当创建唯一索引时，会为该列增加唯一约束

--为存在唯一索引的表test_primary，插入两条索引列存在null值的数据行

Yumiko@Sunny >insert into TEST_PRIMARY values(null,'aaa','aaaaa',1000);

1 row created.

Yumiko@Sunny >insert into TEST_PRIMARY values(null,'bbb','bbbbb',1000);

1 row created.
--此处证明了对于唯一索引，在数据列没有非空约束的前提下，可以插入空值，且可以插入多个空值。

从上面的示例中可以看到，虽然添加唯一索引后，无法在dba_constraints或者user_constraints视图中看到具体的约束。但具体到表的dml操作，可以看到无法针对唯一索引列，添加相同的数据。但空值（null）可以添加，且可以存在多个空值。

3、约束与索引的关系

对于建立主键约束或者唯一键约束的列，会自动为该列创建索引，且该索引属于唯一索引。

示例：

同样准备两张结构、数据相同的表test_primary、test_unique，然后针对empno列，分别添加主键约束以及唯一键约束，观察索引情况。

--观察两张表的约束以及索引情况
Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS,b.STATUS INDEX_STATUS,a.GENERATED

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

no rows selected

--针对表test_primary添加主键约束

Yumiko@Sunny >alter table TEST_PRIMARY add primary key(empno);

Table altered.

--针对表test_unique添加唯一键约束

Yumiko@Sunny >alter table TEST_UNIQUE add unique(empno);

Table altered.

--再次查询两个表的约束以及索引情况。
--从显示不难看出，当创建主键约束或者是唯一键约束时，oracle会自动创建一个同名的索引，且该索引为唯一索引。
--generated列同样证明了，索引是自动创建的。
Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS,b.STATUS INDEX_STATUS,a.GENERATED

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

TABLE_NAME      CONSTRAINT_NAME C CONSTRAI INDEX_NAME      INDEX_UNI INDEX_ST GENERATED

--------------- --------------- - -------- --------------- --------- -------- --------------

TEST_UNIQUE     SYS_C005426     U ENABLED  SYS_C005426     UNIQUE    VALID    GENERATED NAME

TEST_PRIMARY    SYS_C005427     P ENABLED  SYS_C005427     UNIQUE    VALID    GENERATED NAME

从上面可以看到，当主键约束或唯一约束创建时，oracle会自动为该列创建一个唯一索引。

继续往下，禁用或者删除约束，观察索引情况。

--查看两个表的约束及索引。
Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS,b.STATUS INDEX_STATUS,a.GENERATED

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

TABLE_NAME      CONSTRAINT_NAME C CONSTRAI INDEX_NAME      INDEX_UNI INDEX_ST GENERATED

--------------- --------------- - -------- --------------- --------- -------- --------------

TEST_UNIQUE     SYS_C005426     U ENABLED  SYS_C005426     UNIQUE    VALID    GENERATED NAME

TEST_PRIMARY    SYS_C005427     P ENABLED  SYS_C005427     UNIQUE    VALID    GENERATED NAME

Yumiko@Sunny >select index_name,status,dropped from user_indexes;

INDEX_NAME      STATUS   DRO

--------------- -------- ---

SYS_C005426     VALID    NO

SYS_C005427     VALID    NO

PK_EMP          VALID    NO

BIG_EMP         VALID    NO

PK_DEPT         VALID    NO

--禁用表test_primary的主键约束

Yumiko@Sunny >alter table TEST_PRIMARY disable CONSTRAINT SYS_C005427;

Table altered.

--删除表test_unique的唯一键约束

Yumiko@Sunny >alter table TEST_UNIQUE drop CONSTRAINT SYS_C005426;

Table altered.

--此时再次查询两个表的约束以及索引情况。
--不难发现，当禁用或者删除主键约束或者唯一键约束后，相应的索引被删除。

Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS,b.STATUS INDEX_STATUS,a.GENERATED

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

no rows selected

Yumiko@Sunny >select index_name,status,dropped from user_indexes;

INDEX_NAME      STATUS   DRO

--------------- -------- ---

PK_EMP          VALID    NO

BIG_EMP         VALID    NO

PK_DEPT         VALID    NO

从上面可以明显看到，当主键约束或者唯一键约束禁用或者删除后，相应的索引会被删除。

4、总结

综合上面的内容，总结如下：
1）默认创建的索引为非唯一索引。
2）在索引列上建立唯一索引，会增加唯一约束。该列的列值可以为空（若该列存在主键约束除外），但必须唯一。
3）当主键约束或者唯一键约束建立后，会自动为该列创建唯一索引。
4）当主键约束或者唯一键约束禁用或者删除后，相应的索引会被删除。

5、特殊情况

当在创建主键约束或唯一键约束前，该列创建了普通索引（非唯一索引），虽然约束创建后，该列的键值会具有唯一约束。但引用的索引会使非唯一索引。

--创建普通索引
Yumiko@Sunny >create index ind_test_normal_empno on test_normal(empno);

Index created.

--再次确认之前创建的普通索引

Yumiko@Sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS from user_indexes where TABLE_NAME='TEST_NORMAL';

INDEX_NAME                INDEX_TYPE                  TABLE_NAME      UNIQUENES

------------------------- --------------------------- --------------- ---------

IND_TEST_NORMAL_EMPNO     NORMAL                      TEST_NORMAL     NONUNIQUE

--为之前创建普通索引的列添加唯一键约束

Yumiko@Sunny >alter table TEST_NORMAL add CONSTRAINT ind_test_normal_empno_unique unique(empno);

Table altered.

--查看此时约束以及索引的情况
--注意，一般情况下，当在为建立索引的列上添加唯一键约束时，会自动创建一个唯一索引。
--当建立唯一键约束时，如果该列存在索引，无论是唯一索引或者普通索引，都会被唯一键约束使用。此处使用的之前创建的普通索引。

Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS,b.STATUS INDEX_STATUS,a.GENERATED

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

TABLE_NAME      CONSTRAINT_NAME C CONSTRAI INDEX_NAME                INDEX_UNI INDEX_ST GENERATED

--------------- --------------- - -------- ------------------------- --------- -------- --------------

TEST_NORMAL     IND_TEST_NORMAL U ENABLED  IND_TEST_NORMAL_EMPNO     NONUNIQUE VALID    USER NAME

                _EMPNO_UNIQUE

--尝试添加重复的列值，提示唯一约束冲突

Yumiko@Sunny >insert into TEST_NORMAL values(7369,1,1,1);

insert into TEST_NORMAL values(7369,1,1,1)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.IND_TEST_NORMAL_EMPNO_UNIQUE) violated

--虽然索引上存在唯一约束，但由于先前建立的普通索引（非唯一）被使用，导致oracle在选择执行计划时，采用了index range scan的方式。

Yumiko@Sunny >set autotrace trace

Yumiko@Sunny >select * from TEST_NORMAL where empno=7369;

Execution Plan

----------------------------------------------

Plan hash value: 2754332829

-------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Rows | Bytes| Cost (%CPU)| Time    |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |     1|    39|     1   (0)| 00:00:01|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_NORMAL          |     1|    39|     1   (0)| 00:00:01|

|*  2 |   INDEX RANGE SCAN          | IND_TEST_NORMAL_EMPNO|     1|      |     0   (0)| 00:00:01|

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7369)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        717  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--删除约束及索引

Yumiko@Sunny >alter table TEST_NORMAL drop CONSTRAINT ind_test_normal_empno_unique;

Table altered.

Yumiko@Sunny >drop index IND_TEST_NORMAL_EMPNO;

Index dropped.

--直接创建唯一键约束，进而由oracle自动创建唯一索引

Yumiko@Sunny >alter table TEST_NORMAL add CONSTRAINT ind_test_normal_empno_unique unique(empno);

Table altered

--验证约束以及索引，此时唯一键约束列上的索引属于唯一索引

Yumiko@Sunny >select a.TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,a.STATUS CONSTRAINT_STATUS,

  2  b.INDEX_NAME,UNIQUENESS INDEX_UNIQUENESS,b.STATUS INDEX_STATUS,a.GENERATED

  3  from user_constraints a,user_indexes b

  4  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME like '%TEST_%';

TABLE_NAME      CONSTRAINT_NAME C CONSTRAI INDEX_NAME                INDEX_UNI INDEX_ST GENERATED

--------------- --------------- - -------- ------------------------- --------- -------- ----------

TEST_NORMAL     IND_TEST_NORMAL U ENABLED  IND_TEST_NORMAL_EMPNO_UNI UNIQUE    VALID    USER NAME

                _EMPNO_UNIQUE              QUE

--由于此时索引列上的索引是唯一索引，oracle在选择执行计划时，会选择采用index unique scan的方式。

Yumiko@Sunny >select * from TEST_NORMAL where empno=7369;

Execution Plan

----------------------------------------------

Plan hash value: 1065970114

-------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                        | Rows |Bytes| Cost (%CPU)| Time    |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                             |     1|   39|     1   (0)| 00:00:01|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_NORMAL                 |     1|   39|     1   (0)| 00:00:01|

|*  2 |   INDEX UNIQUE SCAN         | IND_TEST_NORMAL_EMPNO_UNIQUE|     1|     |     0   (0)| 00:00:01|

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EMPNO"=7369)

Statistics

---------------------------------------------------

        192  recursive calls

          0  db block gets

         33  consistent gets

          0  physical reads

          0  redo size

        581  bytes sent via SQL*Net to client

        458  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed
上一篇：easyui-datetimebox设置默认时分秒00:00:00


下一篇：云栖号2019云计算盘点-【金融行业上云篇】