索引读取顺序

目的：考察索引读取数据的顺序

1.创建测试表注意字段长度
SQL> create table test1104 (n1 char(2000),n2 char(2000),n3 char(2000),n4 char(1000));

Table created.

2.修改字段属性 not null

SQL> alter table test1104 modify (n1 not null);

Table altered.

3.插入测试数据
SQL> insert into test1104 values(1,1,1,1);
SQL> insert into test1104 values(2,2,2,2);

2 row created.

4.创建索引
SQL> create index ind_n1 on test1104(n1);

Index created.

5.取出数据的rowid
SQL> select rowid,n1 from test1104;

ROWID N1
------------------------------------
AAAT5/AAEAAAAQEAAA 1
AAAT5/AAEAAAAQFAAA 2

虽然仅仅两条数据，但还是占用两个数据块

6.根据该rowid 取出数据分布的位置
SQL> select get_rowid('AAAT5/AAEAAAAQEAAA') from dual;

GET_ROWID('AAAT5/AAEAAAAQEAAA')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Object# is :81535
Relative_fno is :4
Block number is :1028
Row number is :0

SQL> select get_rowid('AAAT5/AAEAAAAQFAAA') from dual;

GET_ROWID('AAAT5/AAEAAAAQFAAA')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Object# is :81535
Relative_fno is :4
Block number is :1029
Row number is :0

7.dump该索引
SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 81536';

Session altered.

索引dump信息如下：

----- begin tree dump
leaf: 0x10005b3 16778675 (0: nrow: 2 rrow: 2)
----- end tree dump

8.检查该地址对应的数据块

SQL> select dbms_utility.data_block_address_file(16778675) "file",dbms_utility.data_block_address_block(16778675) "block" from dual;

file block
---------- ----------
4 1459

9.dump该数据块
SQL> alter system dump datafile 4 block 1459;

System altered.

dump信息如下：