大資料量刪除的思考（四）

在本系列的前一期文章中，我製作了一些圖，突出顯示了按表掃描執行大量刪除操作和按索 引範圍掃描執行大量刪除之間的主要區別。 根據所涉及的資料模式，選擇正確的策略可能對隨機I/Os 的數量、生成的undo的數量和排序所需的CPU數量產生顯著影響——所有這些都可能影響執行刪除所需的時間。

然而，這個簡單的演示跟生產環境當中相比，生產環境當中這個情況更為複雜。所以，如果你面臨著一項艱鉅的任務，你需要仔細考慮如何對真正代表你要處理的系統的東西進行建模。實際上有兩種不同的情況，這一點很重要。

* 當你在處理一個非常大的一次性任務時，你需要在第一時間就把它做好，一些關鍵性的特殊情況不要發現的太遲——尤其是如果你不允許把生產系統離線來完成這個任務任務，而且你的工作期限很緊的話。

* 當你有一份常規的、但不經常發生的、非常大的工作時，有必要了解一下哪些看起來不相關的小操作可能對執行時產生很大影響;而且，瞭解下一次升級可能會出現什麼問題是值得的，這樣您就可以預先解決任何問題。

當然，後者的一個簡單例子是我對12c的簡短評論，以及它透過索引快速全掃描來驅動刪除的能力-這一功能在早期版本的Oracle中無法執行。在我的小示例中，一個測試將其執行計劃從11g的索引全掃描更改為12c的索引快速全掃描，完成所需的時間是原來的兩倍。

繼續想一想——當你試圖透過索引範圍掃描來刪除Oracle中的表或者索引時，您能想到多少事情，這可能會產生怎樣的影響？

對於一個繁忙的系統，這個建議聽起來不錯。有時候，你會發現一個長時間執行的DML語句在執行時速度非常慢，因為事實上它涉及到資料中最近的部分，因此會受到當前變化的影響;從這一點來看，Oracle發現它必須讀取undo段來獲取undo資料，這使得建立與讀取一致的資料塊版本成為可能-它需要這樣做，以便它可以檢查當前和讀取一致的版本的塊同意哪些行應該刪除。

我做的一個例子是透過“date_open”索引刪除資料-因此，如何強制索引進行降序範圍掃描，以便首先檢查最新的資料在它有很多(或任何)時間遭受其他DML的附帶損害之前?

有一個非常快捷的方法可以檢驗這個想法的有效性。所以我們要做的就是檢查排序的行數和刪除的行數我們就能知道最佳化是否發生了。

我的測試資料集有1000000行和4個索引(主鍵client_ref、date_open和date_closed索引)，所以在最好的情況下，我應該看到:“sort (rows)”= 4 *行被刪除。下面是我做的一個測試的總結，我想知道會發生什麼：

delete /*+ index_desc(t1 t1_pk) */ from t1 where id <= 5e6
5000000 rows deleted.
Name                                 Value                       
----                                 -----
sorts (rows)                            29

我們刪除了500萬行並(有效地)沒有排序。當我們按降序遍歷索引時，最佳化根本不適用—我確實檢查了執行計劃是否顯示了我所指定的“索引範圍遞減掃描”。

create index t1_dt_open on t1(date_open desc) nologging tablespace test_8k_assm_2;
delete /*+ index(t1 t1_dt_open) */ from t1 where date_open <= add_months(sysdate, -60);
4999999 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                    20,003,449

在副作用很小的範圍內，“sort (rows)”= 4 *已刪除的行:所以可以使用降序索引先嚐試刪除較新的資料——這很好，作為一個通用特性來記住可能很有用。

讓我們想象一下其他可能出錯的情況。

*我在這個表上定義了一個主鍵——但是你可以使約束延遲，或者您可以簡單地建立一個非惟一索引來保護惟一(或主鍵)約束。如果我們試圖透過主鍵索引刪除，會產生什麼影響?

*如果我們要考慮約束，我們可能要考慮外來鍵約束的影響——我們有一個client_ref列，在生產系統中，它可能是對clients表的外來鍵引用。讓我們建立這個表並新增外來鍵約束。

*當我們使事情變得更困難時——有一個眾所周知的特性將陣列處理轉換為“逐行”處理——觸發器。如果我們向表中新增行級觸發器，會產生什麼效果?什麼型別的觸發器(在之前/之後、插入/更新/刪除)有什麼區別嗎?

以下是一些結果-首先，主鍵約束的非唯一索引：

alter table t1 drop primary key;
alter table t1 add constraint t1_pk primary key(id)
deferrable initially immediate
using index nologging tablespace test_8k_assm_2
;
delete /*+ index(t1 t1_pk) */ from t1 where id <= 5e6;
5000000 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                     15,000,004

在這個例子中，Oracle將我的主鍵索引設定為非惟一，作為約束可延遲的副作用，但是即使約束不可延遲，並且您只是將索引建立為非惟一，其效果也是一樣的。統計資料告訴我們，我們已經將最佳化應用於四個索引中的三個——快速檢查一下v$segment_statistics，就會發現它是主鍵索引，沒有進行特殊處理，它受到了超過500萬個“db塊更改”的影響。在這一點上，有必要快速檢查一下，看看透過其他索引驅動是否會改變這種情況——但是不會，這是惟一約束與非惟一索引結合的副作用。

其次，當大表是“子表”時，引用完整性的影響：

create table t2 (
        client_id,
        client_name
) as
select
        distinct
                client_ref,
                rpad('x',100,'x')
from
        t1
;
alter table t2 add constraint t2_pk primary key(client_id);
alter table t1 modify client_ref not null;
alter table t1 add constraint t1_fk_t2 foreign key (client_ref) references t2(client_id);
5000000 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                    15,002,849

我們已經排序了大約1500萬行，而通常我們需要排序2000萬行.同樣，我們可以檢查v$segment_statistics來找出哪個索引遭受了500萬的損失“db block changes”你可能不會對“外來鍵”索引被逐行維護而感到驚訝-我們可能會猜測，這是某種先發制人的程式碼使得Oracle必須處理“外來鍵鎖定”威脅。 我們透過主鍵刪除這個特定測試的後續操作是，考慮如果我們透過外來鍵索引本身刪除，或者甚至將約束脩改為“on delete cascade”並刪除一些父行，將會發生什麼。透過client_ref在t1上驅動delete仍然最佳化了其他三個索引，但是當您試圖利用“on delete cascade”機制時，這個技巧根本沒有機會產生大規模的效果。在幕後你會發現這樣的事情：

delete from "TEST_USER"."T1"
where
 "CLIENT_REF" = :1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute   3000      5.23      15.37      69349       9238     428052       32510
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3003      5.23      15.37      69349       9238     428052       32510
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     0          0          0  DELETE  T1 (cr=3 pr=22 pw=0 time=9672 us)
     7          8         11   INDEX RANGE SCAN T1_CLIENT (cr=3 pr=0 pw=0 time=125 us cost=3 size=594 card=22)(object id 150589)
This output the consequence of a bulk delete of 3,000 rows from t2 – because of the “on delete cascade”, the delete operated row by row on t2 and for each row Oracle executed a delete statement against t1.

這個輸出是t2批次刪除3000行的結果——由於“ on delete cascade ”，delete在t2上逐行操作，對於每一行Oracle都對t1執行一條delete語句。

從技術上講，基於陣列的最佳化是有效的，由於索引範圍掃描，它給我們帶來了一點好處，但是資料的分散性是如此之大，以至於每次呼叫幾乎沒有給我們帶來任何好處。在某個階段，我們將不得不進一步探索這種父/子的關係。

最後是觸發器。眾所周知，行級觸發器可以將陣列處理轉換為單行處理——Oracle的索引維護最佳化也會發生同樣的事情嗎？

create or replace trigger t1_brd
before delete on t1
for each row
begin
    null;
end;
/
delete /*+ index(t1 t1_pk) */ from t1 where id <= 5e6;
5000000 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                         2,639

最佳化完全消失了。同樣的事情也會發生在“為每一行刪除後”觸發器上，但是如果觸發器是insert或update(行級)觸發器，則不會發生這種情況。值得一提的是，索引最佳化也發生在索引列的值發生變化的更新上(請參閱本文)，因此留給感興趣的讀者一個練習，看看哪些(如果有的話)觸發器型別允許最佳化在陣列更新後繼續存在。

| 譯者簡介

湯健·沃趣科技資料庫技術專家

沃趣科技資料庫工程師，多年Oracle資料庫從業經驗，深入理解Oracle資料庫結構體系，現主要參與公司一體機產品安裝、測試、最佳化，並負責電信行業資料庫以及系統運維。