高效的SQL(index values与index column values关系？)

'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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-751213/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-751213/