oracle查询倒叙,ORACLE 索引结构--倒叙索引：

本帖最后由 sunyunyi 于 2018-11-10 10:58 编辑

ORACLE 倒叙索引：

作者简介：

----------------------------------------------------

@ 孙显鹏，海天起点oracle技术专家，十年从业经验

@ 精通oracle内部原理，擅长调优和解决疑难问题

@ 致力于帮助客户解决生产中的问题，提高生产效率。

@ 爱好：书法，周易，中医。微信：sunyunyi_sun

@ 易曰：精义入神，以致用也！

@ 调优乃燮理阴阳何其难也！

-----------------------------------------------------

下面我们看看倒叙索引内部结构是什么样子，是不是索引键值就是倒叙排列呢？

答案肯定不是，因为oracle不可能为索引建立两个不同的结构。

create user zfy identified by zfy default tablespace test;

grant dba to zfy;

conn zfy/zfy

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

create table tb_ti as select rownum id,'wolf' col0,'wits' col1 from dual connect by level<=10000;

create index idx_tb_ti on tb_ti(id desc);

exec sys.dbms_stats.gather_table_stats ( OwnName => 'ZFY',TabName => 'TB_TI',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);

select object_name,object_id from dba_objects where object_name in ('TB_TI','IDX_TB_TI');

OBJECT_NAM  OBJECT_ID

---------- ----------

TB_TI         73553

IDX_TB_TI     73554

alter session set events 'immediate trace name treedump level 73554';

/u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_642.trc

----- begin tree dump

branch: 0x14000a3 20971683 (0: nrow: 23, level: 1)

leaf: 0x14000a4 20971684 (-1: row:449.449 avs:817)

leaf: 0x14000a5 20971685 (0: row:449.449 avs:816)

leaf: 0x14000a6 20971686 (1: row:449.449 avs:817)

leaf: 0x14000a7 20971687 (2: row:449.449 avs:816)

leaf: 0x14000a8 20971688 (3: row:449.449 avs:817)

leaf: 0x14000a9 20971689 (4: row:449.449 avs:816)

leaf: 0x14000aa 20971690 (5: row:449.449 avs:817)

leaf: 0x14000ab 20971691 (6: row:449.449 avs:816)

leaf: 0x14000ac 20971692 (7: row:449.449 avs:817)

leaf: 0x14000ad 20971693 (8: row:449.449 avs:816)

leaf: 0x14000ae 20971694 (9: row:449.449 avs:817)

leaf: 0x14000af 20971695 (10: row:449.449 avs:816)

leaf: 0x14000b1 20971697 (11: row:449.449 avs:817)

leaf: 0x14000b2 20971698 (12: row:449.449 avs:816)

leaf: 0x14000b3 20971699 (13: row:449.449 avs:817)

leaf: 0x14000b4 20971700 (14: row:449.449 avs:816)

leaf: 0x14000b5 20971701 (15: row:449.449 avs:817)

leaf: 0x14000b6 20971702 (16: row:449.449 avs:816)

leaf: 0x14000b7 20971703 (17: row:449.449 avs:817)

leaf: 0x14000b8 20971704 (18: row:449.449 avs:816)

leaf: 0x14000b9 20971705 (19: row:449.449 avs:817)

leaf: 0x14000ba 20971706 (20: row:449.449 avs:816)

leaf: 0x14000bb 20971707 (21: row:122.122 avs:6144)

----- end tree dump

首先分析枝节点

select to_number('000a3','xxxxxxx') from dual; --163

alter system dump datafile 5 block 163;

Block header dump:  0x014000a3

Object id on Block? Y

seg/obj: 0x11f52  csc:  0x0000000000169aa6  itc: 1  flg: E  typ: 2 - INDEX

brn: 0  bdba: 0x14000a0 ver: 0x01 opc: 0

inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn  0x0000000000169aa6

Branch block dump

=================

header address 140043605704780=0x7f5e715f004c

kdxcolev 1

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 22

kdxcofbo 72=0x48

kdxcofeo 7858=0x1eb2

kdxcoavs 7786

kdxbrlmc 20971684=0x14000a4

kdxbrsno 0

kdxbrbksz 8056

kdxbr2urrc 8

row#0[8047] dba: 20971685=0x14000a5

col 0; len 3; (3):  3d 9f cb

col 1; TERM

row#1[8038] dba: 20971686=0