ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > Index扫描生成返回记录的处理流程到底是什么

Index扫描生成返回记录的处理流程到底是什么

原创 数据库开发技术 作者：sundog315 时间：2010-05-19 14:25:17 0 删除 编辑

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博客 ” ，链接：https://blog.itpub.net/19423/viewspace-1033724/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 从底向上第五篇--了解表的压缩属性
下一篇： segment createion deferred
请登录后发表评论 登录
全部评论
sundog315

注册时间：2010-01-05

  • 博文量
    126
  • 访问量
    540262

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593