Oracle 中的 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更小一点。

 

不像 MySQL 唯一性约束是由唯一性索引实现，Oracle 的索引和约束是分开对待的。

如果某一天，需要将某个唯一性约束（Unique Key）变为不约束，unique index 只能删除重建了，一般变更的语句会变成这样：

create table table_xxx .......;

create index uk_xx on table_xx ( col ) online ;

# 以上实现了一个唯一性约束

# 突然来了一个需求，要将这个唯一性约束去掉，但是按 col 列查的语句海得跑 -_-

create index idx_xx on table_xx ( col , 'c' ) online ;

drop index uk_xx ;

create index idx_col on table_xx ( col ) online ; 

drop index idx_xx ;

没有看错，索引建两次和删索引两次，第一次建复合索引是因为 Oracle 不允许两个索引的索引列信息一致，只能加一个无关重要的东西卡位。

针对这种奇葩需求，使用 non unique index + unique constraint 更灵活：

create table table_xxx .......;

create index uk_xx on table_xx ( col ) online ;

alter table table_xx add constraint uk_xx unique( col ) using uk_xx ;

# 以上实现了一个唯一性约束


# 突然来了一个需求，要将这个唯一性约束去掉，但是按 col 列查的语句海得跑 -_-

alter table table_xx drop constraint uk_xx ;

从维护的角度来说，特别是大表的情况下，推荐第二种方式。

 

转载请注明出处： https://blog.youkuaiyun.com/killlkilll/article/details/97966651