[原]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更小一点。