香蕉皮

通常Oracle的Table經過經年累月的Insert資料後，當做大量的Data Retention後，常常會發現效能還是不佳，那是因為High Water Mark(HWM)，並

不會HWM並不會往前移動，造成Full Table Scan時，還會從第一個Data Block掃瞄到HWM的位置，此時除了Table Move或Export再Import外，別

無他法，但Oracle 10g開始支援縮小區段(Shrink Space) 這個命令‧(文章內容和範例參考至申建忠的大作)

 

//shrink space compact
這個操作需要對表格加上表格層次的共用鎖定(TM:Row Exclusive)，並對搬移資料列加上獨佔鎖定(Exclusive)‧ 然後將表格的資
料列盡可能地向區段標頭(Segment Header)進中，方法是使用一組新增、刪除資料列操作‧先新增新的資料列，再刪除舊的資料列
，但新增的資料列將儘可能靠近區段標頭‧資料管理者可以線上重組表格，而不會妨礙其他使用者對該表格進行DML操作‧ 縮小區
段不會建立新的區段(Segment)，因而可以減少表格重組過程中需要額外的空間需求‧

但要注意的是該操作不會移動高水位標記(High Water Mark)，也不會回收位於高水位標記以後的區塊(Data Block)， 所以重組後
的表格，僅僅只是改善非Full Talbe Scan以外的情形(例Index Fast Full Scan、Index Range Scan、Index Unique Scan等等)‧
語法: ALTER TABLE table_name SHRINK SPACE COMPACT

 

//shrink space
取得表格獨佔鎖定(Exclusive)，將高水位標記(High Water Mark)移動到目前資料區塊(Data Block)中有資料列的最後一個資料區
塊的位置‧之後釋放新高水標記以後的資料區塊，因此，表格空間將標示那些回收的擴充區塊(Extent)為可用的擴充區塊‧
語法: ALTER TABLE table_name SHRINK SPACE

 

 

 

//[01]建立範例環境
SQL> create table scott.t3 as select * from dba_objects where object_id<=5000;
     已建立表格.
SQL> create index scott.t3_object_id_idx on scott.t3(object_id);
     已建立索引.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.

//[02]目前T2表格所在的Tablespace為USERS，以及共有4917筆資料列與使用52個Data Block
SQL> select table_name,tablespace_name,num_rows,blocks from dba_tables
  2  where owner='SCOTT' and table_name='T3';
     TABLE_NAME      TABLESPACE_NAME        NUM_ROWS     BLOCKS
     --------------- -------------------- ---------- ----------
     T3              USERS                      4917         67

//[03]目前Index的狀態為有效
SQL> select index_name,status from dba_indexes
  2  where owner='SCOTT' and index_name='T3_OBJECT_ID_IDX';
     INDEX_NAME                     STATUS
     ------------------------------ --------
     T3_OBJECT_ID_IDX               VALID

//[04]Table Move前，T3表格的物件編號為51356，以及區段編號為51356
SQL> select object_name,object_id,data_object_id from dba_objects
  2  where owner='SCOTT' and object_name='T3' and object_type='TABLE';
     OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID
     --------------- ---------- --------------
     T3                   51356          51356

//[05]目前區段所使用的擴充區塊(Extent)數量為9個，共有72個Data Block
SQL> select extent_id,file_id,block_id,blocks from dba_extents
  2  where owner='SCOTT' and segment_name='T3';
      EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
     ---------- ---------- ---------- ----------
              0          4         57          8
              1          4         65          8
              2          4         73          8
              3          4         81          8
              4          4         89          8
              5          4         97          8
              6          4        105          8
              7          4        113          8
              8          4        121          8
     已選取 9 個資料列.

//[06]縮小區段前資料列的Rowid
SQL> select rowid,object_id,object_name from scott.t3
  2  where object_id=4999;
     ROWID               OBJECT_ID OBJECT_NAME
     ------------------ ---------- -----------------------------
     AAAMicAAEAAAAB7AA9       4999 ALL_SNAPSHOT_REFRESH_TIMES

//[07]object_id為4999的資料列所在的Data Block為123，資料列為該Data Block的第61筆資料列
SQL> select dbms_rowid.rowid_block_number(rowid) block_number,
  2         dbms_rowid.rowid_row_number(rowid) block_number
  3  from scott.t3 where object_id=4999;
     BLOCK_NUMBER BLOCK_NUMBER
     ------------ ------------
              123           61

//[08]進行測試的資料刪除，造成Data Block有部份空間浪費
SQL> DELETE scott.t3 where MOD(object_id,2)=0;
     已刪除 2457 個資料列.
SQL> COMMIT;
     確認完成.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.

//[09]雖然資料列筆數減少為2460筆，但所使用的Data Block數量與資料列筆數4917筆相同‧因此有Data Block空間浪費的現象
SQL> select table_name,tablespace_name,num_rows,blocks from dba_tables
  2  where owner='SCOTT' and table_name='T3';
     TABLE_NAME      TABLESPACE_NAME        NUM_ROWS     BLOCKS
     --------------- -------------------- ---------- ----------
     T3              USERS                      2460         67

//[10]進行縮小區段前，表格必須啟用資料列移動(Row Movement)功能
SQL> alter table scott.t3 enable row movement;
     已更改表格.

//[11]Shrink Space Compact只搬移資料列，但不重設高水位標記(HWM)
SQL> alter table scott.t3 shrink space compact;
     已更改表格.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.

//[12]縮小區段並不會建立一個新的區段，所以縮小區段後，區段編號並沒有改變‧
SQL> select object_name,object_id,data_object_id from dba_objects
  2  where owner='SCOTT' and object_name='T3' and object_type='TABLE';
     OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID
     --------------- ---------- --------------
     T3                   51356          51356

//[13]縮小區段後資料列的Rowid已經跟進行縮小區段之前的Rowid不一樣
SQL> select rowid,object_id,object_name from scott.t3
  2  where object_id=4999;
     ROWID               OBJECT_ID OBJECT_NAME
     ------------------ ---------- -----------------------------
     AAAMicAAEAAAAA8AAo       4999 ALL_SNAPSHOT_REFRESH_TIMES

//[14]縮小區段後object_id為4999的資料列所在的Data Block為60，資料列為該Data Block的第40筆資料列
SQL> select dbms_rowid.rowid_block_number(rowid) block_number,
  2         dbms_rowid.rowid_row_number(rowid) block_number
  3  from scott.t3 where object_id=4999;
     BLOCK_NUMBER BLOCK_NUMBER
     ------------ ------------
               60           40

//[15]因為縮小區段使用新增／刪除資料列的技巧來搬移資料列，因此Index的內容也一同被維護
SQL> select index_name,status from dba_indexes
  2  where owner='SCOTT' and index_name='T3_OBJECT_ID_IDX';
     INDEX_NAME                     STATUS
     ------------------------------ --------
     T3_OBJECT_ID_IDX               VALID

//[16]縮小區段並沒有建立新的區段(Segment)，所以擴充區塊(Extent)並未改變
SQL> select extent_id,file_id,block_id,blocks from dba_extents
  2  where owner='SCOTT' and segment_name='T3';
      EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
     ---------- ---------- ---------- ----------
              0          4         57          8
              1          4         65          8
              2          4         73          8
              3          4         81          8
              4          4         89          8
              5          4         97          8
              6          4        105          8
              7          4        113          8
              8          4        121          8
     已選取 9 個資料列.

//[17]Shrink Space將會搬移資料列，並會重新設定高水位標記(HWM)，與釋放高水位標記以後的Data Block
SQL> alter table scott.t3 shrink space;
     已更改表格.
SQL> execute dbms_stats.gather_table_stats('scott','t3');
     已順利完成 PL/SQL 程序.

//[18]經過Shrink Space後，所使用的區塊減為27個
SQL> select table_name,tablespace_name,num_rows,blocks from dba_tables
  2  where owner='SCOTT' and table_name='T3';
     TABLE_NAME      TABLESPACE_NAME        NUM_ROWS     BLOCKS
     --------------- -------------------- ---------- ----------
     T3              USERS                      2460         27

//[19]區段所擁有的擴充區塊(Extent)也減少為4個，Data Block共32個
SQL> select extent_id,file_id,block_id,blocks from dba_extents
  2  where owner='SCOTT' and segment_name='T3';
      EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
     ---------- ---------- ---------- ----------
              0          4         57          8
              1          4         65          8
              2          4         73          8
              3          4         81          8

//[20]關閉表格必的資料列移動(Row Movement)功能
SQL> alter table scott.t3 disable row movement;
     已更改表格.