HWM高水位是指一個segment(Table/Index)中已使用與未曾使用的Block分界線,
也就是說在HWM之下的Block是已使用(包含曾使用),在HWM之上的Block是未曾使用。
當不斷地insert資料時,HWM也會跟著向上移動,但是Delete資料,HWM卻不會往下移動,
就像飲料一樣,在裝杯時水量不斷增加,但是開始喝飲料時,水量就下降,
但是最高的水痕依然留在杯上,沒有降低。
而Oracle在執行Table的full scan時並不是只讀取有資料的Block,
而是從HWM以下的Block都會讀取(無論是否有資料),所以會影響查詢的時間。
如果有大量空的已使用Block就表示該Segment有大量的碎片,浪費大量的空間。
例如我有一個暫存各種資料的暫存表,有一次新增了三百多萬筆的資料
SQL> select count(1) from tmp_ddd;
COUNT(1)
----------
3705682
SQL> delete tmp_ddd;
3705682 rows deleted.
SQL> commit;
Commit complete.
SQL> select segment_name,segment_type,blocks FROM dba_segments WHERE segment_name='TMP_DDD';
SEGMENT_NA SEGMENT_TY BLOCKS
---------- ---------- ----------
TMP_DDD TABLE 42912
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM dba_tables WHERE table_name='TMP_DDD';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------- ---------- ---------- ------------
TMP_DDD 0 42636 8
由上可知雖然已經將資料刪除,但是BLOCKS還是有這麼多
查詢Table的大小
SQL> select segment_name, bytes/1024/1024 "表大小MB" from dba_segments where segment_name='TMP_DDD';
SEGMENT_NAME 表大小MB
-------------- ----------
TMP_DDD 335.25
查詢Table的實際大小
SQL> select table_name, AVG_ROW_LEN ,NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024 "表實際大小MB",LAST_ANALYZED from dba_tables where table_name='TMP_DDD';
SEGMENT_NAME 表實際大小MB
-------------- -------------
TMP_DDD .00494
碎片公式:(1-表實際數據大小/表大小)
(1-0.00494/335.25) = 99.9%
對於查詢的影響
SQL> set timing on;
SQL> select * from tmp_ddd;
no rows selected
Elapsed: 00:00:05.24
沒有任何資料,查詢花費5秒鐘。
所以可以利用truncate Table,把HWM降低。
(也可以利用alter table table_name move、drop再重建或10g以後還可以用shrink)
SQL> truncate table cusadmin.tmp_ddd;
Table truncated.
SQL> select segment_name,segment_type,blocks FROM dba_segments WHERE segment_name='TMP_DDD';
SEGMENT_NA SEGMENT_TY BLOCKS
---------- ---------- ----------
TMP_DDD TABLE 16
SQL> analyze table cusadmin.tmp_ddd compute statistics;
Table analyzed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM dba_tables WHERE table_name='TMP_DDD';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ---------- ------------
TMP_DDD 0 0 16
再次查詢,不到1秒鐘
SQL> select * from tmp_ddd;
no rows selected
Elapsed: 00:00:00.01