白天一個朋友來電，諮詢這樣一個場景：生產環境上一個索引表空間，由於事先規劃有問題，出現空間不足。申請額外空間需要走正式流程需要時間，問臨時有沒有什麼好方法？

表空間對應的索引實際上是一張核心資料表的索引所在空間。由於經常性的進行資料匯入操作，引起索引空間的不足。經過思考，在短時間內申請不到額外資源的情況下，可以從幾個方面去進行嘗試。

1、實驗環境準備

朋友的環境是Oracle 10g，所以對應使用相似的實驗環境。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

2、嘗試索引rebuild

對很多DBA而言，索引定期rebuild是維護相關查詢效率的重要日常工作。很多時候，經常性的對一些索引進行rebuild重構操作，的確可以最佳化索引結構，提高執行作業效率。

在以前的Blog中，筆者討論過對於定期rebuild索引的見解，此處不重複累述（http://space.itpub.net/17203031/spacelist-blog-itemtypeid-84194）。這裡要強調的是，相對於堆表（Heap Table）結構，普通索引的空間使用上，是有不同的。

下面構建實驗環境資料表T：

SQL> select count(*) from t;

COUNT(*)

----------

202872

SQL> create index idx_t_name on t(object_name);

Index created

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

PL/SQL procedure successfully completed

SQL> select segment_name, bytes from dba_segments where segment_name in ('T','IDX_T_NAME') and wner='SCOTT';

SEGMENT_NAME BYTES

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

T 24117248

IDX_T_NAME 9437184

此時，我們刪除資料表中資料，重新收集統計量。

SQL> delete t;

202872 rows deleted

SQL> commit;

Commit complete

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

PL/SQL procedure successfully completed

SQL> select segment_name, bytes from dba_segments where segment_name in ('T','IDX_T_NAME') and wner='SCOTT';

SEGMENT_NAME BYTES

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

T 24117248

IDX_T_NAME 9437184

注意：我們將資料刪除掉之後，資料表和索引的分配空間是不會回收的。堆表（Heap Table）的空閒結構，會在日後資料插入（非direct load）方式時被利用起來。但是，索引標註為刪除的葉子節點，卻可能不會被重用！

如果索引列是那種單向遞增的取值方式，如使用sequence生成的資料值，所新插入的數值都是在葉子節點的右側高位上。這樣的話，過去被刪除的葉子節點是不會別利用上的。從而，索引的體積就只能單向的不斷膨脹。

解決的方法，就是進行週期性的rebuild操作。

SQL> alter index idx_t_name rebuild;

Index altered

SQL> select segment_name, bytes from dba_segments where segment_name in ('T','IDX_T_NAME') and wner='SCOTT';

SEGMENT_NAME BYTES

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

T 24117248

IDX_T_NAME 65536

注意：有的時候，rebuild也是需要額外的一些空間使用的，所以在資源特別緊張的時候，可以先unuable掉索引，再進行rebuild操作。

3、索引物件壓縮

壓縮索引段物件，是另一種解決問題的方法。在Oracle的各個版本中，一直在不斷完善壓縮技術。希望實現一種對DML操作效能影響最小的壓縮技術。

SQL> create table t as select owner a, owner b from dba_objects;

Table created

SQL> select count(*) from t;

COUNT(*)

----------

53301

SQL> create index idx_t_ownernocompr on t(a);

Index created

SQL> create index idx_t_ownercompr on t(b) compress;

Index created

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

PL/SQL procedure successfully completed

在構建索引時，使用compress關鍵字，可以定義壓縮索引段結構。空間使用情況如下：

SQL> select index_name, compression from dba_indexes where wner='SCOTT' and table_name='T';

INDEX_NAME COMPRESSION

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

IDX_T_OWNERNOCOMPR DISABLED

IDX_T_OWNERCOMPR ENABLED

SQL> select segment_name, bytes, blocks from dba_segments where segment_name in ('IDX_T_OWNERNOCOMPR','IDX_T_OWNERCOMPR');

SEGMENT_NAME BYTES BLOCKS

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

IDX_T_OWNERNOCOMPR 2097152 256

IDX_T_OWNERCOMPR 786432 96

可以看出，壓縮的索引物件可以節省空間使用。同時，對執行計劃來說，壓縮索引也是可以出現在執行計劃中的。

SQL> create table t_back as select * from t;

Table created

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 441125606

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

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

| 0 | SELECT STATEMENT | | 2235 | 26820 | 20

| 1 | TABLE ACCESS BY INDEX ROWID| T | 2235 | 26820 | 20

|* 2 | INDEX RANGE SCAN | IDX_T_OWNERNOCOMPR | 2235 | | 6

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

Predicate Information (identified by operation id):

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

2 - access("A"='SCOTT')

14 rows selected

SQL> explain plan for select * from t where b='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2961582115

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

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

| 0 | SELECT STATEMENT | | 2235 | 26820 | 18

| 1 | TABLE ACCESS BY INDEX ROWID| T | 2235 | 26820 | 18

|* 2 | INDEX RANGE SCAN | IDX_T_OWNERCOMPR | 2235 | | 4

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

Predicate Information (identified by operation id):

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

2 - access("B"='SCOTT')

14 rows selected

注意：壓縮索引，特別是11g之前的壓縮索引雖然壓縮比例尚可，但是對DML操作來說，必然存在一定的效能影響。這個方面到了11g有了根本性的改善，11g中推出的Advanced Compression元件，針對各種型別資料提供了全新的壓縮解決方案，同時其中的OLTP屬性，將壓縮對DML操作的影響降到最小。

4、分割槽索引使用

另外還有一種思路，就是建立在分割槽表基礎上。朋友的資料表是一張分割槽表，而且每次主要使用的就是一個分割槽的資料。那麼，可否只是啟用這個分割槽的索引，其他分割槽的索引不生成或者失效呢？

注意：分割槽表的索引分為全域性索引Global Index和本地索引Local Index兩個型別。如果是全域性索引，這種思路就不成立，因為索引段對應是一個。如果是Local Index，這種可能性是存在的。

SQL> create table t

2 partition by list(owner)

3 (

4 partition t_list_sys values ('SYS'),

5 partition t_list_scott values ('SCOTT'),

6 partition t_list_hr values ('HR'),

7 partition t_list_others values (default)

8 )

9 as select * from dba_objects where 1=0;

Table created

SQL> insert into t select * from dba_objects;

50727 rows inserted

SQL> commit;

Commit complete

建立本地索引。

SQL> create index idx_t_idlocal on t(object_id);

Index created

SQL> select segment_name, partition_name, bytes from user_segments where segment_name='T';

SEGMENT_NAME PARTITION_NAME BYTES

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

T T_LIST_SYS 3145728

T T_LIST_SCOTT 65536

T T_LIST_HR 65536

T T_LIST_OTHERS 4194304

SQL> create index idx_t_idlocal on t(object_id) local;

Index created

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

PL/SQL procedure successfully completed

SQL> select index_name, index_type, status from user_indexes where index_name='IDX_T_IDLOCAL';

INDEX_NAME INDEX_TYPE STATUS

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

IDX_T_IDLOCAL NORMAL N/A

SQL> select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

IDX_T_IDLOCAL T_LIST_SCOTT USABLE

IDX_T_IDLOCAL T_LIST_SYS USABLE

IDX_T_IDLOCAL T_LIST_HR USABLE

IDX_T_IDLOCAL T_LIST_OTHERS USABLE

可以透過部分unusable的方法，只啟用一部分的索引段物件。

SQL> alter index idx_t_idlocal unusable;

Index altered

SQL> select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

IDX_T_IDLOCAL T_LIST_SCOTT UNUSABLE

IDX_T_IDLOCAL T_LIST_HR UNUSABLE

IDX_T_IDLOCAL T_LIST_OTHERS UNUSABLE

IDX_T_IDLOCAL T_LIST_SYS UNUSABLE

SQL> alter index idx_t_idlocal rebuild;

alter index idx_t_idlocal rebuild

ORA-14086: 不能將分割槽索引作為整體重建

SQL> alter index idx_t_idlocal rebuild partition T_LIST_SYS;

Index altered

SQL> select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

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

IDX_T_IDLOCAL T_LIST_SCOTT UNUSABLE

IDX_T_IDLOCAL T_LIST_HR UNUSABLE

IDX_T_IDLOCAL T_LIST_OTHERS UNUSABLE

IDX_T_IDLOCAL T_LIST_SYS USABLE

本地索引的rebuild，是透過每個部分分割槽分別進行rebuild。當一部分分割槽usable，另一部分unusable的時候，處在usable狀態的索引是可以生效使用的。

SQL> explain plan for select * from t where wner='SYS' and object_id=1000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2092295073

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

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

| 0 | SELECT STATEMENT | | 1 | 92 |

| 1 | PARTITION LIST SINGLE | | 1 | 92 |

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 1 | 92 |

|* 3 | INDEX RANGE SCAN | IDX_T_IDLOCAL | 1 | |

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

Predicate Information (identified by operation id):

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

2 - filter("OWNER"='SYS')

3 - access("OBJECT_ID"=1000)

16 rows selected

SQL> explain plan for select * from t where wner='SCOTT' and object_id=1000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 882533222

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

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

| 0 | SELECT STATEMENT | | 1 | 85 | 3 (0)| 00:00:01 |

| 1 | PARTITION LIST SINGLE| | 1 | 85 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL | T | 1 | 85 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_ID"=1000)

14 rows selected

同時，並不影響insert等操作。

SQL> insert into t select * from dba_objects;

50726 rows inserted

SQL> commit;

Commit complete

但是，一部分索引處在unusable狀態，並不是沒有問題。在重新收集統計量的時候，Oracle會報錯。

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

begin dbms_stats.gather_table_stats(user,'T',cascade => true); end;

ORA-20000: index "SCOTT"."IDX_T_IDLOCAL" or partition of such index is in unusable state

ORA-06512: 在 "SYS.DBMS_STATS", line 13056

ORA-06512: 在 "SYS.DBMS_STATS", line 13076

ORA-06512: 在 line 1

所以，這種方法也只是權宜之計。

5、暫時rebuild到另一表空間

一個表空間空間缺少，可以暫時性的將索引rebuild到其他稍微空閒的表空間中，作為臨時的替代。

索引表空間不足的幾個處理思路

相關文章