Oracle delete操作隱藏著你可能不知道的秘密

一 現象描述

Delete是oracle數據庫中的常用操作，尤其是在自動化測試中，初始化環境、前置準備都不可避免的進行增刪操作，但持續一時間后，可能會碰到表空間不足這類報錯現象，這就不禁納悶兒了，明明插入數據前會有刪除的，數據總量并沒有呈現明顯的量級變化，為什么表占用空間卻在偷偷增大呢？

二 現象分析

出現上述現象的原因是Delete操作并不會釋放占用的空間。在講解原因之前，先了解下oracle中高水位線的概念，有助于理解delete操作產生的這種現象。

所謂的高水位（HWM），通俗的講就是一個標記，用來記錄已經有多少數據塊（Block）分配給表，可以拿水庫的歷史最高水位來類比，當使用delete操作后，數據雖然被刪除了，但這個高水位的標記并沒有降低，就好比水庫的歷史最高水位不會因為水被釋放了而降低。因而，原則上在沒有外部干預的條件下，這個高水位標記值只會增大，不會降低。

三 實戰模擬重現現象

根據上面的現象描述和分析，接下來，我會用具體的實例模擬該現象，使大家可以更直觀的了解。

第1，創建一張測試表test，具體字段不需要關心，只要知道初始了存儲空間為100M，如圖所示：

第2，創建完成后，我們查看下數據表占用的空間，如圖所示：

其中，查詢前需要對表進行分析，使用命令為：ANALYZE TABLE test ESTIMATE STATISTICS;查詢語句為：SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TEST';

注意上面三個字段的結果：BLOCKS=0;  EMPTY_BLOCKS=13312;  NUM_ROWS=0，即當前表占用的塊數為0，默認1 BLOCK = 8kb，預分配的塊為13312，行數為0。

一切都沒有問題，新創建的表，沒有數據嘛，當然行數為0，占用塊數為0嘍。

第3，寫一個語句塊，循環插入1000條語句，再次對test表進行分析、查詢，結果如下：

從圖中可以看到，占用BLOCKS=222，NUM_ROWS=1000，合乎邏輯，插入了1000條數據，占用了空間嘛。

第4，使用Delete語句刪除1000條數據，再次對test表進行分析、查詢，結果卻是如下：

從上圖中可以清楚的看到，數據被刪除后，NUM_ROWS=0了，但是BLOCKS并沒有被置為0，也就是這部分數據塊仍然被認為是占用的。

因此，就出現了本文一開始就提到的現象，隨著不斷的插入、刪除數據，BLOCKS也會不斷擴大，盡快delete操作后，可能表中數據量很少，但表占用的存儲空間未減少。

四 解決方法

針對delete操作引起的空間不釋放現象，或者，更正式一點的說法，如何降低高水位線，方法有很多種，如，shrink space；move tablespace；create table xxx as select * from xxx 重建表等。使用這些方法前，我們的原則是：

如果可以truncate，直接truncate，該操作會重置高水位線，BLOCKS會被置為0，NUM_ROWS置為0；否則，優先使用shrink space，該方法不需要重建索引。

接著上面第4步，我們使用shrink space降低高水位線，釋放空間，其中，使用shrink space命令前，需要先alter table test enable row movement;開啟行移動，再次對表進行分析、查詢，結果如下：

從圖中可以看出，此時BLOCKS已經被置為0了，但是，細心的你可能也發現， EMPTY_BLOCKS已經不是初始的13312，而是此時的40，這說明shrink space不僅會釋放高水位線以下的空間，也會釋放申請的空間，即高水位線上下都有操作，這也是與move、truncate的不同，它們只能釋放高水位線以下的空間。

五 shrink space常用操作命令

Shrink space的常用命令如下：

六 Delete操作的潛在影響

根據上述分析，delete操作產生的潛在影響如下：

1. 全表掃描通常要讀出直到HWM標記的所有屬于該表的數據塊，即使該表中沒有任何數據；（造成查詢變慢）

2. 插入操作時使用append關鍵字，即使HWM以下有空閑的數據庫塊，插入時使用HWM以上的數據塊；（造成HWM自動增大）

七 總結

通過上文的現象描述和分析，隨著insert的不斷操作，高水位線也隨著不斷增加，盡管delete了數據，但高水位線并沒有下降，導致表占用的空間沒有釋放。因此，在實際應用中，如果可能，盡量使用truncate，而且該操作高效、快速；否則要考慮下delete操作遺留的影響，使用合適的方法整理空間。