一、  大表 delete 背景需求

某使用者的資料庫存在一個交易賬單明細表，存放近 3 年的交易賬單明細資訊，現有資料量約 120 G ，共計 1700w 行資料記錄，由於使用者業務要求及提高查詢效率的關係，僅需要保留近一個月的訂單明細資訊即可，其餘資訊將不再提供給業務使用者進行查詢，現希望對此大表進行裁剪，按表內提供的時間欄位進行篩選刪除，並不干擾正常業務執行。

二、  大表 delete 效率分析

經過對資料庫的查詢發現 1600 w + 的資料都需要被刪除，改表在開藥等產生交易流水的過程中都會不斷的產生資料，如果採用 create   table   XXX 賬單交易明細 _temp as select * from XXX 賬單交易明細  where create_date > sysdate-30 的方式撈出需求資料再進行 truncate   table   操作存在兩點問題， 1 是存在業務中斷， 2 是很難保證在 create   table   後無新資料產生。所以排除這種做法。

為避免業務中斷和資料的丟失，採用刪除資料的方式進行處理。基本的執行 sql   為 delete   from   XXX 賬單交易明細   where   create _date < sysdate-30 。但是對於這樣的一個 sql 執行依然存在著一些問題。

透過上述的環境可知 sql 刪除的資料量過大，眾所周知， oracle 在進行大事務處理的時候回產生大量的 undo 和 redo 資料，這中間產生的資料量可能已經超過了表本身的資料量，對於一個大的事務執行來說，中途無法斷執行，一旦執行被中斷，又會消耗大量的 io 去進行資料的回寫，這樣對於整體的效能消耗非常嚴重，也可能對現有業務衝擊較大。佔用正常業務量使用的 io 情況。這種情況其實就意味著如果將這麼一個大表刪除當成是一個大的事務來處理是非常糟糕的一個事情，因為中途如果產生業務的卡頓問題，中途想停止作業也是不可能的，因為中途停止造成的資料 rollback 可能是對業務系統更加大的衝擊。綜上所述對於這個刪除作業來說應該將以此大的事務切割成小的事務來進行完成。那麼一個表刪除切割刪除可以根據根據 id %100 這種方式來進行刪除，比如說 d elete   from   XXX 賬單交易明細   where   create _date < sysdate-30 and mod(id,100) = 1; 進行事務的切割刪除，然後可以同時跑多個程式刪除 mod(id,100) = 2 ， 3 等等後面的資料進行加速刪除任務。但是這種切片其實存在一個問題就是事務槽的爭用。當執行多個程式並行任務的時候，可能多個程式會去同時爭搶一個塊上的事務槽，因為這種取模的切割方法無法很好的分配每個程式只掃描固定的一些區域，事實上，很多情況下相當於每個程式都要去掃描這個表裡面的全部內容，這種事務的爭搶和重複掃描的情況在並行多程式處理這種事務的時候有可能比單一程式處理快不了多少。因此我們可以考慮透過 dba _extends 這個檢視查詢這個表資料所在的物理塊上進行事務的切割。查詢分隔的 sql 如下：

select 'where rowid between ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||''' and ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' ||';'

   from (select distinct b.rn,   first_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,

                         last_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,

                         first_value(decode(sign(range2 - range1),

                                            1,

                                            a.bid +

                                            ((b.rn - a.range1) * a.chunks1),

                                            a.bid)) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,

                         last_value(decode(sign(range2 - range1),

                                           1,

                                           a.bid +

                                           ((b.rn - a.range1 + 1) * a.chunks1) - 1,

                                           (a.bid + a.blocks - 1))) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2

           from (select fid,

                        bid,

                        blocks,

                        chunks1,

                        trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,

                        trunc((sum2 - 0.1) / chunks1) range2

                   from (select /*+ rule */

                          relative_fno fid,

                          block_id bid,

                          blocks,

                          sum(blocks) over() sum1,

                          trunc((sum(blocks) over()) / &&rowid_ranges) chunks1,

                          sum(blocks) over(order by relative_fno, block_id) sum2

                           from dba_extents

                          where segment_name = upper('&&segment_name')

                            and owner = upper('&&owner'))

                  where sum1 > &&rowid_ranges) a,

                (select rownum - 1 rn

                   from dual

                 connect by level <= &&rowid_ranges) b

          where b.rn between a.range1 and a.range2) c,

        (select max(data_object_id) oid

           from dba_objects

          where object_name = upper('&&segment_name')

            and owner = upper('&&owner')

            and data_object_id is not null) d;

透過上述的 sql 執行時輸入使用者名稱、表名、切割片數來對一個大表進行裁剪，執行後能得出一個 rowid 的範圍值如下圖：

 

透過對刪除的 sql 中加上 rowid   的範圍刪選，能夠清晰的指定出每一個刪除任務的程式掃描物理檔案的範圍，且單一物理塊上作業的刪除程式有且僅有一個，從而減少每個程式所需要掃描的資料量和減少事務槽爭用的情況。透過上述方法順利對 1700 W 行的大表進行了資料清理工作，同時也沒有反饋作業過程中有業務卡頓的情況。

三、  r ow id 切片刪除限制

雖然基於 rowid 切片的技術可以實現將大的批次刪除動作切割成多個小事務，並減少不同併發之間的事務槽爭用問題，但並不能說明該技術沒有缺陷或者說適用於所有主流場景，下列將針對該技術的某些限制，做一下簡要說明。

1、 rowid 切片不連續問題，其實透過檢視這些 sql 不難發現這種 rowid 切片的邏輯是透過查詢表所在的 r file # 和 block # 來構造出一個 rowid ，從而指定這個 rowid 的範圍，但是中間的邏輯並非是實實在在的 rowid ，而是一個虛構的，這個虛構的過程中有些語句會在成以下的切片情況：

rowid 範圍

      切片 1 |---------------------------------------------|

      切片 2                             |------------------------------------------------|

中間的 rowid 其實是有交集的。之前就碰到的一個類似案例，是將一個大表遷移到一個分割槽表上面去，當時使用這種 rowid 的切片語句來進行加速執行，導致的問題就是表中有重複的資料，因為這個 rowid 的不連續問題，導致了有部分塊是被重複進行複製了，在新的表上面又沒有主鍵索引，導致業務上線執行了一段時間才發現這樣的問題。

2 、 rowid 切片的分割槽不支援需要切片的表其本身是分割槽表，其實透過上面的 sql 很容易就發現，這個 segment _name 就是需要切片的表名，但是如果這個表本身就是分割槽表，他的段的名字就不能是表名了。