高效的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/，如需轉載，請註明出處，否則將追究法律責任。