'596849' reverse key index is ? （转载 asktom ）

本文小实验来源 ASKTOM 网站，感谢 http://asktom.oracle.com

doudou@TEST> create table t (x number, y varchar2(10));

doudou@TEST> insert into t values(596849 ,'596849');

doudou@TEST> create index t1_idx on t(x);

doudou@TEST> create index t2_idx on t(y);

dump the blocks

t1_idx

row#0[8018] flag: ------, lock: 0, len=14

col 0; len 4; (4):  c3 3c 45 32

col 1; len 6; (6):  01 80 00 0c 00 00

t2_idx

row#0[8016] flag: ------, lock: 0, len=16

col 0; len 6; (6):  35 39 36 38 34 39

col 1; len 6; (6):  01 80 00 0c 00 00

select dump ( 596849 , 16 ),

chr(to_number( '35' , 'xx' ))||

chr(to_number( '39' , 'xx' ))||

chr(to_number( '36' , 'xx' ))||

chr(to_number( '38' , 'xx' ))||

chr(to_number( '34' , 'xx' ))||

chr(to_number( '39' , 'xx' )) as "chr(to)" from dual;

DUMP(596849,16)                                  chr(to)

------------------------------------------------ ------------------------------------------------

Typ=2 Len=4: c3,3c,45,32                         596849

【 '596849' index values is 596849 。

( 思考： index values 与 index columns values 关系？ ) 】

Alter index t1_idx rebuild reverse;

Alter index t2_idx rebuild reverse;

reverse key index

dump the blocks

t1_idx

row#0[8018] flag: ------, lock: 0, len=14

col 0; len 4; (4):  32 45 3c c3

col 1; len 6; (6):  01 80 00 0c 00 00

t2_idx

row#0[8016] flag: ------, lock: 0, len=16

col 0; len 6; (6):  39 34 38 36 39 35

col 1; len 6; (6):  01 80 00 0c 00 00

select dump ( 596849 , 16 ),

chr(to_number( '39' , 'xx' ))||

chr(to_number( '34' , 'xx' ))||

chr(to_number( '38' , 'xx' ))||

chr(to_number( '36' , 'xx' ))||

chr(to_number( '39' , 'xx' ))||

chr(to_number( '35' , 'xx' )) as "chr(to)" from dual;

DUMP(596849,16)                                  chr(to)

------------------------------------------------ ------------------------------------------------

Typ=2 Len=4: c3,3c,45,32                         948695

【 '596849' reverse key index values is 948695 。

( 思考： reverse key index values 与 index columns values 关系？ ) 】

总结：

         反键索引值是与索引的数据值相似、相反的

         非反键索引值是与索引的数据值相似、相对应的

附表

实验操作

1、   取索引的 blocks 并 dump

1-1 、根据索引名称取到相应的索引对象 ID

doudou@TEST> select object_id from dba_objects where object_name='T1_IDX';

 OBJECT_ID

----------

     55558

1-2 、根据索引对象 ID 并使用 tree dump 查看索引树结构

sys@TEST> alter session set events 'immediate trace name TREEDUMP level 55558';

Session altered.

[root@dg-pp ~]# more /opt/oracle/admin/test/udump/test_ora_15629.trc

----- begin tree dump

leaf: 0x1800014 25165844 (0: nrow: 1 rrow: 1)

----- end tree dump

         1-3 、使用 DBMS_UTILITY 包，由 Data block address 找到索引所在的文件和块

sys@TEST> select dbms_utility.data_block_address_file(25165844) "file",dbms_utility.data_block_address_block(25165844) "block"  from dual;

      file      block

---------- ----------

         6         20

         1-4 、 dump 索引所在块

sys@TEST> alter system dump datafile 6 block 20;

System altered.

         1-5 、查看 dump 后的 trc 文件

t1_idx

row#0[8018] flag: ------, lock: 0, len=14

col 0; len 4; (4):  c3 3c 45 32

col 1; len 6; (6):  01 80 00 0c 00 00

同样原理取 t2_idx

row#0[8016] flag: ------, lock: 0, len=16

col 0; len 6; (6):  35 39 36 38 34 39

col 1; len 6; (6):  01 80 00 0c 00 00

2、   删除 t1_idx 和 t2_idx 重新创建反键索引 t1_idx 和 t2_idex ，然后查看 dump 后的 trc 文件

t1_idx

row#0[8018] flag: ------, lock: 0, len=14

col 0; len 4; (4):  32 45 3c c3

col 1; len 6; (6):  01 80 00 0c 00 00

t2_idx

row#0[8016] flag: ------, lock: 0, len=16

col 0; len 6; (6):  39 34 38 36 39 35

col 1; len 6; (6):  01 80 00 0c 00 00

3 、 Get trc name （ SQL ）

         2-1 、

SELECT     a.VALUE
        ||  b.symbol
        ||  c.instance_name
        ||   '_ora_'
        ||  d.spid
        ||   '.trc'  trace_file
   FROM  ( SELECT  VALUE
           FROM  v$parameter
          WHERE  NAME  =   'user_dump_dest' ) a,
       ( SELECT  SUBSTR (VALUE,  - 6 ,  1 ) symbol
           FROM  v$parameter
          WHERE  NAME  =   'user_dump_dest' ) b,
       ( SELECT  instance_name
           FROM  v$instance) c,
       ( SELECT  spid
           FROM  v$session s, v$process p, v$mystat m
          WHERE  s.paddr  =  p.addr  AND  s.SID  =  m.SID  AND  m.statistic#  =   0 ) d
/

TRACE_FILE

----------------------------------------------------------------------------------------------------

/opt/oracle/admin/test/udump/test_ora_15629.trc

转载 http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:474483191697