在笔者早期的文章中，从结构视角讨论过Unique Index和Normal Index的差异。Oracle的Unique Index是一种特殊的约束索引结构，通常而言，Unique Index可以有几个方面的优势：
首先是更加精简的结构，同内容情况下，Unique Index在体积上略小于Normal Index。其次，Unique Index提供出额外的列取值约束保证。第三就是Oracle在Unique Index中，有一些独特的SQL检索行为。
如果表采用唯一索引，在SQL执行过程中，是有很多的性能优势和好处的。本篇我们借助常用的性能测量工具进行比较研究。
1、环境介绍
我们选择Oracle 11gR2进行实验，创建数据表T，对应的两个数据列结构和内容完全相同。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t as select object_id obj_id_1, object_id obj_id_2 from dba_objects;
Table created
SQL> select count(*) from t;
COUNT(*)
----------
75596
在完全相同的列obj_id_1和obj_id_2上创建普通和唯一索引。
SQL> create index idx_t_normal on t(obj_id_1);
Index created
SQL> create unique index idx_t_unique on t(obj_id_2);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
2、执行计划分析
先从执行计划层面看两种类型索引的差异，选择索引最高效的=SQL语句结构。
SQL> explain plan for select * from t where obj_id_1=1000;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 400739531
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)
|* 2 | INDEX RANGE SCAN | IDX_T_NORMAL | 1 | | 1 (0)
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJ_ID_1"=1000)
14 rows selected
SQL> explain plan for select * from t where obj_id_2=1000;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 399591198
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)
|* 2 | INDEX UNIQUE SCAN | IDX_T_UNIQUE | 1 | | 1 (0)
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJ_ID_2"=1000)
14 rows selected
从上面的执行计划中，我们可以看到明确的差异。在普通索引idx_t_normal在等号条件下的时候，执行路径操作位Index Range Scan。Range Scan的行为是从索引根节点开始，通过分支节点逐层比较定位，定位到第一个符合条件的叶子节点上。由于索引叶子节点都是有序排列，符合条件的其他值一定在第一个符合条件叶子节点的水平位置上进行Range Scan操作。
而唯一索引Unique Index在操作上使用的是不同的操作。唯一索引在叶子节点上有一个满足条件约束，就是使用=号的时候，至多只有一个符合条件的取值。Oracle只需要通过根节点导航定位到第一个条件叶子节点就可以了，不需要Range Scan动作。
由于我们使用的基础数据完全相同，所以在成本计算值和行数上，两个方案没有任何差别。注意：执行计划中反映的情况是通过统计量的计算值，真实情况如何呢？
3、SQL运行统计量分析
我们使用autotrace工具，进行SQL实际执行分析。
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
SQL> select * from t where obj_id_1=1000;
统计信息
----------------------------------------------------------
31 recursive calls
0 db block gets
48 consistent gets
11 physical reads
0 redo size
419 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t where obj_id_2=1000;
统计信息
----------------------------------------------------------
19 recursive calls
0 db block gets
47 consistent gets
11 physical reads
0 redo size
353 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
注意标红的内容：我们通过一系列实验，发现在进行定位操作的时候，普通索引Range Scan动作要比Unique Scan多进行一次逻辑读。
那么，我们怎么理解这个问题，一种猜想是：对于Normal Index，每次进行的叶子节点检索过程中，是一个“判断”的过程。由于叶子节点是有序的，Oracle在读到某一个节点时候，只有判断下一个节点是否是不符合情况的记录，才能决定终止。而Unique Index的结构造成，当进行等号条件检索的时候，Oracle一次最多能找到一条符合条件的记录。也就是说，如果当前叶子节点已经符合条件了，就不需要进行下一个节点的试探验证动作了。就是这个试探动作，让逻辑读的数目差距1。
真实情况是如何呢？我们能找到的比较细节工具就是10046事件跟踪。
4、10046事件跟踪
我们分别使用两个会话，进行10046跟踪Oracle在两个SQL中的行为。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5821.trc
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select * from t where obj_id_1=1000;
OBJ_ID_1 OBJ_ID_2
---------- ----------
1000 1000
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> conn / as sysdba
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
---------------------------------------------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5839.trc
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select * from t where obj_id_2=1000;
OBJ_ID_1 OBJ_ID_2
---------- ----------
1000 1000
SQL> alter session set events '10046 trace name context off';
Session altered.
分析对obj_id_1条件，也就是普通索引的Raw Trace片段。
=====================
PARSING IN CURSOR #4831628 len=35 dep=0 uid=0 ct=3 lid=0 tim=1382679804979468 hv=1557130689 ad='2fe520b8' sqlid='16r9h71fczvf1'
select * from t where obj_id_1=1000
END OF STMT
PARSE #4831628:c=34995,e=95739,p=6,cr=45,cu=0,mis=1,r=0,dep=0,og=1,plh=400739531,tim=1382679804979464
EXEC #4831628:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=400739531,tim=1382679804979584
WAIT #4831628: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1382679804979635
WAIT #4831628: nam='db file sequential read' ela= 71 file#=1 block#=96817 blocks=1 obj#=78115 tim=1382679804979791
WAIT #4831628: nam='db file sequential read' ela= 50 file#=1 block#=96820 blocks=1 obj#=78115 tim=1382679804979919
WAIT #4831628: nam='db file sequential read' ela= 59 file#=1 block#=90226 blocks=1 obj#=78114 tim=1382679804980048
FETCH #4831628:c=0,e=413,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=400739531,tim=1382679804980082
WAIT #4831628: nam='SQL*Net message from client' ela= 437 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679804980566
FETCH #4831628:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=400739531,tim=1382679804980633
STAT #4831628 id=1 cnt=1 pid=0 pos=1 bj=78114 p='TABLE ACCESS BY INDEX ROWID T (cr=4 pr=3 pw=0 time=406 us cost=2 size=10 card=1)'
STAT #4831628 id=2 cnt=1 pid=1 pos=1 bj=78115 p='INDEX RANGE SCAN IDX_T_NORMAL (cr=3 pr=2 pw=0 time=288 us cost=1 size=0 card=1)'
WAIT #4831628: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679804980702
*** 2013-10-25 13:43:40.038
WAIT #4831628: nam='SQL*Net message from client' ela= 15057747 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679820038463
CLOSE #4831628:c=0,e=34,dep=0,type=0,tim=1382679820039051
=====================
在normal index动作中，Oracle进行了三次单块读动作（db file sequential read），读取了索引和数据块。分析步骤index range scan操作中，进行了三次一致读（consistent read）动作cr=3。注意一点，这个3次读是SQL语句本身的读动作。我们在上一部分中看到了48是这个3次外加其他recursive SQL进行的读次数。
那么，唯一索引情况呢？
=====================
PARSING IN CURSOR #8985920 len=35 dep=0 uid=0 ct=3 lid=0 tim=1382679944147064 hv=2859221912 ad='3168c9f0' sqlid='3b0tusfp6shws'
select * from t where obj_id_2=1000
END OF STMT
PARSE #8985920:c=24997,e=39398,p=6,cr=45,cu=0,mis=1,r=0,dep=0,og=1,plh=399591198,tim=1382679944147058
EXEC #8985920:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=399591198,tim=1382679944147191
WAIT #8985920: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1382679944147241
WAIT #8985920: nam='db file sequential read' ela= 101 file#=1 block#=91953 blocks=1 obj#=78116 tim=1382679944147422
WAIT #8985920: nam='db file sequential read' ela= 55 file#=1 block#=91955 blocks=1 obj#=78116 tim=1382679944147698
WAIT #8985920: nam='db file sequential read' ela= 78 file#=1 block#=90226 blocks=1 obj#=78114 tim=1382679944147830
FETCH #8985920:c=1000,e=605,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=399591198,tim=1382679944147881
STAT #8985920 id=1 cnt=1 pid=0 pos=1 bj=78114 p='TABLE ACCESS BY INDEX ROWID T (cr=3 pr=3 pw=0 time=598 us cost=2 size=10 card=1)'
STAT #8985920 id=2 cnt=1 pid=1 pos=1 bj=78116 p='INDEX UNIQUE SCAN IDX_T_UNIQUE (cr=2 pr=2 pw=0 time=440 us cost=1 size=0 card=1)'
WAIT #8985920: nam='SQL*Net message from client' ela= 793 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679944148753
FETCH #8985920:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=399591198,tim=1382679944148789
WAIT #8985920: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679944148826
*** 2013-10-25 13:45:58.623
WAIT #8985920: nam='SQL*Net message from client' ela= 14474484 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679958623347
CLOSE #8985920:c=0,e=125,dep=0,type=0,tim=1382679958624126
=====================
Index Unique Scan同样进行了三次的单块读动作，但是在Index Unique Scan操作中，进行一致读cr的次数为2，比刚刚的normal index少一次。
相信这也就是我们看到统计量中一次逻辑的差异的根源。
5、结论
Oracle Index是我们非常常见的优化策略，其内容也是复杂多变。唯一索引作为我们经常用到的索引类型，其特性值得我们好好研究。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-775124/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-775124/
1327
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
