'596849' reverse key index is
?
(转载
asktom
)
本文小实验来源
ASKTOM
网站,感谢
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
转载