环境:
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';
*** 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;
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 --
阅读(815) | 评论(0) | 转发(0) |