查看函数索引存储结构

环境:

OS:Red Hat Linux As5

DB:10.2.0.4

 

  通常我们在创建了某个索引,在这个索引上使用了函数的话,那么就不会走索引,针对这种情况我们可以创建基于函数的索引,创建了基于函数的索引,在索引上会存储通过函数处理过的值.

 

1.创建表并写入数据
SQL> alter session set nls_language=american;

Session altered.

SQL> Create Table tb_fun_index_test
  2  (
  3    Id Number,
  4    createtime Date Default Sysdate
  5  );

Table created.

 

SQL> Insert Into tb_fun_index_test
  2  Select Rownum,Sysdate From dual Connect By Rownum<=1000;

1000 rows created.

SQL> Create Index idx_tb_fun_index_test
  2  On tb_fun_index_test(to_char(createtime,'YYYYMMDD HH24:MI:SS'));

Index created.

 

2.dump索引tree
SQL> Select object_id From Dba_Objects t
  2  Where object_name='IDX_TB_FUN_INDEX_TEST';

 OBJECT_ID
----------
     51423

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

 

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

 

*** 2012-07-25 09:13:19.708
*** ACTION NAME:() 2012-07-25 09:13:19.693
*** MODULE NAME:(SQL*Plus) 2012-07-25 09:13:19.693
*** SERVICE NAME:(SYS$USERS) 2012-07-25 09:13:19.693
*** SESSION ID:(136.85) 2012-07-25 09:13:19.693
----- begin tree dump
branch: 0x1400054 20971604 (0: nrow: 5, level: 1)
   leaf: 0x1400055 20971605 (-1: nrow: 247 rrow: 247)
   leaf: 0x1400056 20971606 (0: nrow: 247 rrow: 247)
   leaf: 0x1400057 20971607 (1: nrow: 247 rrow: 247)
   leaf: 0x1400058 20971608 (2: nrow: 247 rrow: 247)
   leaf: 0x1400059 20971609 (3: nrow: 12 rrow: 12)
----- end tree dump

 

3.查看索引块存储信息
SQL> select dbms_utility.data_block_address_file(20971606) file_no,
  2    dbms_utility.data_block_address_block(20971606) block_no from dual;

   FILE_NO   BLOCK_NO
---------- ----------
         5         86

SQL>alter system dump datafile 5 block 86;

 

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

 

Block header dump:  0x01400056
 Object id on Block? Y
 seg/obj: 0xc8df  csc: 0x00.a35c7  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1400051 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.000a35c7
 
Leaf block dump
===============
header address 198845028=0xbda2264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 247
kdxcofbo 530=0x212
kdxcofeo 1367=0x557
kdxcoavs 837
kdxlespl 0
kdxlende 0
kdxlenxt 20971607=0x1400057
kdxleprv 20971605=0x1400055
kdxledsz 0
kdxlebksz 8036
row#0[8009] flag: ------, lock: 0, len=27
col 0; len 17; (17):  32 30 31 32 30 37 32 35 20 30 39 3a 30 39 3a 33 35
col 1; len 6; (6):  01 40 00 4f 00 6b
row#1[7982] flag: ------, lock: 0, len=27
col 0; len 17; (17):  32 30 31 32 30 37 32 35 20 30 39 3a 30 39 3a 33 35
col 1; len 6; (6):  01 40 00 4f 00 6c
row#2[7955] flag: ------, lock: 0, len=27
col 0; len 17; (17):  32 30 31 32 30 37 32 35 20 30 39 3a 30 39 3a 33 35
col 1; len 6; (6):  01 40 00 4f 00 6d
row#3[7928] flag: ------, lock: 0, len=27
col 0; len 17; (17):  32 30 31 32 30 37 32 35 20 30 39 3a 30 39 3a 33 35
col 1; len 6; (6):  01 40 00 4f 00 6e
row#4[7901] flag: ------, lock: 0, len=27
col 0; len 17; (17):  32 30 31 32 30 37 32 35 20 30 39 3a 30 39 3a 33 35
col 1; len 6; (6):  01 40 00 4f 00 6f
row#5[7874] flag: ------, lock: 0, len=27

 

从以上索引块的部分输出可以看出,索引上记录了to_char(createtime)后的值,即utl_raw.cast_to_varchar2('32303132303732352030393a30393a3335')=20120725 09:09:35.

 

-- The End --