今天同事問了一個問題，說是一個千萬級的表，刪除了500W的資料，怎麼查詢效能沒有明顯的提高。這個問題其實跟高水位HWM這個概念有關，當建立一個表的時候，就會為這個物件分配一個段，段下面有多個extent，一個extent包含多個連續的塊，資料的最小儲存單位是塊，這張表的資料持續增多的時候，那麼使用的塊會越來越多，這個HWM就表示某個塊以下所有的塊全部存過資料了。舉個例子，比如一個水槽，水上升的時候在最高處會有一個標記，但是當水退掉後，這個標記不會下降！、
簡單的瞭解了上面的概念，我們就能解釋上面的問題了，為什麼刪除了大部分資料，查詢效能沒有明顯的提升，因為資料是刪除了，但是HWM沒有下降，那麼對錶的掃描還是掃描了HWM以下所有的塊，效能沒有明顯的提升，下面我們簡單的演示一下：
SQL> create test(a int);

Table created.

SQL> set timing on
#插入1000W測試資料(這個插資料的過程還可以最佳化，這地方就簡單的插下了)
SQL> begin
2 for i in 1..10000000 loop
3 insert into test values(i);
4 if mod(i,10000)=0 then
5 commit;
6 end if;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:08:22.19
#查詢一下這個表
SQL> select count(*) from test;

COUNT(*)
----------
10000000

Elapsed: 00:00:05.01
#刪除一半的資料
SQL> delete from test where a<=5000000;

5000000 rows deleted.

Elapsed: 00:02:22.33
#再次查詢這個表
SQL> select count(*) from test;

COUNT(*)
----------
5000000

Elapsed: 00:00:04.48
看這個結果，按照正常的理解，少了一半的資料，效能應該要提升1倍吧，但是測試結果，兩個查詢時間幾乎一樣，沒有大的差別，這就驗證了上面說的HWM問題。那麼有沒有辦法可以解決這個問題呢，回收了這個高水位不就可以了嘛，那麼有沒有辦法來回收高水位呢，以後回收比較方便，直接用shrink就行了。如下：
SQL> alter table test enable row movement;

Table altered.

Elapsed: 00:00:00.14
SQL> alter table test shrink space;

Table altered.

Elapsed: 00:03:42.36

#再次查詢這個表
SQL> select count(*) from test;

COUNT(*)
----------
5000000

Elapsed: 00:00:00.52
可以看到這裡的查詢速度得到了明顯的提升，這個是在10g後才可以使用的方法，我們也可以透過move重建，達到一樣的效果。這裡就不試驗了，簡單的給下：
alter table test move;後面可以加表空間，表示move到一個新的表空間，不加表空間就表示還在當前的表空間！

轉自：http://space.itpub.net/?uid-26463985-action-viewspace-itemid-713850

delete與高水位線HWM回收

相關文章