Very
recently a question came up regarding B*Tree index in Oracle database. The
question is “How do we say (or prove) that Leaf blocks are double linked list
in B*Tree index”?
Here
is my test case to prove that.
rajesh@ORA11G> create table t as
2 select *
3 fromall_objects;
Table created.
rajesh@ORA11G>
rajesh@ORA11G> create index t_idx on t(object_id);
Index created.
rajesh@ORA11G> select
owner,segment_name,extent_id,blocks,block_id,file_id
2 fromdba_extents
3 wheresegment_name
='T_IDX'
4 and owner = user
5 order by extent_id ;
OWNER
SEGMENT_NAME EXTENT_ID BLOCKS
BLOCK_ID FILE_ID
-------------- -------------- ---------- ---------- ----------
----------
RAJESH T_IDX 0 8 1400
6
RAJESH T_IDX 1 8 1480 6
RAJESH T_IDX 2 8 1488 6
RAJESH T_IDX 3 8 1496 6
RAJESH T_IDX 4
8 1504 6
RAJESH T_IDX 5 8 1512 6
RAJESH T_IDX 6 8 1520 6
RAJESH T_IDX 7 8 1528 6
RAJESH T_IDX 8 8 1624 6
RAJESH T_IDX 9 8 1632 6
RAJESH T_IDX 10 8 1640 6
RAJESH T_IDX 11 8
1648 6
RAJESH T_IDX 12 8 1656 6
RAJESH T_IDX 13 8 1736 6
RAJESH T_IDX 14 8 1744 6
RAJESH T_IDX 15 8 1752 6
RAJESH T_IDX 16 128
183936 6
17 rows selected.
rajesh@ORA11G> alter system checkpoint;
System altered.
rajesh@ORA11G>
Created
a Table and index, and the index spans seventeen extents (while each extend
holds 8 blocks except the last which is 128 blocks) and did a checkpoint
manually to flush contents to Disk.
Let
me pickup any extent, say extend_id = 6 here and that has total of eight blocks
from 1520 to 1527.
So
let us dump the block 1521 and 1522 and 1523 and see are they connected via
double linked list.
Dump block 1521
rajesh@ORA11G> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_8480.trc
PL/SQL procedure successfully completed.
rajesh@ORA11G> alter system dump datafile 6 block 1521;
System altered.
rajesh@ORA11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Dump block 1522
C:\Users\179818>sqlplus rajesh/oracle@ora11g
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 18:09:00
2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
rajesh@ORA11G> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_8580.trc
PL/SQL procedure successfully completed.
rajesh@ORA11G> alter system dump datafile 6 block 1522;
System altered.
rajesh@ORA11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Dump block 1523
C:\Users\179818>sqlplus rajesh/oracle@ora11g
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 18:09:17
2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
rajesh@ORA11G> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora11g\ora11g\trace\ora11g_ora_11120.trc
PL/SQL procedure successfully completed.
rajesh@ORA11G> alter system dump datafile 6 block 1523;
System altered.
rajesh@ORA11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Note:
Kdxlenxt – represents the address of the next leaf block in the
B*Tree index
Kdxleprv – represents the address of the previous leaf block in the
B*Tree index
Looking at the Trace file, block 1521 shows
this
Repeat 1 times
016C9A1F0 00000000 00000000 00000000 04770602 [..............w.]
Block header dump: 0x018005f1
Object id on Block? Y
seg/obj: 0x19a7c csc:
0x507.9d6a0469 itc: 2 flg: E
typ: 2 - INDEX
brn: 0 bdba: 0x18005f0
ver: 0x01 opc: 0
inc: 0 exflg: 0
ItlXidUbaFlag LckScn/Fsc
0x01
0x0000.000.00000000
0x00000000.0000.00 ---- 0
fsc 0x0000.00000000
0x02
0xffff.000.00000000
0x00000000.0000.00 C--- 0
scn 0x0507.9d6a0469
Leaf block dump
===============
header address 382304868=0x16c98264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1755=0x6db
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25167346=0x18005f2
kdxleprv 25167343=0x18005ef
kdxledsz 0
kdxlebksz 8036
row#0[8022] flag: ------, lock: 0, len=14
Looking at the Trace file, block 1522 shows
this
0168FA7F0 00000000 00000000 00000000 04770602 [..............w.]
Block header dump: 0x018005f2
Object id on Block? Y
seg/obj: 0x19a7c csc:
0x507.9d6a0469 itc: 2 flg: E
typ: 2 - INDEX
brn: 0 bdba: 0x18005f0
ver: 0x01 opc: 0
inc: 0 exflg: 0
ItlXidUbaFlag LckScn/Fsc
0x01
0x0000.000.00000000
0x00000000.0000.00 ---- 0
fsc 0x0000.00000000
0x02
0xffff.000.00000000
0x00000000.0000.00 C--- 0
scn 0x0507.9d6a0469
Leaf block dump
===============
header address 378505316=0x168f8864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1754=0x6da
kdxcoavs 820
kdxlespl 0
kdxlende 0
kdxlenxt 25167347=0x18005f3
kdxleprv 25167345=0x18005f1
kdxledsz 0
kdxlebksz 8036
Looking at the Trace file, block 1523 shows
this
Block header dump: 0x018005f3
Object id on Block? Y
seg/obj: 0x19a7c csc:
0x507.9d6a0469 itc: 2 flg: E
typ: 2 - INDEX
brn: 0 bdba: 0x18005f0
ver: 0x01 opc: 0
inc: 0 exflg: 0
ItlXidUbaFlag LckScn/Fsc
0x01
0x0000.000.00000000
0x00000000.0000.00 ---- 0
fsc 0x0000.00000000
0x02
0xffff.000.00000000
0x00000000.0000.00 C--- 0
scn 0x0507.9d6a0469
Leaf block dump
===============
header address 251299940=0xefa8864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1755=0x6db
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25167348=0x18005f4
kdxleprv 25167346=0x18005f2
kdxledsz 0
kdxlebksz 8036
From the above highlighted – it was clear
that the block that appears next to block 1521 is 1522. Since block 1521 has kdxlenxt
=0x18005f2that in-turn is the address for the block 1522.
No comments:
Post a Comment