oracle 刪除主鍵_大數據量刪除的思考 4

譯者? 湯健 · 沃趣科技數據庫技術專家

出品 ?沃趣科技

在本系列的前一期文章中，我制作了一些圖，突出顯示了按表掃描執行大量刪除操作和按索引范圍掃描執行大量刪除之間的主要區別。根據所涉及的數據模式，選擇正確的策略可能對隨機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(行級)觸發器，則不會發生這種情況。值得一提的是，索引優化也發生在索引列的值發生變化的更新上(請參閱本文)，因此留給感興趣的讀者一個練習，看看哪些(如果有的話)觸發器類型允許優化在數組更新后繼續存在。

| 譯者簡介