環境:

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 --

轉載-treedump索引讀取索引儲存的資料值--非唯一性索引

相關文章