簡述Oracle IOT（Index Organized Table）（中）

 

上篇中我們簡單介紹了一下IOT的基本知識和概念。本篇繼續來介紹IOT相關的內容。

 

4、IOT日常維護

 

相對於堆表heap結構，索引組織表最大的特點在於將資料行全部內容作為葉子節點儲存在索引結構中。IOT中只包括索引段（Index Segment）結構，沒有對應的資料表段（Table Segment）結構。

 

在日常運維工作中，我們經常需要對索引結構進行定期的重構rebuild操作，來消除索引無效節點（Dead Node）。那麼，IOT結構中，我們維護工作需要注意些什麼問題呢？

 

我們依然使用上篇的IOT資料表T_IOT和堆表T_HEAP來進行比對實驗。

 

 

SQL> select index_name from user_indexes where table_name='T_IOT';

 

INDEX_NAME

------------------------------

SYS_IOT_TOP_75124

 

 

資料表T_IOT對應的主鍵索引名稱為SYS_IOT_TOP_75124。該索引段大致空間為2M。

 

 

SQL> desc t_iot;

Name        Type          Nullable Default Comments

----------- ------------- -------- ------- --------

OBJECT_ID   NUMBER(10)                             

OBJECT_NAME VARCHAR2(100) Y                        

 

SQL> select count(*) from t_iot;

  COUNT(*)

----------

     72638

 

SQL> select segment_name, bytes/1024/1024 from user_segments where segment_name='SYS_IOT_TOP_75124';

 

SEGMENT_NAME                   BYTES/1024/1024

------------------------------ ---------------

SYS_IOT_TOP_75124                            2

 

 

我們刪除一批資料，形成死葉子節點。

 

 

SQL> delete t_iot where rownum<40000;

39999 rows deleted

 

SQL> commit;

Commit complete

 

SQL> exec dbms_stats.gather_table_stats(user,'T_IOT',cascade => true);

PL/SQL procedure successfully completed

 

SQL> select segment_name, bytes/1024/1024 from user_segments where segment_name='SYS_IOT_TOP_75124';

 

SEGMENT_NAME                   BYTES/1024/1024

------------------------------ ---------------

SYS_IOT_TOP_75124                            2

 

 

資料行被刪除，索引段HWM沒有收縮。我們可以使用analyze index命令進行索引健康程度檢查。

 

 

SQL> analyze index SYS_IOT_TOP_75124 validate structure;

Index analyzed

 

 

QL> select height, blocks, name, lf_rows, DEL_LF_ROWS, pct_used from index_stats;

 

    HEIGHT     BLOCKS NAME                  LF_ROWS DEL_LF_ROWS   PCT_USED

---------- ---------- ------------------------------ ---------- ----------- ----------

         2        256 SYS_IOT_TOP_75124            72638       39999         90

 

 

從index_stats檢視中，我們可以清晰看到有接近四萬葉子節點是Dead狀態，索引樹高度為2。我們進行索引rebuild，是常用的整理索引操作。

 

 

SQL> alter index SYS_IOT_TOP_75124 rebuild;

alter index SYS_IOT_TOP_75124 rebuild

 

ORA-28650: IOT 中的主索引不能重建

 

SQL> alter table t_iot disable constraint SYS_IOT_TOP_75124;

alter table t_iot disable constraint SYS_IOT_TOP_75124

 

ORA-25188: 對於索引表或排序雜湊簇, 無法刪除/禁用/延遲主鍵約束條件

 

 

常用的rebuild操作不能使用在IOT主鍵索引中，而且disable索引也沒有辦法實現。整理IOT的方法，可以選擇資料表的move方法。

 

 

SQL> alter table t_iot move;

 

Table altered

 

SQL> exec dbms_stats.gather_table_stats(user,'T_IOT',cascade => true);

 

PL/SQL procedure successfully completed

 

SQL> select segment_name, bytes/1024/1024 from user_segments where segment_name='SYS_IOT_TOP_75124';

 

SEGMENT_NAME                   BYTES/1024/1024

------------------------------ ---------------

SYS_IOT_TOP_75124                       0.6875

 

 

整理資料表t_iot move操作後，索引高水位線下降。

 

 

SQL> analyze index SYS_IOT_TOP_75124 validate structure;

 

Index analyzed

 

SQL> select height, blocks, name, lf_rows, DEL_LF_ROWS, pct_used from index_stats;

 

    HEIGHT     BLOCKS NAME                    LF_ROWS DEL_LF_ROWS   PCT_USED

---------- ---------- ------------------------------ ---------- ----------- ----------

         2         88 SYS_IOT_TOP_75124            32639           0         89

 

 

從分析結果看，我們消除了死葉子節點。那麼，我們是否可以對資料表開啟row movement呢？這個操作是move操作的替代品。

 

 

SQL> alter table t_iot enable row movement;

alter table t_iot enable row movement

 

ORA-14066: 未分割槽的索引表的選項非法

 

SQL> alter table t_heap enable row movement;

Table altered

 

 

從實驗結果看，row movement不能應用到IOT上。

 

 

5、IOT Index Overflow Segment

 

對IOT表而言，我們需要考慮Overflow Segment的問題。B樹索引葉子節點存在一個長期讓我們爭議的問題，就是葉子塊分裂、合併的問題。

 

索引結構成樹過程和維持過程，是一個索引樹不斷分裂葉子節點、複製資料的過程。當一個新葉子節點值加入索引樹的時候，索引結構需要將其有序的分配在特定的葉子“位置”上。這點和堆表heap table的隨機儲存策略差異很大。如果這個位置所在的資料塊已經寫“滿”，就需要進行資料塊分裂（5/5演算法或者9/1演算法），找一個新的空白塊，將溢位的資料葉子節點資訊寫入到新塊中。這個過程同時伴隨著分支節點的調整。

 

維持B樹平衡過程是很複雜的過程，一般資料表為了維持對應索引的同步結構通常要損失一個數量級的DML操作效率。

 

對於IOT來說，這種B樹平衡過程代表更加複雜的消耗。因為IOT表的所有資料行都要儲存在葉子塊中，維持樹過程中的複製和分裂操作更加劇烈。Oracle為了緩解這個情況，引入了IOT Overflow Segment概念。

 

通常來說，我們使用IOT表是需要進行考量的。我們很傾向選擇資料主鍵列相對較大，列數相對較少的資料表作為IOT表。同時，讀多寫少也是IOT的重要定性指標。

 

Overflow Segment（溢位段）的理念很簡單，透過設定一個閾值（PCTThreshold），來規定將資料行轉移儲存位置。如果我們將PCTThreshold值設定為10，那麼如果一個資料行空間佔有比例超過了10%資料塊大小，非主鍵列都會被“溢”出到IOT索引之外進行儲存。這個溢位空間我們稱之為“Overflow Segment”，我們也可以為溢位段指定單獨的表空間進行儲存。

 

Overflow Segment存在的表空間，我們稱之為Overflow Segment Tablespace。下面我們建立一個全新的IOT，設定專門的PCTThreshold值。

 

 

 

SQL> create table t_iotbig

  2  (object_id number primary key,

  3   object_name varchar2(200),

  4   object_type varchar2(100),

  5   EDITION_NAME varchar2(100),

  6   last_ddl_time date)

  7  organization index tablespace users

  8  pctthreshold 5

  9  overflow tablespace example;

 

Table created

 

 

SQL> insert into t_iotbig select object_id, object_name, object_type, edition_name, last_ddl_time from dba_objects;

72604 rows inserted

 

SQL> commit;

Commit complete

 

SQL> exec dbms_stats.gather_table_stats(user,'T_IOTBIG',cascade => true);

PL/SQL procedure successfully completed

 

 

資料表段（本質是索引段）所在表空間指定，是透過organization index tablespace指定的。Pctthreshold引數來指定溢位段閾值，我們試驗中設定為5%。溢位段overflow segment透過overflow tablespace來指定。

 

裝載約7萬餘條資料之後，我們檢查資料段的情況。

 

 

SQL> col tablespace_name for a10;

SQL> col iot_name for a10;

SQL> select table_name, tablespace_name, num_rows, iot_type, iot_name from dba_tables where wner='SYS' and table_name='T_IOTBIG';

 

TABLE_NAME                     TABLESPACE   NUM_ROWS IOT_TYPE     IOT_NAME

------------------------------ ---------- ---------- ------------ ----------

T_IOTBIG                                       72604 IOT         

 

 

SSQL> select index_name, index_type, PCT_THRESHOLD, tablespace_name from dba_indexes where table_name='T_IOTBIG' and wner='SYS';

 

INDEX_NAME         INDEX_TYPE                  PCT_THRESHOLD TABLESPACE

------------------------------ --------------------------- ------------- ----------

SYS_IOT_TOP_75137              IOT - TOP                               5 USERS

 

 

索引具備屬性pct_threshold=5。同時，我們在dba_tables中，可以看到溢位段的情況。

 

 

SQL> select table_name, tablespace_name, iot_name, iot_type from dba_tables where wner='SYS' and iot_name='T_IOTBIG';

 

TABLE_NAME                     TABLESPACE IOT_NAME   IOT_TYPE

------------------------------ ---------- ---------- ------------

SYS_IOT_OVER_75137             EXAMPLE    T_IOTBIG   IOT_OVERFLOW

 

 

在資料表檢視中，我們發現IOT_NAME中對應IOT資料表名稱的物件中，存在一個特殊的隱含資料表，命名為系統自動命名。這個資料表和IOT不同，明確表示存在表空間EXAMPLE中，IOT_TYPE也明確標註出IOT_OVERFLOW型別。

 

我們從段空間分配的角度，看IOT表T_IOTBIG的情況。

 

 

SQL> select segment_name, segment_type, tablespace_name, extents, blocks from dba_segments where wner='SYS' and segment_name in ('SYS_IOT_OVER_75137','SYS_IOT_TOP_75137','T_IOTBIG');

 

SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE    EXTENTS     BLOCKS

-------------------- ------------------ ---------- ---------- ----------

SYS_IOT_TOP_75137    INDEX              USERS              20        640

SYS_IOT_OVER_75137   TABLE              EXAMPLE             1          8

 

 

從dba_segments中，可以清楚看到IOT表的空間使用情況：索引段是有空間分配的、溢位段也是有空間分配的。而且兩者可以在不同的表空間。

 

 

引數pctthreshold是可以指定這個溢位段閾值。如果不指定，Oracle會選擇一個預設值50%。我們的IOT表t_iot就是這樣的方式。

 

 

SQL> select index_name, index_type, PCT_THRESHOLD, tablespace_name from dba_indexes where table_name='T_IOT' and wner='SYS';

 

INDEX_NAME                     INDEX_TYPE                  PCT_THRESHOLD TABLESPACE

------------------------------ --------------------------- ------------- ----------

SYS_IOT_TOP_75124              IOT - TOP                              50 SYSTEM

 

SQL> select count(*) from dba_tables where wner='SYS' and iot_name='T_IOT';

 

  COUNT(*)

----------

         0