索引儲存小記

本文簡單討論非唯一性與唯一性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、如果可以建立唯一性索引，就不要建普通索引。唯一性索引比一般索引佔用空間要少。