查看: 2774|回复: 0

[原创] ORACLE 索引内部结构和扫描方式完全探索

[复制链接]
论坛徽章:
20
娜美
日期:2017-06-26 15:18:15目光如炬
日期:2018-04-29 22:00:00火眼金睛
日期:2018-04-30 22:00:00目光如炬
日期:2018-07-29 22:00:00火眼金睛
日期:2018-08-31 22:00:00目光如炬
日期:2018-09-02 22:00:00目光如炬
日期:2018-09-16 22:00:01火眼金睛
日期:2018-09-30 22:00:00目光如炬
日期:2018-10-14 22:00:00目光如炬
日期:2018-04-22 22:00:00
发表于 2018-2-28 17:06 | 显示全部楼层 |阅读模式
作者简介:
----------------------------------------------------
@ 孙显鹏,海天起点oracle技术专家,十年从业经验,
@ 拥有OCP 11G认证,精通oracle内部原理,擅长调优
@ 和解决疑难问题,致力于帮助客户解决生产过程中出
@ 现的问题,提高生产效率。
@ 爱好书法,周易,中医!
@ 微信号:sunyunyi_sun
@ 电  话--18629679269
-----------------------------------------------------


准备环境和测试数据
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

conn to pluggable database :

alter pluggable database testpdb open;

sqlplus zfy/zfy@serdb:1521/testpdb

SQL> show con_name;

CON_NAME
------------------------------
TESTPDB

SQL> sho user;
USER is "ZFY"

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

create table tb_ti as select rownum id,'test' data from dual connect by level<=10000;

create unique index idx_tb_ti on tb_ti(id);

exec sys.dbms_stats.gather_table_stats ( OwnName => 'ZFY',TabName => 'TB_TI',Estimate_Percent => NULL,
Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);

select header_file,header_block from dba_segments where owner='ZFY' and segment_name='IDX_TB_TI';


HEADER_FILE HEADER_BLOCK
----------- ------------
         13        52570


col trace_file for a100
select a.value || '/diag/rdbms/'||b.value||'/'|| c.instance_name ||'/trace/'||c.instance_name || '_ora_' || d.spid || '.trc' trace_file
     from (select value from v$parameter where name = 'diagnostic_dest') a,
          (select value from v$parameter where name = 'db_name') b,
           (select instance_name from v$instance) c,
           (select spid
             from v$session s, v$process p, v$mystat m
            where s.PADDR = p.ADDR
              and s.SID = m.SID
              and m.STATISTIC# = 0) d
/

TRACE_FILE
----------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_7570.trc

alter system dump datafile 13 block 52570;

*** 2018-02-28T09:28:53.327604+08:00 (TESTPDB(3))
Start dump data blocks tsn: 6 file#:13 minblk 52570 maxblk 52570
Block dump from cache:
Dump of buffer cache at level 4 for pdb=3 tsn=6 rdba=25218394
BH (0x95f989f8) file#: 13 rdba: 0x0180cd5a (6/52570) class: 4 ba: 0x9569c000 --段头块
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
  dbwrid: 0 obj: 125829 objn: 125829 tsn: [3/6] afn: 13 hint: f
  hash: [0x87deb108,0x87deb108] lru: [0x95f99058,0x95f986b8]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x95f986e0,0x95f97500] objaq: [0x770dddd0,0x95f986f0]
  st: XCURRENT md: NULL fpin: 'ktswh03: ktscts' fscn: 0x1feb73b tch: 3
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x1feb744] HSUB: [1]
  Printing buffer operation history (latest change first):
  cnt: 1   
Block dump from disk:
buffer tsn: 6 rdba: 0x0180cd5a (6/52570)
scn: 0x1feb744 seq: 0x02 flg: 0x04 tail: 0xb7442302
frmt: 0x02 chkval: 0x8d39 type: 0x23=PAGETABLE SEGMENT HEADER --段头块
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FCB5FE72000 to 0x00007FCB5FE74000
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 4      #blocks: 32 --32个块-一个区8个
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x0180cd71  ext#: 3      blk#: 1      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 25   
  mapblk  0x00000000  offset: 3     
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x0180cd71  ext#: 3      blk#: 1      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 25   
  mapblk  0x00000000  offset: 3     
  Level 1 BMB for High HWM block: 0x0180cd68 --高水位
  Level 1 BMB for Low HWM block: 0x0180cd68  --低水位
  --------------------------------------------------------
  Segment Type: 2 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x0180cd59
  Last Level 1 BMB:  0x0180cd68    --最后一级位图地址
  Last Level II BMB:  0x0180cd59   --最后二级位图地址
  Last Level III BMB:  0x00000000  --无第二个三级位图
     Map Header:: next  0x00000000  #extents: 4    obj#: 125829 flag: 0x10000000
  Inc # 0
  Extent Map --区图
  -----------------------------------------------------------------
   0x0180cd58  length: 8     
   0x0180cd60  length: 8     
   0x0180cd68  length: 8     
   0x0180cd70  length: 8     

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x0180cd58 Data dba:  0x0180cd5b
   Extent 1     :  L1 dba:  0x0180cd58 Data dba:  0x0180cd60
   Extent 2     :  L1 dba:  0x0180cd68 Data dba:  0x0180cd69
   Extent 3     :  L1 dba:  0x0180cd68 Data dba:  0x0180cd70
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x0180cd59

End dump data blocks tsn: 6 file#: 13 minblk 52570 maxblk 52570


查看二级位图块:
select to_number('0cd59','xxxxxxx') from dual;
52569

alter system dump datafile 13 block 52569

*** 2018-02-28T10:11:54.346528+08:00 (TESTPDB(3))
Start dump data blocks tsn: 6 file#:13 minblk 52569 maxblk 52569
Block dump from cache:
Dump of buffer cache at level 4 for pdb=3 tsn=6 rdba=25218393
Block dump from disk:
buffer tsn: 6 rdba: 0x0180cd59 (6/52569)
scn: 0x1feb744 seq: 0x01 flg: 0x04 tail: 0xb7442101
frmt: 0x02 chkval: 0x6d6f type: 0x21=SECOND LEVEL BITMAP BLOCK --二级位图块
Hex dump of block: st=0, typ_found=1
Dump of Second Level Bitmap Block
   number: 2       nfree: 2       ffree: 0      pdba:     0x0180cd5a
   Inc #: 0 Objd: 125829 Flag: 3
  opcode:0
xid:
  L1 Ranges :
  --------------------------------------------------------
   0x0180cd58  Free: 3 Inst: 1
   0x0180cd68  Free: 7 Inst: 1

  --------------------------------------------------------
End dump data blocks tsn: 6 file#: 13 minblk 52569 maxblk 52569


dump 第一个位图块

select to_number('0cd58','xxxxxxx') from dual;

alter system dump datafile 13 block 52568


*** 2018-02-28T10:16:47.991768+08:00 (TESTPDB(3))
Start dump data blocks tsn: 6 file#:13 minblk 52568 maxblk 52568
Block dump from cache:
Dump of buffer cache at level 4 for pdb=3 tsn=6 rdba=25218392
Block dump from disk:
buffer tsn: 6 rdba: 0x0180cd58 (6/52568)
scn: 0x1feb744 seq: 0x03 flg: 0x04 tail: 0xb7442003
frmt: 0x02 chkval: 0xa4e0 type: 0x20=FIRST LEVEL BITMAP BLOCK -- 一级位图
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FCB5FE72000 to 0x00007FCB5FE74000
7FCB5FE72000 0000A220 0180CD58 01FEB744 04030000  [ ...X...D.......]
7FCB5FE72010 0000A4E0 00000000 00000000 00000000  [................]
7FCB5FE72020 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7FCB5FE72040 00000000 00000000 00000000 00000002  [................]
7FCB5FE72050 FFFFFFFF 00000000 00000003 00000010  [................]
7FCB5FE72060 00000002 00000000 00000001 00000000  [................]
7FCB5FE72070 00000000 00000003 5A9603F7 5A9603F7  [...........Z...Z]
7FCB5FE72080 00000000 00000000 00000000 00000000  [................]
7FCB5FE72090 0180CD59 00000000 00000000 00000000  [Y...............]
7FCB5FE720A0 00000000 00000000 00000000 00000000  [................]
7FCB5FE720B0 00000000 00000000 00000000 00000380  [................]
7FCB5FE720C0 0001EB85 00977D35 00000000 0180CD58  [....5}......X...]
7FCB5FE720D0 00000008 00000000 0180CD60 00000008  [........`.......]
7FCB5FE720E0 00000008 00000000 00000000 00000000  [................]
7FCB5FE720F0 00000000 00000000 00000000 00000000  [................]
        Repeat 8 times
7FCB5FE72180 00000000 00000000 00000000 55555557  [............WUUU]
7FCB5FE72190 00000000 00000000 00000000 00000000  [................]
        Repeat 15 times
7FCB5FE72290 00000000 01FEB744 00000000 00000000  [....D...........]
7FCB5FE722A0 00000000 00000000 00000000 00000000  [................]
        Repeat 63 times
7FCB5FE726A0 00000000 00000000 00000000 41414141  [............AAAA]
7FCB5FE726B0 41414141 41414141 41414141 00000000  [AAAAAAAAAAAA....]
7FCB5FE726C0 00000000 00000000 00000000 00000000  [................]
        Repeat 402 times
7FCB5FE73FF0 00000000 00000000 00000000 B7442003  [............. D.]
Dump of First Level Bitmap Block
--------------------------------
   nbits : 2 nranges: 2         parent dba:  0x0180cd59 -- 上级领导家地址   poffset: 0     
   unformatted: 0       total: 16   --低下16个兄弟     first useful block: 3      
   owning instance : 0
   instance ownership changed at 02/28/2018 09:20:55
   Last successful Search 02/28/2018 09:20:55
   Freeness Status:            nf1 0      nf2 1      nf3 0      nf4 0      nf5 0      
   Extent Map Block Offset: 4294967295
   First free datablock : 3      
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
Dealloc scn(ub4/ub4): (0x00000000.00977d35)
Format scn: 0x0000000001feb744
   Flag: 0x00000380 (REJCTX/RESV/AUX/-/-/-/-/-)
   Inc #: 0 Objd: 125829
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x0180cd58  Length: 8      Offset: 0      
   0x0180cd60  Length: 8      Offset: 8      

   0:Metadata   1:Metadata   2:Metadata   3:25-50% free --前三个就是L3,L2,L1,第四个空闲
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
  --------------------------------------------------------
  ktspfsc -
  nro:0 ncmp:0 nff:0 nxo:0 lastxs:0 nxid:0 ff:0
  clntime: 0 addtime:0 spare1:0 spare2:0
  ro: rejection opcode, xo: xid offset List
  0. ro:0 xo:-1  1. ro:0 xo:-1  2. ro:0 xo:-1  3. ro:0 xo:-1
  4. ro:0 xo:-1  5. ro:0 xo:-1  6. ro:0 xo:-1  7. ro:0 xo:-1
  8. ro:0 xo:-1  9. ro:0 xo:-1  10. ro:0 xo:-1  11. ro:0 xo:-1
  12. ro:0 xo:-1  13. ro:0 xo:-1  14. ro:0 xo:-1  15. ro:0 xo:-1
  ktspfsc xidlist -
  0. NULL  1. NULL  2. NULL
  3. NULL  4. NULL  5. NULL
  6. NULL  7. NULL  8. NULL
  9. NULL  10. NULL  11. NULL
  12. NULL  13. NULL  14. NULL
  15. NULL  16. NULL  17. NULL
  18. NULL  19. NULL  20. NULL
  21. NULL  22. NULL  23. NULL
  24. NULL  25. NULL  26. NULL
  27. NULL  28. NULL  29. NULL
  30. NULL  31. NULL  32. NULL
  33. NULL  34. NULL  35. NULL
  36. NULL  37. NULL  38. NULL
  39. NULL  40. NULL  41. NULL
  42. NULL  43. NULL  44. NULL
  45. NULL  46. NULL  47. NULL
  48. NULL  49. NULL  50. NULL
  51. NULL  52. NULL  53. NULL
  54. NULL  55. NULL  56. NULL
  57. NULL  58. NULL  59. NULL
  60. NULL  61. NULL  62. NULL
  63. NULL
  Blocks reserved for critical index section - ---index的重要信息
  Total reserved: 0
0. db:0x0 state:0
1. db:0x0 state:0
2. db:0x0 state:0
3. db:0x0 state:0
4. db:0x0 state:0
5. db:0x0 state:0
6. db:0x0 state:0
7. db:0x0 state:0
End dump data blocks tsn: 6 file#: 13 minblk 52568 maxblk 52568

dump第二个L1:

0x0180cd68
select to_number('0cd68','xxxxxxx') from dual;

alter system dump datafile 13 block 52584



*** 2018-02-28T10:34:32.877282+08:00 (TESTPDB(3))
Start dump data blocks tsn: 6 file#:13 minblk 52584 maxblk 52584
Block dump from cache:
Dump of buffer cache at level 4 for pdb=3 tsn=6 rdba=25218408
Block dump from disk:
buffer tsn: 6 rdba: 0x0180cd68 (6/52584)
scn: 0x1feb744 seq: 0x02 flg: 0x04 tail: 0xb7442002
frmt: 0x02 chkval: 0x3c08 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of First Level Bitmap Block
--------------------------------
   nbits : 2 nranges: 2         parent dba:  0x0180cd59   poffset: 1     
   unformatted: 7       total: 16        first useful block: 1      
   owning instance : 0
   instance ownership changed at
   Last successful Search
   Freeness Status:            nf1 0      nf2 0      nf3 0      nf4 0      nf5 0      
   Extent Map Block Offset: 4294967295
   First free datablock : 9      
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
Dealloc scn(ub4/ub4): (0x00000000.00977d35)
Format scn: 0x0000000001feb744
   Flag: 0x00000281 (REJCTX/-/AUX/-/-/-/-/-)
   Inc #: 0 Objd: 125829
  HWM Flag: HWM Set
      Highwater::  0x0180cd71  ext#: 3      blk#: 1      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 25   
  mapblk  0x00000000  offset: 3     
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x0180cd68  Length: 8      Offset: 0      
   0x0180cd70  Length: 8      Offset: 8      

   0:Metadata   1:FULL   2:FULL   3:FULL  --只有第一个块bmb,9号后面的没有格式化
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:unformatted   10:unformatted   11:unformatted
   12:unformatted   13:unformatted   14:unformatted   15:unformatted
  --------------------------------------------------------
  ktspfsc -
  nro:0 ncmp:0 nff:0 nxo:0 lastxs:0 nxid:0 ff:0
  clntime: 0 addtime:0 spare1:0 spare2:0
  ro: rejection opcode, xo: xid offset List
  0. ro:0 xo:-1  1. ro:0 xo:-1  2. ro:0 xo:-1  3. ro:0 xo:-1
  4. ro:0 xo:-1  5. ro:0 xo:-1  6. ro:0 xo:-1  7. ro:0 xo:-1
  8. ro:0 xo:-1  9. ro:0 xo:-1  10. ro:0 xo:-1  11. ro:0 xo:-1
  12. ro:0 xo:-1  13. ro:0 xo:-1  14. ro:0 xo:-1  15. ro:0 xo:-1
  ktspfsc xidlist -
  0. NULL  1. NULL  2. NULL
  3. NULL  4. NULL  5. NULL
  6. NULL  7. NULL  8. NULL
  9. NULL  10. NULL  11. NULL
  12. NULL  13. NULL  14. NULL
  15. NULL  16. NULL  17. NULL
  18. NULL  19. NULL  20. NULL
  21. NULL  22. NULL  23. NULL
  24. NULL  25. NULL  26. NULL
  27. NULL  28. NULL  29. NULL
  30. NULL  31. NULL  32. NULL
  33. NULL  34. NULL  35. NULL
  36. NULL  37. NULL  38. NULL
  39. NULL  40. NULL  41. NULL
  42. NULL  43. NULL  44. NULL
  45. NULL  46. NULL  47. NULL
  48. NULL  49. NULL  50. NULL
  51. NULL  52. NULL  53. NULL
  54. NULL  55. NULL  56. NULL
  57. NULL  58. NULL  59. NULL
  60. NULL  61. NULL  62. NULL
  63. NULL
  ktspfsr not configured -
End dump data blocks tsn: 6 file#: 13 minblk 52584 maxblk 52584


SQL> select bytes,extent_id,file_id,block_id,blocks,RELATIVE_FNO from dba_extents where owner='ZFY' and segment_name='IDX_TB_TI';

     BYTES  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
     65536          0         13      52568          8            6
     65536          1         13      52576          8            6
     65536          2         13      52584          8            6
     65536          3         13      52592          8            6

这是索引初始分配了16个块,4个区,每个区8个块8*8k=65536,第一个L1管理16个块也就是两个区,
LMT AUTOALLOCATE 策略:
前     16 个    extent  每个区大小为   8*8k
接下来 63 个  extent  每个区大小为  128*8k
接下来 120 个 extent  每个区大小为  1024*8k
接下来  n 个   extent  每个区大小为  8192*8k

52568 --52570位段头L3,52569为L2,52568位第一个L1
dump 第一个index block:

alter system dump datafile 13 block 52572;

Block header dump:  0x0180cd5c
Object id on Block? Y
seg/obj: 0x1eb85  csc:  0x0000000001feb73c  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x180cd58 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  0x0000000001feb73c
Leaf block dump
===============
header address 140511464071268=0x7fcb5fe72064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 520 --这个块中包括520行数据
kdxcofbo 1076=0x434
kdxcofeo 1896=0x768
kdxcoavs 820
kdxlespl 0
kdxlende 0
kdxlenxt 25218397=0x180cd5d
kdxleprv 0=0x0 --左边没有叶节点了
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 00
---[8021]代表在块内的位置,新插入的在低位置,这里我们实验插入的值是有顺序递增的,
如果没有顺序,那么在单个块内也是没有顺序,但是块外面叶是有顺序的,但是在内存中是反的
col 0; len 2; (2):  c1 02 --- select dump(1,16) from dual;后面依次
row#1[8010] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 01
col 0; len 2; (2):  c1 03
row#2[7999] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 02
col 0; len 2; (2):  c1 04
row#3[7988] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 03
col 0; len 2; (2):  c1 05
row#4[7977] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 04
col 0; len 2; (2):  c1 06
row#5[7966] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 05
col 0; len 2; (2):  c1 07
row#6[7955] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 06
col 0; len 2; (2):  c1 08
row#7[7944] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 07
col 0; len 2; (2):  c1 09
row#8[7933] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 08
col 0; len 2; (2):  c1 0a
row#9[7922] flag: -------, lock: 0, len=11, data6):  01 80 cd 43 00 09
col 0; len 2; (2):  c1 0b
row#10[7911] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 0a
col 0; len 2; (2):  c1 0c
row#11[7900] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 0b
col 0; len 2; (2):  c1 0d
row#12[7889] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 0c
col 0; len 2; (2):  c1 0e
row#13[7878] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 0d
col 0; len 2; (2):  c1 0f
row#14[7867] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 0e
col 0; len 2; (2):  c1 10
row#15[7856] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 0f
col 0; len 2; (2):  c1 11
row#16[7845] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 10
col 0; len 2; (2):  c1 12
row#17[7834] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 11
col 0; len 2; (2):  c1 13
row#18[7823] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 12
col 0; len 2; (2):  c1 14
row#19[7812] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 13
col 0; len 2; (2):  c1 15
row#20[7801] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 14


.....

alter session set events 'immediate trace name treedump level 125829';

----- begin tree dump
branch: 0x180cd5b 25218395 (0: nrow: 20, level: 1) 共20个叶块,
   leaf: 0x180cd5c 25218396 (-1: row:520.520 avs:820) --这个叶块就是我们刚dump的
   leaf: 0x180cd5d 25218397 (0: row:513.513 avs:819)
   leaf: 0x180cd5e 25218398 (1: row:513.513 avs:819)
   leaf: 0x180cd5f 25218399 (2: row:513.513 avs:819)
   leaf: 0x180cd60 25218400 (3: row:513.513 avs:819)
   leaf: 0x180cd61 25218401 (4: row:513.513 avs:819)
   leaf: 0x180cd62 25218402 (5: row:513.513 avs:819)
   leaf: 0x180cd63 25218403 (6: row:513.513 avs:820)
   leaf: 0x180cd64 25218404 (7: row:513.513 avs:819)
   leaf: 0x180cd65 25218405 (8: row:513.513 avs:819)
   leaf: 0x180cd66 25218406 (9: row:513.513 avs:819)
   leaf: 0x180cd67 25218407 (10: row:513.513 avs:819)
   leaf: 0x180cd69 25218409 (11: row:513.513 avs:819)
   leaf: 0x180cd6a 25218410 (12: row:513.513 avs:819)
   leaf: 0x180cd6b 25218411 (13: row:513.513 avs:820)
   leaf: 0x180cd6c 25218412 (14: row:513.513 avs:819)
   leaf: 0x180cd6d 25218413 (15: row:513.513 avs:819)
   leaf: 0x180cd6e 25218414 (16: row:513.513 avs:819)
   leaf: 0x180cd6f 25218415 (17: row:513.513 avs:819)
   leaf: 0x180cd70 25218416 (18: row:246.246 avs:4555)
----- end tree dump

select BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR from dba_indexes where index_name='IDX_TB_TI';


    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
---------- ----------- ------------- ----------------------- ----------------------- -----------------
         1          20         10000                       1                       1                20

统计信息很准确,总20个叶节点,不同值10000,聚合因子20因为没有下一个rowid在不同数据块所以20个叶聚合因子就是20

接下来我们看看唯一索引等值链接扫描方式:

ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';


select id from tb_ti where id=10000;

        ID
----------
     10000


Execution Plan
----------------------------------------------------------
Plan hash value: 2239560929

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IDX_TB_TI |     1 |     4 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=10000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        536  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
trace:

ktrgtc2(): started for block <0x0006 : 0x0180cd5b> objd: 0x0001eb85
  env [0x7fa10c6b2ed4]: (scn: 0x0000000001ff89a9   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ff8951  flg: 0x00000661)
ktrexc(): returning 2 on:  0x7fa111e22f30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgtc2(): completed for block <0x0006 : 0x0180cd5b> objd: 0x0001eb85
ktrgtc2(): started for block <0x0006 : 0x0180cd70> objd: 0x0001eb85
  env [0x7fa10c6b2ed4]: (scn: 0x0000000001ff89a9   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000001ff89a9  ma-scn: 0x0000000001ff8951  flg: 0x00000662)
ktrexc(): returning 2 on:  0x7fa111e22f30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgtc2(): completed for block <0x0006 : 0x0180cd70> objd: 0x0001eb85


首先扫描 根节点block <0x0006 : 0x0180cd5b>,就是上面index结构中的branch: 0x180cd5b
然后扫描 叶节点block <0x0006 : 0x0180cd70>,因为条件为10000该id正好在leaf: 0x180cd70 25218416 (18: row:246.246 avs:4555)块中


从上面信息清楚看到唯一索引扫描的块数=BLEVEL+1或者+2如果存在行链接的情况,我们的实验就是2个块



select * from tb_ti where id=10000;

trace:
ktrgtc2(): started for block <0x0006 : 0x0180cd5b> objd: 0x0001eb85
  env [0x7fa10c6b6fb4]: (scn: 0x0000000001ff8f3d   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ff8e86  flg: 0x00000661)
ktrexc(): returning 2 on:  0x7fa111e22f30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgtc2(): completed for block <0x0006 : 0x0180cd5b> objd: 0x0001eb85
ktrgtc2(): started for block <0x0006 : 0x0180cd70> objd: 0x0001eb85
  env [0x7fa10c6b6fb4]: (scn: 0x0000000001ff8f3d   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000001ff8f3d  ma-scn: 0x0000000001ff8e86  flg: 0x00000662)
ktrexc(): returning 2 on:  0x7fa111e22f30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgtc2(): completed for block <0x0006 : 0x0180cd70> objd: 0x0001eb85
ktrgtc2(): started for block <0x0006 : 0x0180cd57> objd: 0x0001eb84
  env [0x7fa10c6b6fb4]: (scn: 0x0000000001ff8f3d   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000001ff8f3d  ma-scn: 0x0000000001ff8e86  flg: 0x00000662)
kcbzib: called kcfrbd1 with makecr 0 dsf 0x500000 dsf2 0x0 cursor valid 1 bscn 0x1feb71b lfb 18


select object_name from dba_objects where object_id=to_number('0001eb84','xxxxxxxxx')
OBJECT_NAME
--------------------------------------------------------------------------------
TB_TI

因为select * 所以在上面的步骤上需要再回表访问数据

接下来看看所以范围扫描:

select * from tb_ti where id>0 and id<523; --这样需要访问两个叶节点

select * from tb_ti where id>0 and id<523; --这样需要访问两个索引叶节点
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85    --叶节点
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84  --数据块
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85  
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): completed for  block <0x0006 : 0x0180cd5c> objd: 0x0001eb85
ktrget2(): started for block  <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): completed for  block <0x0006 : 0x0180cd43> objd: 0x0001eb84
ktrget2(): started for block  <0x0006 : 0x0180cd5d> objd: 0x0001eb85     --第二个叶节点
ktrget2(): completed for  block <0x0006 : 0x0180cd5d> objd: 0x0001eb85


[oracle@serdb trace]$ more /u02/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_29735.trc| grep ktrget2 |wc -l
146

0x0001eb84 为表TB_TI,数据块0x0180cd43含有524行,那么也就是我们的查询只需要访问一个数据块即可
SQL> select to_number('0cd43','xxxxxxxxx') from dual;

TO_NUMBER('0CD43','XXXXXXXXX')
------------------------------
                         52547

alter system dump datafile 13 block 52547
Object id on Block? Y
seg/obj: 0x1eb84  csc:  0x0000000001feb715  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x180cd40 ver: 0x01 opc: 0
     inc: 0  exflg: 0
===============
tsiz: 0x1f80
hsiz: 0x42a
pbl: 0x7fe420d7907c
     76543210
flag=--------
ntab=1
nrow=524
frre=-1
fsbo=0x42a
fseo=0x758
avsp=0x32e
tosp=0x32e
0xeti[0]      nrow=524        offs=0
0x12ri[0]     offs=0x1f75
0x14ri[1]     offs=0x1f6a
0x16ri[2]     offs=0x1f5f
0x18ri[3]     offs=0x1f54
0x1ari[4]     offs=0x1f49


从上面的trace可以清楚看到索引范围扫描,访问一次叶节点---访问一次数据块---如此循环




接下来我们分析普通索引:

drop index idx_tb_ti;

create index idx_tb_ti on tb_ti(id);

select object_id from dba_objects where object_name='IDX_TB_TI'

alter session set events 'immediate trace name treedump level 125833';


----- begin tree dump
branch: 0x180cd5b 25218395 (0: nrow: 21, level: 1)
   leaf: 0x180cd5c 25218396 (-1: row:485.485 avs:824) --第一个叶块包含了485行
   leaf: 0x180cd5d 25218397 (0: row:479.479 avs:816)
   leaf: 0x180cd5e 25218398 (1: row:479.479 avs:816)
   leaf: 0x180cd5f 25218399 (2: row:479.479 avs:816)
   leaf: 0x180cd60 25218400 (3: row:479.479 avs:816)
   leaf: 0x180cd61 25218401 (4: row:478.478 avs:830)
   leaf: 0x180cd62 25218402 (5: row:479.479 avs:816)
   leaf: 0x180cd63 25218403 (6: row:479.479 avs:816)
   leaf: 0x180cd64 25218404 (7: row:479.479 avs:816)
   leaf: 0x180cd65 25218405 (8: row:478.478 avs:830)
   leaf: 0x180cd66 25218406 (9: row:479.479 avs:816)
   leaf: 0x180cd67 25218407 (10: row:479.479 avs:816)
   leaf: 0x180cd69 25218409 (11: row:479.479 avs:816)
   leaf: 0x180cd6a 25218410 (12: row:479.479 avs:816)
   leaf: 0x180cd6b 25218411 (13: row:478.478 avs:830)
   leaf: 0x180cd6c 25218412 (14: row:479.479 avs:816)
   leaf: 0x180cd6d 25218413 (15: row:479.479 avs:816)
   leaf: 0x180cd6e 25218414 (16: row:479.479 avs:816)
   leaf: 0x180cd6f 25218415 (17: row:479.479 avs:816)
   leaf: 0x180cd70 25218416 (18: row:478.478 avs:830)
   leaf: 0x180cd71 25218417 (19: row:418.418 avs:1731)
----- end tree dump

alter system dump datafile 13 block 52572;

Block header dump:  0x0180cd5c
Object id on Block? Y
seg/obj: 0x1eb89  csc:  0x0000000001ffb606  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x180cd58 ver: 0x01 opc: 0
     inc: 0  exflg: 0
Leaf block dump
===============
header address 139768363888740=0x7f1e5bae3064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1830=0x726
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 25218397=0x180cd5d
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):  01 80 cd 43 00 00
row#1[8008] flag: -------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 cd 43 00 01
row#2[7996] flag: -------, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  01 80 cd 43 00 02
row#3[7984] flag: -------, lock: 0, len=12
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  01 80 cd 43 00 03
row#4[7972] flag: -------, lock: 0, len=12
col 0; len 2; (2):  c1 06
col 1; len 6; (6):  01 80 cd 43 00 04
row#5[7960] flag: -------, lock: 0, len=12
col 0; len 2; (2):  c1 07
col 1; len 6; (6):  01 80 cd 43 00 05

下面是上面唯一索引的dump信息:
row#0[8021] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 00
col 0; len 2; (2):  c1 02 --- select dump(1,16) from dual;后面依次
row#1[8010] flag: -------, lock: 0, len=11, data:(6):  01 80 cd 43 00 01
col 0; len 2; (2):  c1 03

格式不一样了,普通索引多了一列,该列值和唯一索引的data:(6):  01 80 cd 43 00 00 值是相同的。
也就是说唯一索引和普通索引结构不一样。

01 80 cd 43 00 00  这个值是什么意思呢?哈哈仔细看就会发现前面四个数字合起来就是数据库的地址呀!
后面两位表示在该块中的位置了。所以这里就需要明确了索引叶节点第一个值为索引列的值,第二个值为数据
行在数据块中的地址和位置了,并不是我们理解的rowid,其实和rowid意思相同。

select to_number('cd43','xxxxxxx') from dual  --52547
我们dump 13 52547 块看看:
flag=--------
ntab=1
nrow=524
frre=-1
fsbo=0x42a
fseo=0x758
avsp=0x32e
tosp=0x32e
0xeti[0]      nrow=524        offs=0
0x12ri[0]     offs=0x1f75
0x14ri[1]     offs=0x1f6a
0x16ri[2]     offs=0x1f5f
0x18:pri[3]     offs=0x1f54
0x1a:pri[4]     offs=0x1f49
0x1c:pri[5]     offs=0x1f3e
0x1e:pri[6]     offs=0x1f33
0x20:pri[7]     offs=0x1f28
0x22:pri[8]     offs=0x1f1d
0x24:pri[9]     offs=0x1f12
0x26:pri[10]    offs=0x1f07
0x28:pri[11]    offs=0x1efc
....
0x428:pri[523]  offs=0x758
block_row_dump:
tab 0, row 0, @0x1f75
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 4]  74 65 73 74
tab 0, row 1, @0x1f6a
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03
col  1: [ 4]  74 65 73 74
tab 0, row 2, @0x1f5f
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 04
col  1: [ 4]  74 65 73 74
tab 0, row 3, @0x1f54
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2


接下来我们看看索引的扫描方式:

ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';

select id from tb_ti where id=10000;

ktrgtc2(): started for block <0x0006 : 0x0180cd5b> objd: 0x0001eb89
  env [0x7f496bbfeed4]: (scn: 0x0000000001ffca3c   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ffc9dd  flg: 0x00000661)
ktrexc(): returning 2 on:  0x7f497136ef30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgtc2(): completed for block <0x0006 : 0x0180cd5b> objd: 0x0001eb89
ktrget2(): started for block  <0x0006 : 0x0180cd71> objd: 0x0001eb89
env [0x7f496bbfeed4]: (scn: 0x0000000001ffca3c   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x0000000
000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ffc9dd  flg: 0x00000660)
ktrexf(): returning 9 on:  0x7f497136ef30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgcm(): completed for block  <0x0006 : 0x0180cd71> objd: 0x0001eb89
ktrget2(): completed for  block <0x0006 : 0x0180cd71> objd: 0x0001eb89

和唯一索引没什么区别

那么我们修改id=1为id=10000,看看会怎么样

update tb_ti set id=10000 where id=1; commit;

ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';

select id from tb_ti where id=10000;

        ID
----------
     10000
     10000

ktrgtc2(): started for block <0x0006 : 0x0180cd5b> objd: 0x0001eb89
  env [0x7fdb137dced4]: (scn: 0x0000000001ffcc76   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ffcc04  flg: 0x00000661)
ktrexc(): returning 2 on:  0x7fdb18f4cf30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgtc2(): completed for block <0x0006 : 0x0180cd5b> objd: 0x0001eb89
ktrget2(): started for block  <0x0006 : 0x0180cd71> objd: 0x0001eb89
env [0x7fdb137dced4]: (scn: 0x0000000001ffcc76   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x0000000
000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ffcc04  flg: 0x00000660)
ktrexf(): returning 9 on:  0x7fdb18f4cf30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgcm(): completed for block  <0x0006 : 0x0180cd71> objd: 0x0001eb89
ktrget2(): completed for  block <0x0006 : 0x0180cd71> objd: 0x0001eb89
ktrget2(): started for block  <0x0006 : 0x0180cd71> objd: 0x0001eb89
env [0x7fdb137dced4]: (scn: 0x0000000001ffcc76   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x0000000
000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ffcc04  flg: 0x00000660)
ktrexf(): returning 9 on:  0x7fdb18f4cf30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgcm(): completed for block  <0x0006 : 0x0180cd71> objd: 0x0001eb89
ktrget2(): completed for  block <0x0006 : 0x0180cd71> objd: 0x0001eb89


SQL> select dump(10000,16) from dual;

DUMP(10000,16)
--------------------------------------------------------------------------------
Typ=2 Len=2: c3,2

select
dbms_rowid.rowid_relative_fno(rowid) ralative_fno,
dbms_rowid.rowid_block_number(rowid) block_number
from TB_TI where id=10000;


RALATIVE_FNO BLOCK_NUMBER
------------ ------------
           6        52547
           6        52567
select to_number('0cd71','xxxxxxxxx') from dual;
alter system dump datafile 13 block 52593

col 0; len 2; (2):  c3 02
col 1; len 6; (6):  01 80 cd 43 00 00
row#418[2603] flag: -------, lock: 0, len=12
col 0; len 2; (2):  c3 02
col 1; len 6; (6):  01 80 cd 57 00 a9
----- end of leaf block Logical dump -----

看到了吧,当修改了id=1为id=10000,那么oracle修改了id=10000行的索引叶节点信息在其后面添加了相同ID的块号和位置信息
所以,这就是普通索引和唯一索引的区别了。

那么到这里基本上索引的结构就讲的很清楚了。


下面我们看看索引键值为复杂字符会怎么样?

create table tb_tc as select 'txeddsdagasgasdgasedASDFASDGFGDASADSFASD'|| to_char(rownum+1234) ID from dual connect by level<=10000;

create index idx_tb_tc on tb_tc(id);

exec sys.dbms_stats.gather_table_stats ( OwnName => 'ZFY',TabName => 'TB_TC',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);

select object_id from dba_objects where owner='ZFY' and object_name='IDX_TB_TC';--125835
alter session set events 'immediate trace name treedump level 125835';
select max(id) from tb_tc; ---txeddsdagasgasdgasedASDFASDGFGDASADSFASD9999

ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';

select * from tb_tc where id='txeddsdagasgasdgasedASDFASDGFGDASADSFASD9999'


ktrgtc2(): started for block <0x0006 : 0x0181894b> objd: 0x0001eb8b
  env [0x7fd84fa4aed4]: (scn: 0x0000000001ffdd30   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x00000
00000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ffdd26  flg: 0x00000661)
ktrexc(): returning 2 on:  0x7fd8551caf30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgtc2(): completed for block <0x0006 : 0x0181894b> objd: 0x0001eb8b
ktrget2(): started for block  <0x0006 : 0x0181899f> objd: 0x0001eb8b
env [0x7fd84fa4aed4]: (scn: 0x0000000001ffdd30   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000  st-scn: 0x0000000
000000000  hi-scn: 0x0000000000000000  ma-scn: 0x0000000001ffdd26  flg: 0x00000660)
ktrexf(): returning 9 on:  0x7fd8551caf30  cr-scn: 0xffffffffffffffff   xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  cl-scn: 0xffffffffffffffff  sfl: 0
ktrgcm(): completed for block  <0x0006 : 0x0181899f> objd: 0x0001eb8b
ktrget2(): completed for  block <0x0006 : 0x0181899f> objd: 0x0001eb8b

trace显示没有任何区别。
2018-02-28
孙显鹏






您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表