ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 再说Unique Index和Normal Index行为差异

再说Unique Index和Normal Index行为差异

原创 Linux操作系统 作者:realkid4 时间:2013-10-28 08:41:08 0 删除 编辑

在笔者早期的文章中,从结构视角讨论过Unique IndexNormal Index的差异。OracleUnique Index是一种特殊的约束索引结构,通常而言,Unique Index可以有几个方面的优势:

 

首先是更加精简的结构,同内容情况下,Unique Index在体积上略小于Normal Index。其次,Unique Index提供出额外的列取值约束保证。第三就是OracleUnique 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_1obj_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 ScanRange Scan的行为是从索引根节点开始,通过分支节点逐层比较定位,定位到第一个符合条件的叶子节点上。由于索引叶子节点都是有序排列,符合条件的其他值一定在第一个符合条件叶子节点的水平位置上进行Range Scan操作。

 

而唯一索引Unique Index在操作上使用的是不同的操作。唯一索引在叶子节点上有一个满足条件约束,就是使用=号的时候,至多只有一个符合条件的取值。Oracle只需要通过根节点导航定位到第一个条件叶子节点就可以了,不需要Range Scan动作。

 

由于我们使用的基础数据完全相同,所以在成本计算值和行数上,两个方案没有任何差别。注意:执行计划中反映的情况是通过统计量的计算值,真实情况如何呢?

 

3SQL运行统计量分析

 

我们使用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事件跟踪。

 

410046事件跟踪

 

我们分别使用两个会话,进行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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: TRCA-Trace Analyzer
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7632172