查看反向键索引存储结构

环境:

OS:Red Hat Linux As5

DB:10.2.0.4

 

  反向键索引在存储索引的时候是反向存储的,在主键是顺序插入的情况下创建反向索引,这可以增加平衡i/o的能力,但基于索引范围扫描的查询在反向键索引上不适用.

 

1.创建表并创建索引
Create Table tb_Rev_index_test
(
 Id Number,
 Name Varchar2(10)
);

Insert Into tb_Rev_index_test Values (1,'abc');
Insert Into tb_Rev_index_test Values (2,'def');
Insert Into tb_Rev_index_test Values (3,'fij');
Insert Into tb_Rev_index_test Values (4,'klm');

 

create index index_tb_Rev_index_test on tb_Rev_index_test(Name) Reverse;

 

2.dump索引树
Select object_id
 From dba_objects tt
Where tt.object_name = 'INDEX_TB_REV_INDEX_TEST'
------------------
51502

SQL>alter session set events 'immediate trace name treedump level 51502';

 

查看trc文件部分内容如下:

 

*** 2012-07-25 09:52:40.643
*** ACTION NAME:() 2012-07-25 09:52:40.643
*** MODULE NAME:(SQL*Plus) 2012-07-25 09:52:40.643
*** SERVICE NAME:(SYS$USERS) 2012-07-25 09:52:40.643
*** SESSION ID:(140.31) 2012-07-25 09:52:40.643
----- begin tree dump
leaf: 0x140007c 20971644 (0: nrow: 4 rrow: 4)
----- end tree dump

 

3.dump索引块
select dbms_utility.data_block_address_file(20971644) file_no,
  dbms_utility.data_block_address_block(20971644) block_no from dual;

---------------------- 
FILE_NO   BLOCK_NO
5         124

SQL>alter system dump datafile 5 block 124;

 

查看trc文件部分内容如下:

 Object id on Block? Y
 seg/obj: 0xc92e  csc: 0x00.a3ed5  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1400079 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.000a3ed5
 
Leaf block dump
===============
header address 186065508=0xb172264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7984=0x1f30
kdxcoavs 7940
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8023] flag: ------, lock: 0, len=13
col 0; len 3; (3):  63 62 61         -- cba
col 1; len 6; (6):  01 40 00 76 00 00
row#1[8010] flag: ------, lock: 0, len=13
col 0; len 3; (3):  66 65 64         -- fed
col 1; len 6; (6):  01 40 00 76 00 01
row#2[7997] flag: ------, lock: 0, len=13
col 0; len 3; (3):  6a 69 66        -- jif
col 1; len 6; (6):  01 40 00 76 00 02
row#3[7984] flag: ------, lock: 0, len=13
col 0; len 3; (3):  6d 6c 6b        -- mlk
col 1; len 6; (6):  01 40 00 76 00 03
----- end of leaf block dump -----
End dump data blocks tsn: 6 file#: 5 minblk 124 maxblk 124

 

从以上索引块的记录可以看出,索引字段的值全是反向存储的.
即:
表存储的记录  索引存储的记录
abc           cba
def           fed
fij           jif
klm           mlk

 

4.查询分析

下面我们看下如下查询的执行计划
Explain Plan For
Select *
 From tb_Rev_index_test tt
Where tt.name = 'abc';

Select * From table(dbms_xplan.display())

PLAN_TABLE_OUTPUT
Plan hash value: 2949461915
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_REV_INDEX_TEST       |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_TB_REV_INDEX_TEST |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TT"."NAME"='abc')

 

从以上计划输出可以看出,走了索引,索引存储的是'cba',但为什么查询'abc'会走索引呢?这是Oracle执行的时候将'abc'做了反向转换,通过转换后的值'cba',直接去索引上找到相应的索引键值.下面我们再看一个范围的查询:

Explain Plan For
Select *
 From tb_Rev_index_test tt
Where tt.name >= 'abc' And tt.name <= 'klm';

Select * From table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
Plan hash value: 1024913535
 
---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     4 |    28 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_REV_INDEX_TEST |     4 |    28 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("TT"."NAME">='abc' AND "TT"."NAME"<='klm')

 

这里走了全表扫描,为什么呢?要是按照普通索引的话肯定会走index range扫描的.原因是反向键存储的索引键值是不连续的,即'abc'和'klm'之间的值是不连续存储的,这导致Oracle不能使用范围扫描去找'abc'和'klm'之间的值,也找不到,所以采用了全表扫描.
所以索引范围扫描在反向键索引上是不适用的.