Index掃描生成返回記錄的處理流程到底是什麼

sundog315發表於2010-05-19

Branch Block->Leaf Block->Data Block

這個流程是索引掃描的流程。

現在的問題是,對於一個索引掃描的執行計劃,TABLE ACCESS BY INDEX ROWID到底意味著什麼?

1.直接返回Data Block相關rowid的行,索引存在的意義僅為定位記錄

2.讀取Data Block相關rowid行的記錄,並在索引的基礎上補全記錄,索引列的資料不是真正Data Block相對列的資料

[@more@]

C:UsersAdministrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 5月 19 10:16:41 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn test/test
已連線。
SQL> create table t as select * from dba_objects;

表已建立。

SQL> create index t_idx on t(object_id);

索引已建立。

SQL> select object_id,object_name from t where object_id=2;

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
2
C_OBJ#

SQL> select rowid from t where object_id=2;

ROWID
------------------
AAAOURAAEAAAACDAAw

SQL> set autot off
SQL> select dbms_rowid.rowid_to_absolute_fno('AAAOURAAEAAAACDAAw',user,'T') file#,
2 dbms_rowid.rowid_block_number('AAAOURAAEAAAACDAAw') block#
3 from dual;

FILE# BLOCK#
---------- ----------
4 131

SQL> select file_name from dba_data_files where file_id=4;

FILE_NAME
--------------------------------------------------------------------------------
C:ORACLEAPPADMINISTRATORORADATATESTUSERS01.DBF

用BBEDdd啥的直接修改Block內容,把Object_id=2記錄的Object_name改為C_TTT#

SQL> alter system flush buffer_cache;

系統已更改。

SQL> select rowid from t where object_id=2;

ROWID
------------------
AAAOURAAEAAAACDAAw

SQL> select object_id,object_name from t where object_id=2;

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
2
C_TTT#

Object_name的內容已經改過來,一切都想想象中一樣正常。

但是,當我們修改的是索引列的內容時,會怎樣呢?

SQL> drop index t_idx;

索引已刪除。

SQL> create index t_idx on t(object_name);

索引已建立。

修改Block內容,把Object_id=2記錄的Object_name改回C_OBJ#,按照我的想象,應該是透過索引定位到Block中的一條記錄,然後讀取這個Block,挑出記錄,修剪後返回。
這樣,我們應該可以透過查詢C_TTT#而返回C_OBJ#,雖然這樣的結果確實不合理。來看看真正Oracle是如何處理的。

SQL> alter system flush buffer_cache;

系統已更改。

SQL> select object_name,object_id from t where object_name='C_TTT#';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID
----------
C_TTT#
2

居然還是C_TTT#,實際上,在T表中已經沒有C_TTT#的記錄了。

執行計劃
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='C_TTT#')

Note
-----
- dynamic sampling used for this statement (level=4)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
24 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

看看是不是真的改過來了:
SQL> select object_name,object_id from t where object_id=2;

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID
----------
C_OBJ#
2

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 711 | 225 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 9 | 711 | 225 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)

Note
-----
- dynamic sampling used for this statement (level=4)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
806 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

data資料確實已經修改了,當執行計劃為全表掃描的時候,資料是正確的。但當執行計劃為索引掃描時,貌似TABLE ACCESS BY INDEX ROWID僅僅是補充沒有的資料。

強制執行計劃看一下:
SQL> select /*+ full(t) */ object_name,object_id from t where object_name='C_TTT#';

未選定行

SQL> select /*+ full(t) */ object_name,object_id from t where object_name='C_OBJ#';

OBJECT_NAME
--------------------------------------------------------------------------------

OBJECT_ID
----------
C_OBJ#
2


看一下到底處理順序是什麼?
SQL> alter session set events '10202 trace name context forever';

會話已更改。

SQL> select object_name,object_id from t where object_name='C_TTT#';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID
----------
C_TTT#
2

SQL> alter session set events '10202 trace name context off';

會話已更改。

SQL> alter session set events '10200 trace name context forever';

會話已更改。

SQL> select object_name,object_id from t where object_name='C_TTT#';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID
----------
C_TTT#
2

SQL> alter session set events '10200 trace name context off';

會話已更改。

*** 2010-05-19 09:27:29.218
*** SESSION ID:(146.78) 2010-05-19 09:27:29.218

Consistent read complete...
Block header dump: 0x010004b6
Object id on Block? Y
seg/obj: 0xe513 csc: 0x00.4188ff itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000480 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.004188ff
Consistent read complete...
Block header dump: 0x01000083
Object id on Block? Y
seg/obj: 0xe511 csc: 0x00.418734 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00418734
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Consistent read complete...
Block header dump: 0x010004b6
Object id on Block? Y
seg/obj: 0xe513 csc: 0x00.4188ff itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000480 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.004188ff

*** 2010-05-19 09:27:46.746
ktrgtc2(): started for block <0x0004 : 0x01000403> objd: 0x0000e513
env: (scn: 0x0000.00418a76 xid: 0x0003.003.00000df9 uba: 0x00c18e81.0209.05 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00418a4a 97sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.00418953)
ktrexc(): returning 2 on: 0C38384C scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0
ktrgtc2(): completed for block <0x0004 : 0x01000403> objd: 0x0000e513
ktrget2(): started for block <0x0004 : 0x010004b6> objd: 0x0000e513
env: (scn: 0x0000.00418a76 xid: 0x0003.003.00000df9 uba: 0x00c18e81.0209.05 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00418a4a 96sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.00418953)
ktrexf(): returning 9 on: 0C38384C scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0
ktrgcm(): completed for block <0x0004 : 0x010004b6> objd: 0x0000e513
ktrget2(): completed for block <0x0004 : 0x010004b6> objd: 0x0000e513
ktrget2(): started for block <0x0004 : 0x01000083> objd: 0x0000e511
env: (scn: 0x0000.00418a76 xid: 0x0003.003.00000df9 uba: 0x00c18e81.0209.05 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00418a4a 96sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.00418953)
ktrexf(): returning 9 on: 0C38384C scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0
ktrgcm(): completed for block <0x0004 : 0x01000083> objd: 0x0000e511
ktrget2(): completed for block <0x0004 : 0x01000083> objd: 0x0000e511
ktrget2(): started for block <0x0004 : 0x010004b6> objd: 0x0000e513
env: (scn: 0x0000.00418a76 xid: 0x0003.003.00000df9 uba: 0x00c18e81.0209.05 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00418a4a 96sch: scn: 0x0000.00000000 mascn: (scn: 0x0000.00418953)
ktrexf(): returning 9 on: 0C38384C scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0
ktrgcm(): completed for block <0x0004 : 0x010004b6> objd: 0x0000e513
ktrget2(): completed for block <0x0004 : 0x010004b6> objd: 0x0000e513

event 10200比10202多一次一致讀0x01000403塊,這個塊實際是Index Branch。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/19423/viewspace-1033724/,如需轉載,請註明出處,否則將追究法律責任。

相關文章