delete 與全表掃描

pingley發表於2012-04-23
delete
delete 與全表掃描
先建立一張測試用的表。並填充資料。把表造的大小才有比較的效果。
SQL> create table testing01 (id number(8),col2 varchar2(30));
Table created.
SQL> begin
  2    for i in 1..1000000
  3    loop
  4    insert into testing01 values(i,'This is a testing record');
  5    end loop;
  6    commit;
  7    end;
  8    /   
PL/SQL procedure successfully completed.
透過下面的查詢對剛才建立的表有一個基本的認識。
SQL>  select segment_name,blocks,bytes/1024 as "Size[KB]"
  2   from user_segments
  3   where segment_name = 'TESTING01';
SEGMENT_NAME        BLOCKS   Size[KB]
--------------- ---------- ----------
TESTING01             4992      39936
計算HWM（high water mark），所謂的高水位線用來界定segment 中已經使用過的block 與沒有使用的block.已經使用的block 中不一定實際的儲存有資料。
SQL>  select blocks from user_segments
  2   where segment_name = 'TESTING01';
    BLOCKS
----------
      4992
SQL>  analyze table testing01 estimate statistics;
Table analyzed.
SQL>  select empty_blocks
  2   from user_tables
  3   where table_name = 'TESTING01';
EMPTY_BLOCKS
------------
          86
hwm =total_blocks – unused_blocks – 1.
HWM=4905
在segment testing01。使用使用過的block 數量是4905.執行如下的查詢檢視執行計劃。
SQL> set autotrace traceonly
SQL> select * from testing01;
1000000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3696461997
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1013K|    27M|  1512  (13)| 00:00:19 |
|   1 |  TABLE ACCESS FULL| TESTING01 |  1013K|    27M|  1512  (13)| 00:00:19 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      74301  consistent gets
        315  physical reads
     201404  redo size
   43646886  bytes sent via SQL*Net to client
     733745  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed
把表中的記錄全部delete ，在確定該表對應的segment 的HWM.
SQL> delete from testing01;
1000000 rows deleted.
SQL>  select blocks from user_segments
  2   where segment_name = 'TESTING01';
    BLOCKS
----------
      4992
SQL>  select blocks from user_segments
  2   where segment_name = 'TESTING01';
    BLOCKS
----------
      4992
SQL>  select empty_blocks
  2   from user_tables
  3   where table_name = 'TESTING01';
EMPTY_BLOCKS
------------
          86
透過查詢和使用公式hwm =total_blocks – unused_blocks – 1.
我們可以知道hwm 沒有下降，hwm 之所以沒有下降的原因是保留
這部分空間為了以後的update,insert 的需要。在這種情況下進行
全部掃描會是怎樣的情形呢？
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from testing01;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3696461997
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1013K|    27M|  1512  (13)| 00:00:19 |
|   1 |  TABLE ACCESS FULL| TESTING01 |  1013K|    27M|  1512  (13)| 00:00:19 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
        711  recursive calls
          0  db block gets
       4964  consistent gets
       4869  physical reads
          0  redo size
        340  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> set autotrace off
我們知道表中沒有資料。透過執行計劃我們可以知道這條sql 語句在執行的時候還是進行了。全表掃描讀取了很多的資料塊。因為oracle 在執行全表掃描的時候會讀取物件對應的segment中第一個block 到HWM之間的blocks。因為delete 不會降低HWM ,所以sql 執行的時候如果
要進行全表掃描，那麼oracle 很”無辜“需要多做很多額外的IO操作。為了不讓oracle 無辜的讀取那些在HWM下沒有資料的block，我們需要嘗試降低HWM。可選的方式如下。
1、truncate 一張表，降低HWM 釋放空間。如果表空中的資料都不再需要可以選用這種方式。
當時如果我想要刪除某些行這種方式就不行了。在很多時候可以結合表分割槽技術。
2、使用shrink 的方式，但是會導致索引的無效對index 需要rebuilt.步驟比較多。
3、把表drop 了重建。這可能是最爛的方式，因為要考慮到表之間複雜的參照關係，資料的填充花費的時間和系統資源。
對testing01 進行shrink。我沒有建立索引所以不需要對索引進行rebuilt.
SQL> alter table testing01 move;
Table altered.
SQL> select blocks from user_segments
  2   where segment_name = 'TESTING01';
    BLOCKS
----------
         8
SQL> analyze table testing01 estimate statistics;
Table analyzed.
SQL> select empty_blocks
  2   from user_tables
  3   where table_name = 'TESTING01';
EMPTY_BLOCKS
------------
           8
乖乖，現在HWM線下來了回到了表最初建立的時候的樣子指向segment 中第一個block的左邊。
今日心得
有時候進行全表掃描是好事，或者說是不可避免的。但是在上面這種情況（或者表中經常進行大量delete 的操作）下就糟糕了，因為oracle在執行sql 語句的時候很無辜需要讀取高水位線以下的很多空白的block 做無用功。我們應該考慮透過那些方式避免這種情況的出現。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26110315/viewspace-722053/，如需轉載，請註明出處，否則將追究法律責任。

相關文章