位图索引–反转索引
准备环境和测试数据
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 --Z小rowid col 2; len 6; (6): 01 81 8c 15 01 7f --Z大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 |
位图索引需要注意应用场景和DML操作的影响范围。