Oracle delete 高水位線處理問題

最近遇到 表中資料量很大查詢和更新比較慢 需要刪除，發現刪除後查詢速度還是很慢，原來是delete oracle 高水位沒有下降的原因。

在oracle裡,使用delete刪除資料以後，資料庫的儲存容量不會減少，而且使用delete刪除某個表的資料以後，查詢這張表的速度和刪除之前一樣，不會發生變化。

因為oralce有一個HWM高水位，它是oracle的一個表使用空間最高水位線。當插入了資料以後，高水位線就會上漲，但是如果你採用delete語句刪除資料的話，資料雖然被刪除了，但是高水位線卻沒有降低，還是你剛才刪除資料以前那麼高的水位。除非使用truncate刪除資料。那麼，這條高水位線在日常的增刪操作中只會上漲，不會下跌，所以資料庫容量也只會上升，不會下降。而使用select語句查詢資料時，資料庫會掃描高水位線以下的資料塊，因為高水位線沒有變化，所以掃描的時間不會減少，所以才會出現使用delete刪除資料以後，查詢的速度還是和delete以前一樣

解決方案：

1.首先匯出表，然後truncate這張表，最後匯入這張表。

2.在儲存空間當中移動表，但是由於rowid會被打亂，所以需要重建索引.

3.如果是oracle 10g.可是直接更新表的高水位線。

對應的SQL：

9i中：

create table aa_bak as select * from aa where record_time > sysdate - 10;

truncate table aa;

insert into aa select * from aa_bak;

drop table aa_bak;

10g 版本(也適用於11G)

alter tablename enable row movement;

alter tablename shrink space;

實戰：

select count(*) from wlkp_fp_kj

查詢結果顯示2301245 條記錄

我們需要刪除一部分資料

刪除之前我們先檢視錶的高水位線(wlkp_fp_kj 是張分割槽表）

SELECT segment_name, segment_type, blocks FROM dba_segments WHERE segment_name = 'WLKP_FP_KJ'

刪除資料SQL

1. select count(*) from wlkp_fp_kj where  kprq'2011-12-01 14:00:00', 'yyyy-mm-dd,hh24:mi:ss')   
2. and kprq>to_date('2011-11-01 14:00:00', 'yyyy-mm-dd,hh24:mi:ss')  

記錄條數 54802

刪除語句

1. delete from wlkp_fp_kj where  kprq'2011-12-01 14:00:00', 'yyyy-mm-dd,hh24:mi:ss')   
2. and kprq>to_date('2011-11-01 14:00:00', 'yyyy-mm-dd,hh24:mi:ss')  

刪除後查詢WLKP_FP_KJ 表的高水位

發現查詢結果和上面沒有刪除之前一樣沒有變化。說明DELETE 語句高水位不會下降

我資料庫10G 直接用方法二實現

執行如下語句：

alter table wlkp_fp_kj enable row movement; alter table wlkp_fp_kj shrink space; 

執行後結果在查詢WLKP_FP_KJ的高水位

很明顯看到執行後高水位下降了。
-----&gt>補充說明:

select blocks, empty_blocks from dba_tables where table_name='xxx' and owner='xx';
blocks就是已經分配的空間即HWM，實際分配的空間，不是實際大小

---&gt>Oracle 10g Shrink Table和Shrink Space使用詳解

Oracle 10gShrink Table的使用是本文我們主要要介紹的內容，我們知道，如果經常在表上執行DML操作，會造成資料庫塊中資料分佈稀疏，浪費大量空間。同時也會影響全表掃描的效能，因為全表掃描需要訪問更多的資料塊。從Oracle 10g開始，表可以透過shrink來重組資料使資料分佈更緊密，同時降低HWM釋放空閒資料塊。

segment shrink分為兩個階段：

1、資料重組(compact):透過一系列insert、delete操作，將資料儘量排列在段的前面。在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。由於涉及到rowid的改變，需要enable row movement.同時要disable基於rowid的trigger.這一過程對業務影響比較小。

2、HWM調整：第二階段是調整HWM位置，釋放空閒資料塊。此過程需要在表上加X鎖，會造成表上的所有DML語句阻塞。在業務特別繁忙的系統上可能造成比較大的影響。Shrink Space語句兩個階段都執行。Shrink Space compact只執行第一個階段。

如果系統業務比較繁忙，可以先執行Shrink Space compact重組資料,然後在業務不忙的時候再執行Shrink Space降低HWM釋放空閒資料塊。shrink必須開啟行遷移功能。

alter table table_name enable row movement ;

注意：alter table XXX enable row movement語句會造成引用表XXX的物件(如儲存過程、包、檢視等)變為無效。執行完成後，最好執行一下utlrp.sql來編譯無效的物件。

語法：

alter table  shrink space [  | compact | cascade ]; alter table  shrink space compcat;

收縮表，相當於把塊中資料打結實了，但會保持high water mark;

alter table Shrink Space;

收縮表，降低 high water mark;

alter table Shrink Space cascade;

收縮表，降低 high water mark，並且相關索引也要收縮一下下。

alter index idxname Shrink Space;

回縮索引

1：普通表

Sql指令碼，改指令碼會生成相應的語句

select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables; select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;

2：分割槽表的處理

進行Shrink Space時 發生ORA-10631錯誤.Shrink Space有一些限制.

在表上建有函式索引（包括全文索引）會失敗。

Sql指令碼，改指令碼會生成相應的語句

select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ; select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ; select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';

Shrink的幾點問題：

1. shrink後index是否需要rebuild：因為shrink的操作也會改變行資料的rowid，那麼，如果table上有index時，shrink table後index會不會變為UNUSABLE呢？

我們來看這樣的實驗，同樣構建my_objects的測試表：

create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;
create index i_my_objects on my_objects (object_id);
delete from my_objects where object_name like '%C%'; 
delete from my_objects where object_name like '%U%';

現在我們來shrink table my_objects：

SQL> alter table my_objects enable row movement; Table altered
SQL> alter table my_objects shrink space; Table altered
SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
 INDEX_NAME STATUS ------------------------------ -------- 
I_MY_OBJECTS VALID

我們發現，table my_objects上的index的狀態為VALID，估計shrink在移動行資料時，也一起維護了index上相應行的資料rowid的資訊。我們認為，這是對於move操作後需要rebuild index的改進。但是如果一個table上的index數量較多，我們知道，維護index的成本是比較高的，shrink過程中用來維護index的成本也會比較高。

2. shrink時對table的lock

在對table進行shrink時，會對table進行怎樣的鎖定呢？當我們對table MY_OBJECTS進行shrink操作時，查詢v$locked_objects檢視可以發現，table MY_OBJECTS上加了row-X (SX) 的lock：

SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE ---------- ---------- ------------------ -----------
55422 153 DLINGER 3
SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
OBJECT_ID ---------- 55422

那麼，當table在進行shrink時，我們對table是可以進行DML操作的。

3．shrink對空間的要求

我們在前面討論了shrink的資料的移動機制，既然oracle是從後向前移動行資料，那麼，shrink的操作就不會像move一樣，shrink不需要使用額外的空閒空間。