1、创建测试数据
SQL> create table test (id int);
Table created.
SQL> insert into test select level lv from dual connect by level < 10000;
9999 rows created.
SQL> create index test_ind on test(id);
Index created.
SQL> commit;
Commit complete.
SQL>
2、查看索引对象号并treedump
SQL> select object_id from dba_objects where object_name = 'TEST_IND';
OBJECT_ID
----------
88657
SQL> alter system set events 'immediate trace name treedump level 88657';
System altered.
SQL>
SQL>
---查看trace文件
*** 2020-10-14 11:08:51.749
*** SESSION ID:(71.16391) 2020-10-14 11:08:51.749
*** CLIENT ID:() 2020-10-14 11:08:51.749
*** SERVICE NAME:(SYS$USERS) 2020-10-14 11:08:51.749
*** MODULE NAME:(sqlplus@mncndn1 (TNS V1-V3)) 2020-10-14 11:08:51.749
*** ACTION NAME:() 2020-10-14 11:08:51.749
----- begin tree dump
branch: 0x417611 4290065 (0: nrow: 21, level: 1)
leaf: 0x417612 4290066 (-1: nrow: 485 rrow: 485)
leaf: 0x417613 4290067 (0: nrow: 479 rrow: 479)
leaf: 0x417614 4290068 (1: nrow: 479 rrow: 479)
leaf: 0x417615 4290069 (2: nrow: 479 rrow: 479)
leaf: 0x417616 4290070 (3: nrow: 479 rrow: 479)
leaf: 0x417617 4290071 (4: nrow: 478 rrow: 478)
leaf: 0x417618 4290072 (5: nrow: 479 rrow: 479)
leaf: 0x417619 4290073 (6: nrow: 479 rrow: 479)
leaf: 0x41761a 4290074 (7: nrow: 479 rrow: 479)
leaf: 0x41761b 4290075 (8: nrow: 478 rrow: 478)
leaf: 0x41761c 4290076 (9: nrow: 479 rrow: 479)
leaf: 0x41761d 4290077 (10: nrow: 479 rrow: 479)
leaf: 0x41761e 4290078 (11: nrow: 479 rrow: 479)
leaf: 0x41761f 4290079 (12: nrow: 479 rrow: 479)
leaf: 0x417620 4290080 (13: nrow: 478 rrow: 478)
leaf: 0x417621 4290081 (14: nrow: 479 rrow: 479)
leaf: 0x417622 4290082 (15: nrow: 479 rrow: 479)
leaf: 0x417623 4290083 (16: nrow: 479 rrow: 479)
leaf: 0x417624 4290084 (17: nrow: 479 rrow: 479)
leaf: 0x417625 4290085 (18: nrow: 478 rrow: 478)
leaf: 0x417626 4290086 (19: nrow: 417 rrow: 417)
----- end tree dump
//branch: 0x417611 4290065 (0: nrow: 21, level: 1)
代表一个branch分支,该分支包含了21个leaf
把4290065地址转换为文件号和块号(branch)
SQL> select dbms_utility.data_block_address_file(4290065), dbms_utility.data_block_address_block(4290065) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4290065)
---------------------------------------------
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4290065)
----------------------------------------------
1
95761
SQL>
//把1号文件95761 块中的branch数据dump出来,查看块的内容如下
SQL> alter system dump datafile 1 block 95761;
System altered.
SQL>
//dump内容如下
Block header dump: 0x00417611
Object id on Block? Y
seg/obj: 0x15a51 csc: 0x00.10b36d itc: 1 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0010b36d
Branch block dump
=================
header address 140651344386628=0x7febf16b5a44
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 20 //包含20行数据
kdxcofbo 68=0x44
kdxcofeo 7876=0x1ec4
kdxcoavs 7808
kdxbrlmc 4290066=0x417612
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 4290067=0x417613
col 0; len 3; (3): c2 05 57
col 1; TERM
row#1[8038] dba: 4290068=0x417614
col 0; len 3; (3): c2 0a 42
col 1; TERM
row#2[8029] dba: 4290069=0x417615
col 0; len 3; (3): c2 0f 2d
col 1; TERM
row#3[8020] dba: 4290070=0x417616
col 0; len 3; (3): c2 14 18
col 1; TERM
row#4[8011] dba: 4290071=0x417617
col 0; len 3; (3): c2 19 03
col 1; TERM
row#5[8002] dba: 4290072=0x417618
col 0; len 3; (3): c2 1d 51
col 1; TERM
row#6[7993] dba: 4290073=0x417619
col 0; len 3; (3): c2 22 3c
col 1; TERM
row#7[7984] dba: 4290074=0x41761a
col 0; len 3; (3): c2 27 27
col 1; TERM
row#8[7975] dba: 4290075=0x41761b
col 0; len 3; (3): c2 2c 12
col 1; TERM
row#9[7966] dba: 4290076=0x41761c
col 0; len 3; (3): c2 30 60
col 1; TERM
row#10[7957] dba: 4290077=0x41761d
col 0; len 3; (3): c2 35 4b
col 1; TERM
row#11[7948] dba: 4290078=0x41761e
col 0; len 3; (3): c2 3a 36
col 1; TERM
row#12[7939] dba: 4290079=0x41761f
col 0; len 3; (3): c2 3f 21
col 1; TERM
row#13[7930] dba: 4290080=0x417620
col 0; len 3; (3): c2 44 0c
col 1; TERM
row#14[7921] dba: 4290081=0x417621
col 0; len 3; (3): c2 48 5a
col 1; TERM
row#15[7912] dba: 4290082=0x417622
col 0; len 3; (3): c2 4d 45
col 1; TERM
row#16[7903] dba: 4290083=0x417623
col 0; len 3; (3): c2 52 30
col 1; TERM
row#17[7894] dba: 4290084=0x417624
col 0; len 3; (3): c2 57 1b
col 1; TERM
row#18[7885] dba: 4290085=0x417625
col 0; len 3; (3): c2 5c 06
col 1; TERM
row#19[7876] dba: 4290086=0x417626
col 0; len 3; (3): c2 60 54
col 1; TERM
----- end of branch block dump -----
//通过dump 1号文件95761块(branch 分支块)可以发现该分支块包含20个leaf,而直接treedump索引块看到的是21个leaf
//第一行的地址信息如下:
row#0[8047] dba: 4290067=0x417613
col 0; len 3; (3): c2 05 57
//dba: 4290067 和treedump的第二个 leaf: 0x417613 4290067 (0: nrow: 479 rrow: 479)中的地址是以一样的,都是4290067。
//col 0 的c2 05 57 转换为数字为486,也就是说行所以1-485的数据在第一个leaf里面,只是dump块看到的内容没有直接显示leaf 1,所以dump里面看到的20个leaf,其实隐含了第一个leaf的数据,可以理解为也是21个leaf
SQL> select utl_raw.cast_to_number('c20557') from dual;
UTL_RAW.CAST_TO_NUMBER('C20557')
--------------------------------
486
//第二行数据转换 col 0的值
row#1[8038] dba: 4290068=0x417614
col 0; len 3; (3): c2 0a 42
SQL> select utl_raw.cast_to_number('c20a42') from dual;
UTL_RAW.CAST_TO_NUMBER('C20A42')
--------------------------------
965
SQL>
//965-486=479。也就是说第二个leaf的包含了479行,和treedump里面看到的第二个leaf: 0x417613 4290067 (0: nrow: 479 rrow: 479) 行数一样。
3、查看索引块内容
//查看索引的第一个块的内容
//索引第一个块的地址信息为 leaf: 0x417612 4290066 (-1: nrow: 485 rrow: 485) 包含485行数据
SQL> select dbms_utility.data_block_address_file(4290066), dbms_utility.data_block_address_block(4290066) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4290066) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4290066)
--------------------------------------------- ----------------------------------------------
1 95762
SQL>
SQL> alter system dump datafile 1 block 95762;
System altered.
SQL>
//块的部分内容如下
seg/obj: 0x15a51 csc: 0x00.10b36d itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
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.0010b36d
Leaf block dump
===============
header address 140503873202780=0x7fc99b73aa5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485 //包含485行数据
kdxcofbo 1006=0x3ee
kdxcofeo 1830=0x726
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 4290067=0x417613
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 72 f1 00 00
row#1[8008] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 41 72 f1 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 41 72 f1 00 02
row#3[7984] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 00 41 72 f1 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 00 41 72 f1 00 04
row#5[7960] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 00 41 72 f1 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 00 41 72 f1 00 06
row#7[7936] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 00 41 72 f1 00 07
.
.
.
/转换第一行数据说明
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 72 f1 00 00
col 0 就是索引的key值c1 02
SQL> select utl_raw.cast_to_number('c102') from dual;
UTL_RAW.CAST_TO_NUMBER('C102')
------------------------------
1
SQL>
//col 1的前4位为rowid,后面的表示在块中的行号
//转换 004172f1
SQL> set lin 200
SQL> select to_number('004172f1','xxxxxxxxxx') from dual;
TO_NUMBER('004172F1','XXXXXXXXXX')
----------------------------------
4289265
SQL> select dbms_utility.data_block_address_file(4289265),dbms_utility.data_block_address_block(4289265) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4289265) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4289265)
--------------------------------------------- ----------------------------------------------
1 94961
SQL>
也就是说索引key=1对应的数据块为1号文件的94961块的第一行数据
//查询test表的第一行数据
SQL>
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test where id = 1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 94961
SQL>
4、查看走索引的执行计划
SQL> set autot on
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test where id = 1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 94961
Execution Plan
----------------------------------------------------------
Plan hash value: 3357096749
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IND | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
657 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
因为test表中只有id列,并且id列上面有索引,所以不需要回表。
5、10046跟踪
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select * from test where id = 1;
ID
----------
1
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL>
//trace内容如下
PARSING IN CURSOR #140279459638368 len=31 dep=0 uid=0 oct=3 lid=0 tim=1602692873430196 hv=3352796757 ad='a84c2450' sqlid='ayqb8yg3xg7kp'
select * from test where id = 1
END OF STMT
PARSE #140279459638368:c=2000,e=568,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3357096749,tim=1602692873430181
EXEC #140279459638368:c=0,e=162,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3357096749,tim=1602692873430519
WAIT #140279459638368: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1602692873430592
WAIT #140279459638368: nam='Disk file operations I/O' ela= 147 FileOperation=2 fileno=1 filetype=2 obj#=88657 tim=1602692873431225
WAIT #140279459638368: nam='db file sequential read' ela= 55 file#=1 block#=95761 blocks=1 obj#=88657 tim=1602692873431360
WAIT #140279459638368: nam='db file sequential read' ela= 191 file#=1 block#=95762 blocks=1 obj#=88657 tim=1602692873431787
FETCH #140279459638368:c=1000,e=1263,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=3357096749,tim=1602692873431907
WAIT #140279459638368: nam='SQL*Net message from client' ela= 2119 driver id=1650815232 #bytes=1 p3=0 obj#=88657 tim=1602692873434166
FETCH #140279459638368:c=0,e=51,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=3357096749,tim=1602692873434334
STAT #140279459638368 id=1 cnt=1 pid=0 pos=1 obj=88657 op='INDEX RANGE SCAN TEST_IND (cr=3 pr=2 pw=0 time=1288 us cost=1 size=13 card=1)'
WAIT #140279459638368: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=88657 tim=1602692873434579
*** 2020-10-14 12:28:44.352
WAIT #140279459638368: nam='SQL*Net message from client' ela= 50917832 driver id=1650815232 #bytes=1 p3=0 obj#=88657 tim=1602692924352521
CLOSE #140279459638368:c=0,e=14,dep=0,type=0,tim=1602692924352783
=====================
//其中db file sequential read对应两行记录
WAIT #140279459638368: nam='db file sequential read' ela= 55 file#=1 block#=95761 blocks=1 obj#=88657 tim=1602692873431360
WAIT #140279459638368: nam='db file sequential read' ela= 191 file#=1 block#=95762 blocks=1 obj#=88657 tim=1602692873431787
//一个是1号文件95761, 一个是1号文件95762,对象都是obj#=88657对应索引
//1号文件95762 对应第一个索引块
//索引第一个块的地址信息为 leaf: 0x417612 4290066 (-1: nrow: 485 rrow: 485) 包含485行数据
SQL> select dbms_utility.data_block_address_file(4290066), dbms_utility.data_block_address_block(4290066) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4290066) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4290066)
--------------------------------------------- ----------------------------------------------
1 95762
SQL>
//1号文件95761对应什么?其实就是branch,可以通过上面的treedump看到branch: 0x417611 4290065 (0: nrow: 21, level: 1)
SQL> set lin 200
SQL> select dbms_utility.data_block_address_file(4290065), dbms_utility.data_block_address_block(4290065) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4290065) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4290065)
--------------------------------------------- ----------------------------------------------
1 95761
SQL>
//因为现在查询语句select * from test where id = 1; 可以直接走索引,并且不需要根据rowid回表
//现在给test增加一列name
6、test表新加name列,让走索引回表
SQL> alter table test add name varchar2(20);
Table altered.
SQL>
SQL> update test set name = dbms_random.string('l',20);
9999 rows updated.
SQL> commit;
Commit complete.
SQL>
//查看执行计划
SQL> set autot on
SQL> select * from test where id = 1;
ID NAME
---------- --------------------
1 kgpjvehtqvnwxngyiiyp
Execution Plan
----------------------------------------------------------
Plan hash value: 2216933833
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 | //出现根据rowid回表的情况
|* 2 | INDEX RANGE SCAN | TEST_IND | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
76 consistent gets
1 physical reads
0 redo size
611 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
//可以看到执行计划中id=1的operation出现了根据rowid回表的情况。
//做10046trace查看
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select * from test where id = 1;
ID NAME
---------- --------------------
1 kgpjvehtqvnwxngyiiyp
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL>
---trace文件
=====================
=====================
PARSING IN CURSOR #140473929797440 len=392 dep=1 uid=0 oct=3 lid=0 tim=1602712699578187 hv=747498002 ad='a81d95c8' sqlid='g4xj2t4q8vuhk'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0) FROM (SELECT /*+ NO_PARALLEL("TEST") INDEX("TEST" TEST_IND) NO_PARALLEL_INDEX("TEST") */ 1 AS C1, 1 AS C2, 1 AS C3 FROM "SYS"."TEST" "TEST" WHERE "TEST"."ID"=1 AND ROWNUM <= 2500) SAMPLESUB
END OF STMT
PARSE #140473929797440:c=2000,e=2712,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=2818712035,tim=1602712699578185
EXEC #140473929797440:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2818712035,tim=1602712699578453
WAIT #140473929797440: nam='db file sequential read' ela= 1146 file#=1 block#=95761 blocks=1 obj#=88657 tim=1602712699579732
WAIT #140473929797440: nam='db file sequential read' ela= 1276 file#=1 block#=95762 blocks=1 obj#=88657 tim=1602712699581140
FETCH #140473929797440:c=2999,e=2769,p=2,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=2818712035,tim=1602712699581259
STAT #140473929797440 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2 pr=2 pw=0 time=2780 us)'
STAT #140473929797440 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW (cr=2 pr=2 pw=0 time=2765 us cost=1 size=144 card=16)'
STAT #140473929797440 id=3 cnt=1 pid=2 pos=1 obj=0 op='COUNT STOPKEY (cr=2 pr=2 pw=0 time=2759 us)'
STAT #140473929797440 id=4 cnt=1 pid=3 pos=1 obj=88657 op='INDEX RANGE SCAN TEST_IND (cr=2 pr=2 pw=0 time=2740 us cost=1 size=208 card=16)'
CLOSE #140473929797440:c=0,e=14,dep=1,type=0,tim=1602712699582338
=====================
PARSING IN CURSOR #140473930312800 len=31 dep=0 uid=0 oct=3 lid=0 tim=1602712699583773 hv=3352796757 ad='a84c2450' sqlid='ayqb8yg3xg7kp'
select * from test where id = 1
END OF STMT
PARSE #140473930312800:c=248961,e=260454,p=122,cr=274,cu=0,mis=1,r=0,dep=0,og=1,plh=2216933833,tim=1602712699583771
EXEC #140473930312800:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2216933833,tim=1602712699583942
WAIT #140473930312800: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=88656 tim=1602712699583997
WAIT #140473930312800: nam='db file sequential read' ela= 128 file#=1 block#=94961 blocks=1 obj#=88656 tim=1602712699584278
FETCH #140473930312800:c=0,e=386,p=1,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2216933833,tim=1602712699584423
WAIT #140473930312800: nam='SQL*Net message from client' ela= 808 driver id=1650815232 #bytes=1 p3=0 obj#=88656 tim=1602712699585300
FETCH #140473930312800:c=0,e=22,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2216933833,tim=1602712699585419
STAT #140473930312800 id=1 cnt=1 pid=0 pos=1 obj=88656 op='TABLE ACCESS BY INDEX ROWID TEST (cr=4 pr=1 pw=0 time=365 us cost=2 size=25 card=1)'
STAT #140473930312800 id=2 cnt=1 pid=1 pos=1 obj=88657 op='INDEX RANGE SCAN TEST_IND (cr=3 pr=0 pw=0 time=43 us cost=1 size=0 card=1)'
WAIT #140473930312800: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=88656 tim=1602712699585583
*** 2020-10-14 17:58:25.146
WAIT #140473930312800: nam='SQL*Net message from client' ela= 5561108 driver id=1650815232 #bytes=1 p3=0 obj#=88656 tim=1602712705146792
CLOSE #140473930312800:c=0,e=17,dep=0,type=0,tim=1602712705147016
//WAIT #140473930312800: nam='db file sequential read' ela= 128 file#=1 block#=94961 blocks=1 obj#=88656 tim=1602712699584278
//1号文件94961就是根据rowid需要查询的块
//dump 第一个索引的leaf块
SQL> alter system dump datafile 1 block 95762;
System altered.
SQL>
//索引中第一行数据
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 72 f1 00 00
//转换文件号和块号
select to_number('4172f1','xxxxxxxx') from dual;
SQL> select to_number('4172f1','xxxxxxxx') from dual;
TO_NUMBER('4172F1','XXXXXXXX')
------------------------------
4289265
SQL>
SQL> set lin 200
SQL> select dbms_utility.data_block_address_file(4289265), dbms_utility.data_block_address_block(4289265) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4289265) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4289265)
--------------------------------------------- ----------------------------------------------
1 94961
SQL>
//所以看到,根据INDEX里面记录的rowid(转换为文件号和块号)进行了回表。