再說Unique Index和Normal Index行為差異

bitifi發表於2015-11-07

在筆者早期的文章中,從結構視角討論過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/30496894/viewspace-1825212/,如需轉載,請註明出處,否則將追究法律責任。

相關文章