oracle index column,Oracle 优化之SQL(index values与index column values关系？)

'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

转载