index branch and leaf block structure(轉)

aaqwsh發表於2011-03-12
IndexBloCStruct

Index Range Scans

An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns.
Multiple rows with identical values are sorted in ascending order by rowid。

我以前也看過這段話，但是也就簡單的過去了，沒有細想過，所以當小霸王問為什麼相同值會按照rowid排序的時候我也沒給出特別明確的答覆。做了以下實驗，應該可以給小霸王
一個滿意的答覆了。

在考慮這個問題的時候我們首先要回顧的知識點是unique index和non-unique index它的構造是不一樣
的。對於unique index，它的branch block裡面只儲存key value和leaf block的address，因為根據這
2個值就可以定位當新值插入時會選擇哪個leaf block進行插入，leaf block裡面的值也沒必要按照rowid的順序排列了，只需要按照key value排序就行了。但是如果是non-unique index,branch block
裡面必須儲存key value,leaf block的address,和rowid。在leaf block裡面如果key value相同的話
要按照rowid做升序排列，我個人覺得這樣做會有2個好處，一個是可以提高相同一個leaf block內相同key value能儘量關聯到相同的data block。第2個好處是當插入新的相同key value時能很容易定位插入到哪個block。為了能做到這些，non-unique index必須在branch block裡面放入rowid。下面來看一下實驗。
首先我們需要建立一張測試表

SQL> create table test(a number);

Table created.

插入2000條資料，值為1到2000

SQL> begin                     
  2  for i in 1..2000 loop    
  3  insert into test values(i);
  4  end loop;                 
  5  commit;                   
  6  end;                      
  7  /                         

PL/SQL procedure successfully completed.
再插入500條值為500的資料，這樣的話這個表存在501條值為500的記錄

SQL> begin                     
  2  for i in 1..500 loop    
  3  insert into test values(500);
  4  end loop;                 
  5  commit;                   
  6  end;                      
  7  /                         

PL/SQL procedure successfully completed.                        
SQL>

建立一個non-unique index

SQL> create index ind_test on test(a);

Index created.

檢視non-unique index的object_id

SQL> select object_id from dba_objects where object_name=’IND_TEST’;

 OBJECT_ID
———-
     54032

dump non-unique index的層級結構

SQL>  ALTER SESSION SET EVENTS ‘immediate trace name TREEDUMP level 54032′;

Session altered.

—– begin tree dump
branch: 0×1000044 16777284 (0: nrow: 6, level: 1)
   leaf: 0×1000045 16777285 (-1: nrow: 279 rrow: 279)
   leaf: 0×100004d 16777293 (0: nrow: 275 rrow: 275)
   leaf: 0×100004e 16777294 (1: nrow: 486 rrow: 486)
   leaf: 0×1000046 16777286 (2: nrow: 533 rrow: 533)
   leaf: 0×1000048 16777288 (3: nrow: 533 rrow: 533)
   leaf: 0×1000047 16777287 (4: nrow: 394 rrow: 394)
—– end tree dump

看一下branch block的所在檔案和塊號，準備dump

SQL> select dbms_utility.data_block_address_file(16777284) “file”,
  2  dbms_utility.data_block_address_block(16777284) “block” from dual;

      file      block
———- ———-
         4         68

dump branch block的實際內容

SQL> alter system dump datafile 4 block 68;

System altered.
分析一下branch block，可以看到leaf block是從kdxbrlmc 16777285這個地址開始
dba: 16777294這個塊與其他塊有點不同，它的col1儲存的就是rowid 01 00 00 3e 00 4a
，這裡的col0 c2 06轉換成10進位制的值就是500，為什麼其他leaf block的col1都被置為
TERM省略掉了呢，因為只有16777293，16777294這兩個塊儲存了500這個重複值，所以當繼續
插入500時，oracle可以根據這邊的col1來定位是插入到16777293還是插入到16777294。插入
其他值並不受這個影響，所以oracle對這個地方做了優化，並不是所有non-unique branch block
都是需要記錄rowid的。

kdxbrlmc 16777285=0×1000045
kdxbrsno 1
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8020] dba: 16777293=0×100004d
col 0; len 3; (3):  c2 03 51
col 1; TERM
row#1[8006] dba: 16777294=0×100004e
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 00 3e 00 4a
row#2[8047] dba: 16777286=0×1000046
col 0; len 3; (3):  c2 06 2a
col 1; TERM
row#3[8038] dba: 16777288=0×1000048
col 0; len 3; (3):  c2 0b 4b
col 1; TERM
row#4[8029] dba: 16777287=0×1000047
col 0; len 3; (3):  c2 11 08   

再來看一下具體的leaf block,看看裡面是不是按rowid的順序排列的

SQL> select dbms_utility.data_block_address_file(16777294) “file”,                         
  2  dbms_utility.data_block_address_block(16777294) “block” from dual;

      file      block
———- ———-
         4         78

SQL> alter system dump datafile 4 block 78;

System altered.
可以看到，相同的key value它的順序是根據rowid的升序排列的

kdxlenxt 16777286=0×1000046
kdxleprv 16777293=0×100004d
kdxledsz 0
kdxlebksz 8032
row#0[4656] flag: —-S-, lock: 2, len=12
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 00 3e 00 4a
row#1[4668] flag: —-S-, lock: 2, len=12
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 00 3e 00 4b
row#2[4680] flag: —-S-, lock: 2, len=12
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 00 3e 00 4c
row#3[4692] flag: —-S-, lock: 2, len=12
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 00 3e 00 4d
row#4[4704] flag: —-S-, lock: 2, len=12
col 0; len 2; (2):  c2 06
col 1; len 6; (6):  01 00 00 3e 00 4e
row#5[4716] flag: —-S-, lock: 2, len=12
再來看一下unique index是什麼樣的

SQL> drop index ind_test;

Index dropped.
SQL> truncate table test;

Table truncated.

SQL> begin                      
  2  for i in 1..2000 loop      
  3  insert into test values(i);
  4  end loop;                  
  5  commit;                    
  6  end;                       
  7  / 

PL/SQL procedure successfully completed.

SQL> create unique index ind_test on test(a);

Index created.
      
      
SQL> select object_id from dba_objects where object_name=’IND_TEST’;

 OBJECT_ID
———-
     54049

SQL> ALTER SESSION SET EVENTS ‘immediate trace name TREEDUMP level 54049′;

Session altered.

—– begin tree dump
branch: 0×1000044 16777284 (0: nrow: 4, level: 1)
   leaf: 0×1000045 16777285 (-1: nrow: 520 rrow: 520)
   leaf: 0×1000046 16777286 (0: nrow: 513 rrow: 513)
   leaf: 0×1000047 16777287 (1: nrow: 513 rrow: 513)
   leaf: 0×1000048 16777288 (2: nrow: 454 rrow: 454)
—– end tree dump

SQL> select dbms_utility.data_block_address_file(16777284) “file”,                         
  2  dbms_utility.data_block_address_block(16777284) “block” from dual;

      file      block
———- ———-
         4         68

SQL> alter system dump datafile 4 block 68;

System altered.
注意，unique index裡面並沒有col1這個值，也就說明unique index不保留rowid

kdxbrlmc 16777285=0×1000045
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8048] dba: 16777286=0×1000046
col 0; len 3; (3):  c2 06 16
row#1[8040] dba: 16777287=0×1000047
col 0; len 3; (3):  c2 0b 23
row#2[8032] dba: 16777288=0×1000048
col 0; len 3; (3):  c2 10 30

SQL> select dbms_utility.data_block_address_file(16777286) “file”,                         
  2  dbms_utility.data_block_address_block(16777286) “block” from dual;

      file      block
———- ———-
         4         70

SQL> alter system dump datafile 4 block 70;

System altered.
可以看到，unique index是根據key value的值做升序排列的

kdxlenxt 16777287=0×1000047
kdxleprv 16777285=0×1000045
kdxledsz 6
kdxlebksz 8032
row#0[8020] flag: ——, lock: 0, len=12, data:(6):  01 00 00 3c 02 08
col 0; len 3; (3):  c2 06 16
row#1[8008] flag: ——, lock: 0, len=12, data:(6):  01 00 00 3c 02 09
col 0; len 3; (3):  c2 06 17
row#2[7996] flag: ——, lock: 0, len=12, data:(6):  01 00 00 3c 02 0a
col 0; len 3; (3):  c2 06 18
row#3[7984] flag: ——, lock: 0, len=12, data:(6):  01 00 00 3c 02 0b
col 0; len 3; (3):  c2 06 19
row#4[7972] flag: ——, lock: 0, len=12, data:(6):  01 00 00 3c 02 0c
col 0; len 3; (3):  c2 06 1a

Posted in Oracle at 4:49 下午 由 wanghai

本文簡單討論非唯一性與唯一性B*TREE索引在儲存上的差異，以及全域性索引及本地索引在儲存上的差異。


一、唯一性索引與非唯一性索引
--建立表
SQL> create table k1(id int);

Table created

SQL> create table k2(id int);

Table created

--建立索引
SQL> create index idx_k1 on k1(id);

Index created

SQL> create unique index idx_k1 on k2(id);

Index created

--插入資料
SQL> insert into k1 values(1);

1 row inserted

SQL> insert into k2 values(1);

1 row inserted

SQL> commit;

Commit complete

--checkpoint，使資料重新整理到硬碟上
SQL> alter system checkpoint;

--檢視索引位置
SQL> select segment_name,file_id,block_id from dba_extents where segment_name in ('IDX_K1','IDX_K2');

SEGMENT_NAME FILE_ID BLOCK_ID
-------------------------------- ---------- ----------
IDX_K1 8 5457
IDX_K2 8 5465

--dump索引block
--一般來說，每一個segment的第一個block是segment header，它不存放使用者資料，故我們一般從第二個block開始dump：
SQL> alter system dump datafile 8 block 5458;

System altered

SQL> alter system dump datafile 8 block 5466;

System altered

==============================================================================
普通索引IDX_K1：
row#0[8024] flag: -----, lock: 2
col 0; len 2; (2): c1 02 --這是ID列
col 1; len 6; (6): 02 00 15 42 00 00 --這列是rowid，它以一個列的形式存在在索引結構中

--注意：不要以為上面是兩列的組合索引，最後一列其實是rowid。（如果是兩列的組合非唯一性索引，這裡會顯示有三列的資訊）
--實際上，非唯一性索引中，dump 索引塊出來的資訊的最後一列總是rowid。

唯一性索引IDX_K2：
row#0[8025] flag: -----, lock: 2, data:(6): 02 00 15 4a 00 00
^^^^^^^^^^^^^^^^^ 這是rowid，放在row header上
col 0; len 2; (2): c1 02 --這是索引當前鍵值

===============================================================================
從上面結果可以看出，每一個非唯一性索引entry比唯一性索引多出rowid部分。


二、全域性索引與本地索引

--建立表和索引
SQL> CREATE TABLE PAR1(ID INT) PARTITION BY RANGE(ID)
2 (PARTITION P1 VALUES LESS THAN(10))
3 ;

Table created


SQL> CREATE TABLE PAR2(ID INT) PARTITION BY RANGE(ID)
2 (PARTITION P1 VALUES LESS THAN(10))

Table created

SQL> CREATE TABLE PAR3(ID INT) PARTITION BY RANGE(ID)
2 (PARTITION P1 VALUES LESS THAN(10))
3 ;

Table created

SQL> CREATE UNIQUE INDEX GIDX_PAR1 ON PAR1(ID);

Index created

SQL> CREATE INDEX GIDX_PAR2 ON PAR2(ID);

Index created

SQL> CREATE INDEX LIDX_PAR3 ON PAR3(ID) LOCAL;

Index created

--插入資料
SQL> INSERT INTO PAR1 VALUES(1);

1 row inserted

SQL> INSERT INTO PAR2 VALUES(1);

1 row inserted

SQL> INSERT INTO PAR3 VALUES(1);

1 row inserted

SQL> COMMIT;

Commit complete

--重新整理資料到硬碟
SQL> ALTER SYSTEM CHECKPOINT;

System altered

--索引的儲存位置
SQL> select segment_name,file_id,block_id from dba_extents where segment_name in ('GIDX_PAR1','GIDX_PAR2','LIDX_PAR3');

SEGMENT_NAME FILE_ID BLOCK_ID
-------------------------------- ---------- ----------
GIDX_PAR2 9 1553
GIDX_PAR1 9 1561
LIDX_PAR3 9 1577


--dump索引
SQL> ALTER SYSTEM DUMP DATAFILE 9 BLOCK 1562;

System altered

SQL> ALTER SYSTEM DUMP DATAFILE 9 BLOCK 1554;

System altered

SQL> ALTER SYSTEM DUMP DATAFILE 9 BLOCK 1578;

System altered

=============================索引的儲存情況==============================
索引GIDX_PAR1:全域性唯一性索引
row#0[8021] flag: -----, lock: 2, data:(10): 00 00 1f 5a 02 00 15 82 00 00
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^rowid，注意長度是10
col 0; len 2; (2): c1 02

索引GIDX_PAR2:全域性非唯一性索引
row#0[8020] flag: -----, lock: 2
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 00 1f 5c 02 40 06 0a 00 00
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^rowid，長度是10

索引LIDX_PAR3:本地索引
row#0[8024] flag: -----, lock: 2
col 0; len 2; (2): c1 02
col 1; len 6; (6): 02 40 06 22 00 00
^^^^^^^^^^^^^^^^^rowid，長度是6
========================================================================

總結：
1、非唯一性索引中，每一個index entry都包含有當前的鍵值及rowid兩個部分。
dump block顯示的index entry資訊中的最後一列就是rowid。
2、唯一性索引中，index entry只包含當前的鍵值資訊。rowid會在row header存放。
3、普通索引每一個entry的長度是6個位元組
全域性索引的每一個entry的長度是10個位元組
4、全域性索引比普通索引每一個entry多存放了OBJECT_ID的資訊。
5、如果可以建立唯一性索引，就不要建普通索引。唯一性索引比一般索引佔用空間要少。
 
作者 space6212 00:40 <!-- comment these out if you want to see an example of custom fields, but remember to name the fields in the same way they are named here: 'imfeeling' (livejournal.com style), 'listening' and 'new_field'

: ?

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-689267/，如需轉載，請註明出處，否則將追究法律責任。

相關文章