重庆ORACLE 窥视索引内部结构-重庆赛烽达科技有限公司

位图索引–反转索引

准备环境和测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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';

位图索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create table tb_b (id number,name varchar2(100));
begin
for i in 1..10000
loop
insert into tb_b values(i,'a');
end loop;
end;
 
commit;
 
SQL> update tb_b set name='b' where id>5000;
 
create bitmap index idx_tb_b on tb_b(name);
 
exec sys.dbms_stats.gather_table_stats ( OwnName => 'ZFY',TabName => 'TB_B',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
 
col object_name for a12
select object_name,object_id from dba_objects where object_name in ('TB_B','IDX_TB_B');
 
OBJECT_NAME   OBJECT_ID
------------ ----------
IDX_TB_B         126042
TB_B             126041
 
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_B';
 
    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
---------- ----------- ------------- ----------------------- ----------------------- -----------------
         0           1             2                       1                       1                 2

注意这里的BLEVEL=0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
alter session set events 'immediate trace name treedump level 126042';
----- begin tree dump
*** 2018-03-03T00:29:56.446904+08:00 (TESTPDB(3))
leaf: 0x1818c0b 25267211 (0: row:2.2 avs:5775)
----- end tree dump
 
select to_number('18c0b','xxxxxxx') from dual;
 
SQL> alter system checkpoint;
 
System altered.
 
SQL> alter system dump datafile 13 block 101387;
 
System altered.

trace 信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
Block header dump:  0x01818c0b
 Object id on Block? Y
 seg/obj: 0x1ec5a  csc:  0x0000000002097235  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1818c08 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   0x0001.00c.00000e8b  0x00c00c8a.0489.14  --U-    2  fsc 0x02d1.02097239
Leaf block dump
===============
header address 139977719369828=0x7f4f1a3d5064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 642=0x282
kdxcoavs 5775
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[1410] flag: -----R-, lock: 2, len=747, rsl=721
col 0; len 1; (1):  61 ---a为61
col 1; len 6; (6):  00 00 00 00 00 00  --最小rowid
col 2; len 6; (6):  01 81 8c 15 01 7f  --最大rowid
col 3; len 727; (727): ---位图编码
 ff a1 ba be e0 08 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff
 ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff
 04 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff
 ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff
 ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff 04 ff ff ff ff
 ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff
 ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf
 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff
 ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff 04 ff ff ff ff ff ff ff ff cf
 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff
 ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff
 ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff
 ff ff ff ff ff ca ff ff 0f ff 04 ff ff ff ff ff ff ff ff cf ff ff ff ff ff
 ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff
 ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff
 ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 ca ff ff 0f ff dc 85 01 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff
 ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff
 0f ff 9b 02 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff
 ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff
 ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff 04 ff
 ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff 0f
row#1[642] flag: -------, lock: 2, len=749
col 0; len 1; (1):  62
col 1; len 6; (6):  00 00 00 00 00 00
col 2; len 6; (6):  01 81 8c 1e 00 67
col 3; len 729; (729): 
 ff a7 c4 bf e0 08 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff
 ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff
 04 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff
 ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff
 ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff 04 ff ff ff ff
 ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff
 ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf
 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff
 ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff 90 01 f0 ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
 ff ff cb ff ff ff 0f ff 04 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff
 ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff
 ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff
 ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff
 ff 0f ff 04 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff
 ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff
 ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff f8 02
 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff
 ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff
 ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff
 ff ff ff ff ff cf ff ff ff ff ff ff ff ff ca ff ff 0f ff 04 ff ff ff ff ff
 ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff
 ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff
 ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff
 cf ff ff ff ff ff ff ff ff ca ff ff 0f ff 04 ff ff ff ff ff ff ff ff cc ff
 ff ff ff 0f
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 6 file#: 13 minblk 101387 maxblk 101387