解析INDEX_DESC
oracle执行计划中有类INDEX_DESC提示，我们知道，一般的索引是根据索引列的字段值升序排列，扫描会根据where条件的值判断entry，然后对索引树进行leaf的扫描，在读取leaf的顺序上，INDEX_DESC是个反向的过程，它从树的最后一个leaf往前读取，对于要求从大批量数据中返回少量top large value这类SQL需求来讲，无疑是比较高效的方式
来看一下/*+ index */和 /*+ index_desc */在读取上的不同。
SQL>create table test tablespace test as select * from dba_objects where object_id is not null order by dbms_random.value;
Table created
SQL> create index idx_test_1 on test(object_id) tablespace test;
Index created
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘test’,estimate_percent => 100,cascade => true);
PL/SQL procedure successfully completed
SQL> select /*+ index_desc(test,idx_test_1) */ OBJECT_NAME,OBJECT_ID,dbms_rowid.rowid_relative_fno(rowid) FILE_ID,dbms_rowid.rowid_block_number(rowid) BLOCK_ID from test where object_id>0 and rownum<5;
OBJECT_NAME OBJECT_ID FILE_ID BLOCK_ID
—————————– ———- —————————————
TEST 3030 8 121
TEST_CD 13028 8 65
WRH$_SERVICE_WAIT_CLASS_PK 13027 8 149
WRH$_SERVICE_WAIT_CLASS_PK 13026 8 51SQL> select /*+ index(test,idx_test_1) */ OBJECT_NAME,OBJECT_ID,dbms_rowid.rowid_relative_fno(rowid) FILE_ID,dbms_rowid.rowid_block_number(rowid) BLOCK_ID from test where object_id>0 and rownum<5;
OBJECT_NAME OBJECT_ID FILE_ID BLOCK_ID
————————— ———- —————————— ———————-
C_OBJ# 2 8 112
I_OBJ# 3 8 49
TAB$ 4 8 31
CLU$ 5 8 147
可以看到2者获取的row是不同的.
对先前使用index_desc的语句进行10046跟踪，跟踪之前需要flash buffer_cache.这样可以避免干扰的从trace文件看到从磁盘读取到的block。
—
PARSING IN CURSOR #1 len=178 dep=0 uid=28 oct=3 lid=28 tim=1202439326146305 hv=4287804886 ad=’297b5944′
select /*+ index_desc(test,idx_test_1) */ OBJECT_NAME,OBJECT_ID,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from test where object_id>0 and rownum<5
END OF STMT
PARSE #1:c=14998,e=14540,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1202439326146297
EXEC #1:c=0,e=207,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1202439326146604
WAIT #1: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=6314 tim=1202439326146617
WAIT #1: nam=’db file sequential read’ ela= 26 file#=8 block#=3610 blocks=1 obj#=13032 tim=1202439326146842
WAIT #1: nam=’db file sequential read’ ela= 117 file#=8 block#=3633 blocks=1 obj#=13032 tim=1202439326147139
WAIT #1: nam=’db file sequential read’ ela= 12 file#=8 block#=121 blocks=1 obj#=13030 tim=1202439326147269
FETCH #1:c=2000,e=1110,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1202439326147729
WAIT #1: nam=’SQL*Net message from client’ ela= 208 driver id=1650815232 #bytes=1 p3=0 obj#=13030 tim=1202439326147991
WAIT #1: nam=’db file sequential read’ ela= 13 file#=8 block#=65 blocks=1 obj#=13030 tim=1202439326148117
WAIT #1: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=13030 tim=1202439326148171
WAIT #1: nam=’db file sequential read’ ela= 21 file#=8 block#=149 blocks=1 obj#=13030 tim=1202439326148263
WAIT #1: nam=’db file sequential read’ ela= 16 file#=8 block#=51 blocks=1 obj#=13030 tim=1202439326148353
FETCH #1:c=0,e=369,p=3,cr=4,cu=0,mis=0,r=3,dep=0,og=1,tim=1202439326148400
*** 2009-01-07 11:11:26.616
WAIT #1: nam=’SQL*Net message from client’ ela= 16250106 driver id=1650815232 #bytes=1 p3=0 obj#=13030 tim=1202439342398571
STAT #1 id=1 cnt=4 pid=0 pos=1 obj=0 op=’COUNT STOPKEY (cr=7 pr=6 pw=0 time=751 us)’
STAT #1 id=2 cnt=4 pid=1 pos=1 obj=13030 op=’TABLE ACCESS BY INDEX ROWID TEST (cr=7 pr=6 pw=0 time=727 us)’
STAT #1 id=3 cnt=4 pid=2 pos=1 obj=13032 op=’INDEX RANGE SCAN DESCENDING IDX_TEST_1 (cr=3 pr=2 pw=0 time=583 us)’
=====================
PARSING IN CURSOR #2 len=55 dep=0 uid=28 oct=42 lid=28 tim=1202439342398576 hv=2217940283 ad=’0′
alter session set events ‘10046 trace name context off’
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1202439342398576
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1202439342398576
—-
读取顺序为file#=8 block#=3610–file#=8 block#=3633–file#=8 block#=121–file#=8 block#=65–file#=8 block#=149
比较明显可以看出121-65-149 跟我们先前查询出来的结果顺序是一致的。为了更加清楚的显示读取过程，我们对idx_test_1进行treedump
SQL> select object_id from dba_objects where object_name=’IDX_TEST_1′;
OBJECT_ID
———-
13032
SQL> ALTER SESSION SET EVENTS ‘immediate trace name TREEDUMP level 13032′;
Session altered.
—– begin tree dump
branch: 0×2000e1a 33558042 (0: nrow: 23, level: 1)
leaf: 0×2000e1b 33558043 (-1: nrow: 485 rrow: 485)
leaf: 0×2000e1c 33558044 (0: nrow: 479 rrow: 479)
leaf: 0×2000e1d 33558045 (1: nrow: 479 rrow: 479)
leaf: 0×2000e1e 33558046 (2: nrow: 478 rrow: 478)
leaf: 0×2000e1f 33558047 (3: nrow: 479 rrow: 479)
leaf: 0×2000e20 33558048 (4: nrow: 479 rrow: 479)
leaf: 0×2000e21 33558049 (5: nrow: 479 rrow: 479)
leaf: 0×2000e22 33558050 (6: nrow: 479 rrow: 479)
leaf: 0×2000e23 33558051 (7: nrow: 479 rrow: 479)
leaf: 0×2000e24 33558052 (8: nrow: 478 rrow: 478)
leaf: 0×2000e25 33558053 (9: nrow: 479 rrow: 479)
leaf: 0×2000e26 33558054 (10: nrow: 479 rrow: 479)
leaf: 0×2000e27 33558055 (11: nrow: 478 rrow: 478)
leaf: 0×2000e28 33558056 (12: nrow: 479 rrow: 479)
leaf: 0×2000e29 33558057 (13: nrow: 479 rrow: 479)
leaf: 0×2000e2a 33558058 (14: nrow: 479 rrow: 479)
leaf: 0×2000e2b 33558059 (15: nrow: 479 rrow: 479)
leaf: 0×2000e2c 33558060 (16: nrow: 478 rrow: 478)
leaf: 0×2000e2d 33558061 (17: nrow: 479 rrow: 479)
leaf: 0×2000e2e 33558062 (18: nrow: 460 rrow: 460)
leaf: 0×2000e2f 33558063 (19: nrow: 448 rrow: 448)
leaf: 0×2000e30 33558064 (20: nrow: 449 rrow: 449)
leaf: 0×2000e31 33558065 (21: nrow: 6 rrow: 6)
—-
第1次io file#=8 block#=3610 读取的block为索引的root节点所在的block — 对应treedump里面 branch: 0×2000e1a 33558042 (0: nrow: 23, level: 1)
第2次io file#=8 block#=3633 读取的block为索引树的最后一个leaf — 对应treedump里面 leaf: 0×2000e31 33558065 (21: nrow: 6 rrow: 6)
以下io便是对leaf: 0×2000e31 33558065所存的条目进行回表访问。
DUMP leaf: 0×2000e31 33558065,也就是file#=8 block#=3633。
转换关系如下：
SQL> select dbms_utility.data_block_address_file(33558065) file_id#,dbms_utility.data_block_address_block(33558065) block_id# from dual;
FILE_ID# BLOCK_ID#
———- ———-
8 3633
SQL> alter system dump datafile 8 block 3633;
Leaf block dump
===============
header address 216754268=0xceb685c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 6
kdxcofbo 48=0×30
kdxcofeo 7948=0×1f0c
kdxcoavs 7900
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
kdxleprv 33558064=0×2000e30
kdxledsz 0
kdxlebksz 8032
row#0[8018] flag: ——, lock: 0, len=14
col 0; len 4; (4): c3 02 1f 18
col 1; len 6; (6): 02 00 01 09 00 01 —-对应datafile 8，block 265
row#1[8004] flag: ——, lock: 0, len=14
col 0; len 4; (4): c3 02 1f 1a
col 1; len 6; (6): 02 00 00 59 00 41 —-对应datafile 8，block 89
row#2[7990] flag: ——, lock: 0, len=14
col 0; len 4; (4): c3 02 1f 1b
col 1; len 6; (6): 02 00 00 33 00 4d —-对应datafile 8，block 51
row#3[7976] flag: ——, lock: 0, len=14
col 0; len 4; (4): c3 02 1f 1c
col 1; len 6; (6): 02 00 00 95 00 46 —-对应datafile 8，block 149
row#4[7962] flag: ——, lock: 0, len=14
col 0; len 4; (4): c3 02 1f 1d
col 1; len 6; (6): 02 00 00 41 00 23 —-对应datafile 8，block 65
row#5[7948] flag: ——, lock: 0, len=14
col 0; len 4; (4): c3 02 1f 1f
col 1; len 6; (6): 02 00 00 79 00 04 –对应datafile 8，block 121
—– end of leaf block dump —–
转换关系–
col 1; len 6; (6)转换成64位，
前10位=>file id
中22位=>block id
后16位=>row id
看一下执行计划成本情况
[oracle@test1 getsql]$ ./get_sql.sh 2 2570394218
SQL_TEXT
——————————————————————————–
select * from (select /*+ index(test,idx_test_1) */ * from test
where object_id>0 order by object_id desc) where rownum<10ID P_ID execution_plan COST CARDINALITY BYTES
— —- ————————————————– ——– ———– ——–
0 SELECT STATEMENT 10606
1 0 COUNT STOPKEY
2 1 VIEW 10606 10466 1852482
3 2 SORT ORDER BY STOPKEY 10606 10466 889610
4 3 TABLE ACCESS BY INDEX ROWID TEST 10396 10466 889610
5 4 INDEX RANGE SCAN IDX_TEST_1 24 10466[oracle@test1 getsql]$ ./get_sql.sh 2 3221081669
SQL_TEXT
——————————————————————————–
select * from (select /*+ index_desc(test,idx_test_1) */ * from
test where object_id>0 order by object_id desc) where rownum<10ID P_ID execution_plan COST CARDINALITY BYTES
— —- ————————————————– ——– ———– ——–
0 SELECT STATEMENT 11
1 0 COUNT STOPKEY
2 1 VIEW 11 9 1593
3 2 TABLE ACCESS BY INDEX ROWID TEST 11 9 765
4 3 INDEX RANGE SCAN DESCENDING IDX_TEST_1 2 10466
在10gR2版本下，oracle会根据成本计算自动选择index_desc方式来作为最优的执行计划，9i下未做测试验证。
