程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle索引總結（七）- Oracle唯一索引、普通索引及約束的關系，oracle索引

Oracle索引總結（七）- Oracle唯一索引、普通索引及約束的關系，oracle索引

編輯：Oracle教程

Oracle索引總結（七）- Oracle唯一索引、普通索引及約束的關系，oracle索引

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