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

標籤：通過   一點   解決方案   自動化   避免   rac   where   app   src   

一 現象描述

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操作遺留的影響，使用合適的方法整理空間。

 

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