ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120228]IOT索引组织表相关信息的学习.txt

[20120228]IOT索引组织表相关信息的学习.txt

原创 Linux操作系统 作者:lfree 时间:2012-02-28 11:07:34 0 删除 编辑
[20120228]IOT索引组织表相关信息的学习.txt

IOT 是一种特殊的索引结构,使用它能够解决特定场合的应用问题,但是在许多应用中很少使用,更多的是使用堆表。
我仅仅在生产系统中对3个表使用,我偏向使用静态以及"只读"的小表。

如果IOT表存在除主键外的第2索引,如果使用它存在物理猜"physical guess",我以前的理解一直是第2索引记录了主键信息,
通过这个信息就可以定位IOT表中对应的数据,一直没有很好的测试与理解。我最近也看了两个链接,介绍了IOT的内容:

http://richardfoote.wordpress.com
http://mwidlake.wordpress.com

今天测试看看,看了以前的理解存在很大的偏差!

1.测试环境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t_iot ( a varchar2(10),b varchar2(10),vc varchar2(1200), constraint t_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX;
SQL> create index i_t_iot_b on t_iot(b);
insert into t_iot values ('1'        ,'a','a');
insert into t_iot values ('22'       ,'b','a');
insert into t_iot values ('333'      ,'c','a');
insert into t_iot values ('4444'     ,'d','a');
insert into t_iot values ('55555'    ,'e','a');
insert into t_iot values ('666666'   ,'f','a');
insert into t_iot values ('7777777'  ,'g','a');
insert into t_iot values ('88888888' ,'h','a');
insert into t_iot values ('999999999','i','a');
commit ;

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');

2.转储IOT块的信息:

SQL> SELECT index_name, table_name, blevel, leaf_blocks FROM dba_indexes  WHERE table_name = 'T_IOT';
INDEX_NAME                     TABLE_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
T_IOT_PK                       T_IOT                                   0           1
I_T_IOT_B                      T_IOT                                   0           1

--可以发现索引很小,blevel=0,leaf_blocks=1.

SQL> select header_file,header_block from dba_segments where segment_name='T_IOT_PK';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4          954

SQL> select object_id,data_object_id from dba_objects where object_name='T_IOT_PK';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     83453          83453

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 83453';

----- begin tree dump
leaf: 0x10003bb 16778171 (0: nrow: 9 rrow: 9)
----- end tree dump

仅仅占用1个块。HEADER_BLOCK=954,根节点=955.

SQL> alter system dump datafile 4 block 955 ;

Block header dump:  0x010003bb
 Object id on Block? Y
 seg/obj: 0x145fd  csc: 0x00.63a965  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10003b8 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   0x0002.020.00000c9f  0x00c00b1b.066e.02  --U-    9  fsc 0x0000.0063a9ce
Leaf block dump
===============
header address 182924431972=0x2a97255264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 9
kdxcofbo 54=0x36
kdxcofeo 7897=0x1ed9
kdxcoavs 7843
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8021] flag: K-----, lock: 2, len=11
col 0; len 1; (1):  31
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  61
col  1: [ 1]  61
row#1[8009] flag: K-----, lock: 2, len=12
col 0; len 2; (2):  32 32
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  62
col  1: [ 1]  61
row#2[7996] flag: K-----, lock: 2, len=13
col 0; len 3; (3):  33 33 33
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  63
col  1: [ 1]  61
row#3[7982] flag: K-----, lock: 2, len=14
col 0; len 4; (4):  34 34 34 34
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  64
col  1: [ 1]  61
row#4[7967] flag: K-----, lock: 2, len=15
col 0; len 5; (5):  35 35 35 35 35
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  65
col  1: [ 1]  61
row#5[7951] flag: K-----, lock: 2, len=16
col 0; len 6; (6):  36 36 36 36 36 36
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  66
col  1: [ 1]  61
row#6[7934] flag: K-----, lock: 2, len=17
col 0; len 7; (7):  37 37 37 37 37 37 37
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  67
col  1: [ 1]  61
row#7[7916] flag: K-----, lock: 2, len=18
col 0; len 8; (8):  38 38 38 38 38 38 38 38
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  68
col  1: [ 1]  61
row#8[7897] flag: K-----, lock: 2, len=19
col 0; len 9; (9):  39 39 39 39 39 39 39 39 39
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  69
col  1: [ 1]  61
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 955 maxblk 955

--可以发现没有记录rowid信息,
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  69
col  1: [ 1]  61
--记录了主键外的信息。

3.转储第2索引的信息:
SQL> select header_file,header_block from dba_segments where segment_name='I_T_IOT_B';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4          962

SQL> select object_id,data_object_id from dba_objects where object_name='I_T_IOT_B';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     83454          83454

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 83454';
----- begin tree dump
leaf: 0x10003c3 16778179 (0: nrow: 9 rrow: 9)
----- end tree dump

仅仅占用1个块。HEADER_BLOCK=962,根节点=963.

SQL> alter system dump datafile 4 block 963 ;

Block header dump:  0x010003c3
 Object id on Block? Y
 seg/obj: 0x145fe  csc: 0x00.63a9bd  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10003c0 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   0x0002.020.00000c9f  0x00c00b1b.066e.03  --U-    9  fsc 0x0000.0063a9ce
Leaf block dump
===============
header address 182924431972=0x2a97255264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 9
kdxcofbo 54=0x36
kdxcofeo 7870=0x1ebe
kdxcoavs 7816
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8018] flag: K-----, lock: 2, len=14
col 0; len 1; (1):  61
col 1; len 1; (1):  31
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#1[8003] flag: K-----, lock: 2, len=15
col 0; len 1; (1):  62
col 1; len 2; (2):  32 32
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#2[7987] flag: K-----, lock: 2, len=16
col 0; len 1; (1):  63
col 1; len 3; (3):  33 33 33
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#3[7970] flag: K-----, lock: 2, len=17
col 0; len 1; (1):  64
col 1; len 4; (4):  34 34 34 34
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#4[7952] flag: K-----, lock: 2, len=18
col 0; len 1; (1):  65
col 1; len 5; (5):  35 35 35 35 35
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#5[7933] flag: K-----, lock: 2, len=19
col 0; len 1; (1):  66
col 1; len 6; (6):  36 36 36 36 36 36
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#6[7913] flag: K-----, lock: 2, len=20
col 0; len 1; (1):  67
col 1; len 7; (7):  37 37 37 37 37 37 37
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#7[7892] flag: K-----, lock: 2, len=21
col 0; len 1; (1):  68
col 1; len 8; (8):  38 38 38 38 38 38 38 38
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#8[7870] flag: K-----, lock: 2, len=22
col 0; len 1; (1):  69
col 1; len 9; (9):  39 39 39 39 39 39 39 39 39
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 963 maxblk 963

-- 可以发现第2索引的记录信息如下:
col0 =》对应的是字段b
col1 =》对应的是字段a,也就是主键。
而后面还存在如下信息:
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
-- 可以发现记录的信息都是一样的,col0: [ 4]  01 00 03 bb,应该跟上面的转储对上:

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 83453';
----- begin tree dump
leaf: 0x10003bb 16778171 (0: nrow: 9 rrow: 9)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- end tree dump

也就是T_IOT_PK的根节点。
可以得到一些推测:
1.如果通过第2索引查询主键,是不需要查询IOT表的,因为第2索引里面已经存在。
2.如果通过第2索引查找数据信息,IOT先通过索引节点中记录的数据块信息查询(这个就应该就是物理猜),如果不行可以通过主键也就是对应例子的col1键值,来探查IOT索引组织表。

4.做一些测试:

SQL> set autotrace traceonly ;
SQL> select a from t_iot where b='a';
Execution Plan
----------------------------------------------------------
Plan hash value: 3946844304
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     8 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T_IOT_B |     1 |     8 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"='a')

--可以发现并没有查询IOT表,而是直接访问索引。

SQL> select vc from t_iot where b='a';
Execution Plan
----------------------------------------------------------
Plan hash value: 1095339046
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| T_IOT_PK  |     1 |     4 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| I_T_IOT_B |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"='a')
   2 - access("B"='a')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--可以发现先查询i_t_iot_b索引,然后在查询T_IOT索引组织表。

5.再来了解一下逻辑rowid。

SQL> column vc format a30
SQL> select rowid,a.* from t_iot a;
ROWID                     A          B          VC
------------------------- ---------- ---------- ------------------------------
*BAEAA7sBMf4              1          a          a
*BAEAA7sCMjL+             22         b          a
*BAEAA7sDMzMz/g           333        c          a
*BAEAA7sENDQ0NP4          4444       d          a
*BAEAA7sFNTU1NTX+         55555      e          a
*BAEAA7sGNjY2NjY2/g       666666     f          a
*BAEAA7sHNzc3Nzc3N/4      7777777    g          a
*BAEAA7sIODg4ODg4ODj+     88888888   h          a
*BAEAA7sJOTk5OTk5OTk5/g   999999999  i          a

9 rows selected.

--可以发现一些特点,前面都是一样的都是*BAEAA7s,而且主键键值的长度越长与rowid的长度也越长。再dump(rowid)

SQL> column x format a70
SQL> select dump(rowid) x,rowid,a,b from t_iot ;
X                                                                      ROWID                     A          B
---------------------------------------------------------------------- ------------------------- ---------- ----------
Typ=208 Len=9: 2,4,1,0,3,187,1,49,254                                  *BAEAA7sBMf4              1          a
Typ=208 Len=10: 2,4,1,0,3,187,2,50,50,254                              *BAEAA7sCMjL+             22         b
Typ=208 Len=11: 2,4,1,0,3,187,3,51,51,51,254                           *BAEAA7sDMzMz/g           333        c
Typ=208 Len=12: 2,4,1,0,3,187,4,52,52,52,52,254                        *BAEAA7sENDQ0NP4          4444       d
Typ=208 Len=13: 2,4,1,0,3,187,5,53,53,53,53,53,254                     *BAEAA7sFNTU1NTX+         55555      e
Typ=208 Len=14: 2,4,1,0,3,187,6,54,54,54,54,54,54,254                  *BAEAA7sGNjY2NjY2/g       666666     f
Typ=208 Len=15: 2,4,1,0,3,187,7,55,55,55,55,55,55,55,254               *BAEAA7sHNzc3Nzc3N/4      7777777    g
Typ=208 Len=16: 2,4,1,0,3,187,8,56,56,56,56,56,56,56,56,254            *BAEAA7sIODg4ODg4ODj+     88888888   h
Typ=208 Len=17: 2,4,1,0,3,187,9,57,57,57,57,57,57,57,57,57,254         *BAEAA7sJOTk5OTk5OTk5/g   999999999  i

9 rows selected.

SQL> select dump(rowid,16) x,rowid,a,b from t_iot ;
X                                                                      ROWID                     A          B
---------------------------------------------------------------------- ------------------------- ---------- ----------
Typ=208 Len=9: 2,4,1,0,3,bb,1,31,fe                                    *BAEAA7sBMf4              1          a
Typ=208 Len=10: 2,4,1,0,3,bb,2,32,32,fe                                *BAEAA7sCMjL+             22         b
Typ=208 Len=11: 2,4,1,0,3,bb,3,33,33,33,fe                             *BAEAA7sDMzMz/g           333        c
Typ=208 Len=12: 2,4,1,0,3,bb,4,34,34,34,34,fe                          *BAEAA7sENDQ0NP4          4444       d
Typ=208 Len=13: 2,4,1,0,3,bb,5,35,35,35,35,35,fe                       *BAEAA7sFNTU1NTX+         55555      e
Typ=208 Len=14: 2,4,1,0,3,bb,6,36,36,36,36,36,36,fe                    *BAEAA7sGNjY2NjY2/g       666666     f
Typ=208 Len=15: 2,4,1,0,3,bb,7,37,37,37,37,37,37,37,fe                 *BAEAA7sHNzc3Nzc3N/4      7777777    g
Typ=208 Len=16: 2,4,1,0,3,bb,8,38,38,38,38,38,38,38,38,fe              *BAEAA7sIODg4ODg4ODj+     88888888   h
Typ=208 Len=17: 2,4,1,0,3,bb,9,39,39,39,39,39,39,39,39,39,fe           *BAEAA7sJOTk5OTk5OTk5/g   999999999  i

9 rows selected.

--结合dump(rowid),dump(rowid,16)的转出信息.很容易推导出rowid的信息。以下的例子来说明:
Typ=208 Len=17: 2,4,1,0,3,bb,9,39,39,39,39,39,39,39,39,39,fe           *BAEAA7sJOTk5OTk5OTk5/g   999999999  i

1.开头的2,4,以及结尾fe实在不好猜测,不过好像是固定不变的。
2.中间的1,0,3,bb正好对应的就是IOT的根节点,也就是对应数据IOT的数据块。
3.9,39,39,39,39,39,39,39,39,39中9表示主键长度,39,39,39,39,39,39,39,39,39就是主键的信息。

6.关于物理猜:
SQL> column name format a30
SQL> column value format 999999999
SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND lower(n.name) like '%leaf%';
NAME                                VALUE
------------------------------ ----------
leaf node splits                        0
leaf node 90-10 splits                  0

SQL> update t_iot set vc=lpad('a',1000,'a') ;
9 rows updated.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND lower(n.name) like '%leaf%';
NAME                                VALUE
------------------------------ ----------
leaf node splits                        1
leaf node 90-10 splits                  0

SQL> commit;

--vc长度加大到1000,原来的IOT已经无法存在这些信息,可以发现IOT表发生了1次索引分裂。

SQL> select dump(rowid,16) x,rowid,a,b from t_iot ;
X                                                                      ROWID                     A          B
---------------------------------------------------------------------- ------------------------- ---------- ----------
Typ=208 Len=9: 2,4,1,0,3,bd,1,31,fe                                    *BAEAA70BMf4              1          a
Typ=208 Len=10: 2,4,1,0,3,bd,2,32,32,fe                                *BAEAA70CMjL+             22         b
Typ=208 Len=11: 2,4,1,0,3,bd,3,33,33,33,fe                             *BAEAA70DMzMz/g           333        c
Typ=208 Len=12: 2,4,1,0,3,bd,4,34,34,34,34,fe                          *BAEAA70ENDQ0NP4          4444       d
Typ=208 Len=13: 2,4,1,0,3,bd,5,35,35,35,35,35,fe                       *BAEAA70FNTU1NTX+         55555      e
Typ=208 Len=14: 2,4,1,0,3,bd,6,36,36,36,36,36,36,fe                    *BAEAA70GNjY2NjY2/g       666666     f
Typ=208 Len=15: 2,4,1,0,3,be,7,37,37,37,37,37,37,37,fe                 *BAEAA74HNzc3Nzc3N/4      7777777    g
Typ=208 Len=16: 2,4,1,0,3,be,8,38,38,38,38,38,38,38,38,fe              *BAEAA74IODg4ODg4ODj+     88888888   h
Typ=208 Len=17: 2,4,1,0,3,be,9,39,39,39,39,39,39,39,39,39,fe           *BAEAA74JOTk5OTk5OTk5/g   999999999  i
9 rows selected.

--可以发现逻辑rowid记录的块信息发现了变化。从1,0,3,bb变成了1,0,3,bd和1,0,3,be.

--再次转储第2索引的信息。
SQL> alter system dump datafile 4 block 963 ;

Block header dump:  0x010003c3
 Object id on Block? Y
 seg/obj: 0x145fe  csc: 0x00.63a9bd  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10003c0 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   0x0002.020.00000c9f  0x00c00b1b.066e.03  --U-    9  fsc 0x0000.0063a9ce
Leaf block dump
===============
header address 182925401700=0x2a97341e64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 9
kdxcofbo 54=0x36
kdxcofeo 7870=0x1ebe
kdxcoavs 7816
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8018] flag: K-----, lock: 2, len=14
col 0; len 1; (1):  61
col 1; len 1; (1):  31
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#1[8003] flag: K-----, lock: 2, len=15
col 0; len 1; (1):  62
col 1; len 2; (2):  32 32
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#2[7987] flag: K-----, lock: 2, len=16
col 0; len 1; (1):  63
col 1; len 3; (3):  33 33 33
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#3[7970] flag: K-----, lock: 2, len=17
col 0; len 1; (1):  64
col 1; len 4; (4):  34 34 34 34
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#4[7952] flag: K-----, lock: 2, len=18
col 0; len 1; (1):  65
col 1; len 5; (5):  35 35 35 35 35
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#5[7933] flag: K-----, lock: 2, len=19
col 0; len 1; (1):  66
col 1; len 6; (6):  36 36 36 36 36 36
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#6[7913] flag: K-----, lock: 2, len=20
col 0; len 1; (1):  67
col 1; len 7; (7):  37 37 37 37 37 37 37
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#7[7892] flag: K-----, lock: 2, len=21
col 0; len 1; (1):  68
col 1; len 8; (8):  38 38 38 38 38 38 38 38
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
row#8[7870] flag: K-----, lock: 2, len=22
col 0; len 1; (1):  69
col 1; len 9; (9):  39 39 39 39 39 39 39 39 39
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 963 maxblk 963

--可以发现如下信息没有发生变化,依旧是col  0: [ 4]  01 00 03 bb.
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bb

这样如果我们执行select substr(vc,1,20) from t_iot from where b='a';通过第2索引记录的数据块查询应该找不到vc的值(可以理解物理猜失败),
必须在通过主键来查询,这样逻辑读就会比原来加1.
SQL> set autotrace traceonly
SQL> select substr(vc,1,20) from t_iot where b='a';
Execution Plan
----------------------------------------------------------
Plan hash value: 1095339046

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

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

   1 - access("B"='a')
   2 - access("B"='a')

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

7.现在我们重建第2索引看看。
SQL> alter index i_t_iot_b rebuild ;
Index altered.

SQL> select header_file,header_block from dba_segments where segment_name='I_T_IOT_B';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4          970

SQL> select object_id,data_object_id from dba_objects where object_name='I_T_IOT_B';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     83454          83455

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');
PL/SQL procedure successfully completed.

SQL> SELECT index_name, table_name, blevel, leaf_blocks FROM dba_indexes  WHERE table_name = 'T_IOT';
INDEX_NAME                     TABLE_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ------------------------------ ---------- -----------
T_IOT_PK                       T_IOT                                   1           2
I_T_IOT_B                      T_IOT                                   0           1

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 83454';
Session altered.
----- begin tree dump
leaf: 0x10003cb 16778187 (0: nrow: 9 rrow: 9)
----- end tree dump

I_T_IOT_B仅仅占用1个块。HEADER_BLOCK=970,根节点=971.

SQL> alter system dump datafile 4 block 971 ;

Block header dump:  0x010003cb
 Object id on Block? Y
 seg/obj: 0x145ff  csc: 0x00.63b560  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10003c8 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.0063b560
Leaf block dump
===============
header address 182925401700=0x2a97341e64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 9
kdxcofbo 54=0x36
kdxcofeo 7870=0x1ebe
kdxcoavs 7816
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8018] flag: K-----, lock: 0, len=14
col 0; len 1; (1):  61
col 1; len 1; (1):  31
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bd
row#1[8003] flag: K-----, lock: 0, len=15
col 0; len 1; (1):  62
col 1; len 2; (2):  32 32
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bd
row#2[7987] flag: K-----, lock: 0, len=16
col 0; len 1; (1):  63
col 1; len 3; (3):  33 33 33
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bd
row#3[7970] flag: K-----, lock: 0, len=17
col 0; len 1; (1):  64
col 1; len 4; (4):  34 34 34 34
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bd
row#4[7952] flag: K-----, lock: 0, len=18
col 0; len 1; (1):  65
col 1; len 5; (5):  35 35 35 35 35
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bd
row#5[7933] flag: K-----, lock: 0, len=19
col 0; len 1; (1):  66
col 1; len 6; (6):  36 36 36 36 36 36
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 bd
row#6[7913] flag: K-----, lock: 0, len=20
col 0; len 1; (1):  67
col 1; len 7; (7):  37 37 37 37 37 37 37
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 be
row#7[7892] flag: K-----, lock: 0, len=21
col 0; len 1; (1):  68
col 1; len 8; (8):  38 38 38 38 38 38 38 38
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 be
row#8[7870] flag: K-----, lock: 0, len=22
col 0; len 1; (1):  69
col 1; len 9; (9):  39 39 39 39 39 39 39 39 39
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 be
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 971 maxblk 971


--可以发现索引中的数据块信息指向了正确的位置。
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 03 be

SQL> set autotrace traceonly
SQL> select substr(vc,1,20) from t_iot where b='a';
Execution Plan
----------------------------------------------------------
Plan hash value: 1095339046

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |  1003 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| T_IOT_PK  |     1 |  1003 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| I_T_IOT_B |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"='a')
   2 - access("B"='a')

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

--可以发现逻辑读又变为3个.物理猜测是正确的。

8.总结:
1.IOT的第2索引包含主键信息以及对应信息的块信息。
2.当IOT索引分裂时,第2索引中记录的块信息不会发生变化。这样在使用第2索引探查IOT表时,物理猜就会失败。
3.如果物理猜失败很多,会导致逻辑读增加,可以通过重建第2索引来解决这个问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-717272/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2351
  • 访问量
    6092951