close
通常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)，因而可以減少表格重組過程中需要額外的空間需求‧
這個操作需要對表格加上表格層次的共用鎖定(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
的表格，僅僅只是改善非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
取得表格獨佔鎖定(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 程序.
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;
已更改表格.
全站熱搜
留言列表
