'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
selectdump (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
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
selectdump (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