环境:

OS:Red Hat Linux As 5

DB:10.2.0.4

今天有个刚接触oracle的朋友问我,两个字段的组合索引,其中一个字段存储的是空值(null),那这个空值会不会存储到索引中,我回答是会的,下面通过一个例子说明.

1.建表和索引

SQL> create table tb_index_test(c1 number,c2 number);

Table created.

SQL>
SQL> create index idx_tb_index_test on tb_index_test(c1,c2);

Index created.

SQL> insert into tb_index_test values(null,1);

1 row created.

SQL>
SQL> insert into tb_index_test values(2,null);

1 row created.

SQL> commit;

2.dump索引树

SQL> select object_id from dba_objects t
2 where t.owner='HXL'
3 and t.object_name ='IDX_TB_INDEX_TEST';

OBJECT_ID
----------
70339

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 70339';
trace文件内容下:

----- begin tree dump
leaf: 0x10000fb 16777467 (0: nrow: 2 rrow: 2)
----- end tree dump

3.dump索引树数据块

SQL> select dbms_utility.data_block_address_file(16777467) file_no,
2 dbms_utility.data_block_address_block(16777467) block_no
3 from dual;

FILE_NO BLOCK_NO
---------- ----------
4 251

SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK 251;

得到的trace文件内容如下:

row#0[8010] flag: ------, lock: 2, len=13
col 0; len 2; (2): c1 03 --这里是索引列排序后第1行列1的值utl_raw.cast_to_number('c103')
col 1; NULL --这里是第一行列二的值,是空值
col 2; len 6; (6): 01 00 00 f7 00 01 --treedump rowid
row#1[8023] flag: ------, lock: 2, len=13
col 0; NULL
col 1; len 2; (2): c1 02
col 2; len 6; (6): 01 00 00 f7 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 251 maxblk 251

按照索引字段排序输出表数据如下:

SQL> select c1, c2 from hxl.TB_INDEX_TEST order by c1, c2;

        C1         C2
---------- ----------
         2
                    1

下面说明如何通过treedump出来得到的rowid得到实际的ROWID,我们知道treedump出来的rowid是经过压缩了的,即该rowid中不包含有object_id.

以这个rowid为例子说明:

col 2; len 6; (6): 01 00 00 f7 00 01 --treedump rowid

01 00 00 f7 00 01 转化为二进制

00000001 00000000 00000000 11110111 00000000 00000001

前10bit是数据文件号file#=00000001 00,十进制为4

接下来的22bit是块号block#=000000 00000000 11110111,十进制为247

最后的8bit是行号row#=0000001,十进制为1

下面通过dbms_rowid.rowid_create构建rowid,因为缺少object#,所以我们必须找到索引对应表的object_id.

SQL> select object_id from dba_objects t
2 where wner='HXL'
3 and object_name='TB_INDEX_TEST'; -- 索引对应表的object_id

OBJECT_ID
----------
70338

SQL> select
2 dbms_rowid.rowid_create(rowid_type => 1,
3                          object_number => 70338,
4                          relative_fno => 4,
5                          block_number => 247,
6                          row_number => 1
7                          ) rid
8 from dual;

RID
------------------
AAARLCAAEAAAAD3AAB

这个时候我们获取到了rowid,下面验证下:

SQL> select c1, c2 from hxl.TB_INDEX_TEST
2 where rowid='AAARLCAAEAAAAD3AAB';

C1 C2
---------- ----------
2

通过rowid我们找到了行记录数据.

说明:

非唯一性索引leaf node上存储的索引条目是索引key value+rowid的组合,因为索引key value是无法定位唯一行,所以索引条目中需要存储rowid才能定位唯一行.

-- The End --

ITPub博客

转载-treedump索引读取索引存储的数据值--非唯一性索引

最新文章