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

一 現(xiàn)象描述

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

二 現(xiàn)象分析

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

所謂的高水位（HWM），通俗的講就是一個(gè)標(biāo)記，用來記錄已經(jīng)有多少數(shù)據(jù)塊（Block）分配給表，可以拿水庫的歷史最高水位來類比，當(dāng)使用delete操作后，數(shù)據(jù)雖然被刪除了，但這個(gè)高水位的標(biāo)記并沒有降低，就好比水庫的歷史最高水位不會因?yàn)樗会尫帕硕档?。因而，原則上在沒有外部干預(yù)的條件下，這個(gè)高水位標(biāo)記值只會增大，不會降低。

三 實(shí)戰(zhàn)模擬重現(xiàn)現(xiàn)象

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

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

第2，創(chuàng)建完成后，我們查看下數(shù)據(jù)表占用的空間，如圖所示：

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

注意上面三個(gè)字段的結(jié)果：BLOCKS=0;  EMPTY_BLOCKS=13312;  NUM_ROWS=0，即當(dāng)前表占用的塊數(shù)為0，默認(rèn)1 BLOCK = 8kb，預(yù)分配的塊為13312，行數(shù)為0。

一切都沒有問題，新創(chuàng)建的表，沒有數(shù)據(jù)嘛，當(dāng)然行數(shù)為0，占用塊數(shù)為0嘍。

第3，寫一個(gè)語句塊，循環(huán)插入1000條語句，再次對test表進(jìn)行分析、查詢，結(jié)果如下：

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

第4，使用Delete語句刪除1000條數(shù)據(jù)，再次對test表進(jìn)行分析、查詢，結(jié)果卻是如下：

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

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

四 解決方法

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

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

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

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

五 shrink space常用操作命令

Shrink space的常用命令如下：

六 Delete操作的潛在影響

根據(jù)上述分析，delete操作產(chǎn)生的潛在影響如下：

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

2. 插入操作時(shí)使用append關(guān)鍵字，即使HWM以下有空閑的數(shù)據(jù)庫塊，插入時(shí)使用HWM以上的數(shù)據(jù)塊；（造成HWM自動(dòng)增大）

七 總結(jié)

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