[原]unique index和non unique index的區別

      今天做Schema評審的時候發現一個很奇怪的現象，也許是用工具生成的SQL語句，清一色的如下：

CREATE TABLE table_name (
	id			NUMBER			NOT NULL,
	......
	......
) ;

CREATE INDEX table_name_PK ON table_name(ID) ;
ALTER TABLE table_name 
  ADD CONSTRAINT table_name_PK PRIMARY KEY (ID) 
  USING INDEX table_name_PK ;

      通常來說主鍵（Primary Key，PK）的index是unique index，而現在變成了non-unique index，這有什么不同呢？于是我建了兩張1000萬數據的表，并用兩種不同的index設定為PK的index，語句如下：

create table tab1000w01 
as 
select level id,'killkill Hello world' data
from dual connect by level<=1000*10000;

create table tab1000w02 
as 
select level id,'killkill Hello world' data
from dual connect by level<=1000*10000;

CREATE UNIQUE INDEX tab1000w01_pk ON tab1000w01 (PK_ID)  ;
ALTER TABLE  tab1000w01 ADD CONSTRAINT tab1000w01_PK PRIMARY KEY (PK_ID) USING INDEX tab1000w01_pk ;

CREATE INDEX tab1000w02_pk ON tab500w02 (PK_ID)  ;
ALTER TABLE  tab1000w02 ADD CONSTRAINT tab1000w02_PK PRIMARY KEY (PK_ID) USING INDEX tab1000w02_pk ;

      以下是按照PK查找數據的語句：

select * from tab1000w01 where id=34567;

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    35 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1000W01        |     1 |    35 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_TAB1000W01_PK |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=34567)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets


select * from tab1000w02 where id=34567;

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    35 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1000W02        |     1 |    35 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TAB1000W02_PK |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=34567)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets

      從執行計劃來看，一個是index unique scan，一個是index range scan，從consistent gets來看，一個是4，一個是5，使用unique index節省了1個，不要少看這1個consistent gets，它可是占了總體的20%啊。

      不過這是為什么呢？這篇文章很好地介紹這兩種索引的異同：Differences Between Unique and Non-Unique Indexes，說到底是這兩種索引的結構不同。引用一下這篇文章的分析：

Leaf block dump
===============
header address 143336028=0x88b225c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 500
kdxcofbo 1036=0x40c
kdxcofeo 1042=0x412
kdxcoavs 6
kdxlespl 0
kdxlende 0
kdxlenxt 75520140=0x480588c
kdxleprv 75520138=0x480588a
kdxledsz 0
kdxlebksz 8036
row#0[8022] flag: ------, lock: 0, len=14     <=== length is 14 bytes for the index row entry
col 0; len 4; (4):  c3 60 61 1c
col 1; len 6; (6):  04 80 50 3c 01 06         <=== rowid is stored as a second column for the index row entry
row#1[8008] flag: ------, lock: 0, len=14
col 0; len 4; (4):  c3 60 61 1d
col 1; len 6; (6):  04 80 50 3c 01 07

      non-unique index將 rowid 作為一個字段和數據字段組合成一個“唯一、復合”索引，而unique index的結構如下：

Leaf block dump
===============
header address 143336028=0x88b225c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 533
kdxcofbo 1102=0x44e
kdxcofeo 1112=0x458
kdxcoavs 10
kdxlespl 0
kdxlende 0
kdxlenxt 75527436=0x480750c
kdxleprv 75527434=0x480750a
kdxledsz 6
kdxlebksz 8036
row#0[8023] flag: ------, lock: 0, len=13, data:(6):  04 80 5e 34 02 82    <=== length is 13 byes and rowid not stored as a second column entry
col 0; len 4; (4):  c3 60 30 2c
row#1[8010] flag: ------, lock: 0, len=13, data:(6):  04 80 5e 34 02 83
col 0; len 4; (4):  c3 60 30 2d

      從dump文件中可以看到結構不同導致index中的entry的長度是不一樣的，unique index稍稍短一點，所以每個block可以容納更多的index entry，從宏觀來看unique index更小一點。