舉一反三-分區裁剪作用的「新」發現

作者介紹

趙勇 雲和恩墨北區技術工程師

專註於SQL審核和優化相關工作。曾經服務的客戶涉及金融保險、電信運營商、政府、生產製造等行業。

分區裁剪的定義

分區表的實質是採用化整為零的思想，將一個大對象劃分為若干個小的對象。當數據的訪問，被限定在幾個較小的對象，甚至只發生在1個小對象上時，這時所訪問的數據量，只是原來的幾分之一，幾十分之一、幾百分之一，甚至更少。其性能較未分區時，會得到極大的提升。當發生這種情況時，我們稱之為分區裁剪（或分區消除）。

實驗論證

分別創建分區表和普通表

假設該表是用於存儲學生信息的，其上共有四列：

第一列stu_year，表示學生的入學年份；

第二列stu_no，表示學號（5位序號），每年的新生都從00001開始；

第三列stu_name，表示學生的姓名；

第四列stu_memo，表示存儲備註信息。

該表按入學年份，即stu_year列進行了分區，分區的類型為列表分區。

再創建一個同樣列信息的普通表：

向兩個表中插入模擬數據

總共5000行數據，每個學年的學生人數為1000人，且學號不重複。但整體來看，每個學號，都是5個重複值。

執行上述操作後，分區表stu_part 和普通表stu_normal中擁有了完全相同的數據。

分別在分區表和普通表查詢信息

分別在分區表stu_part 和普通表stu_normal查詢入學年份為2015年，學號為00001的學生姓名：

分區表查詢用SQL：

普通表查詢用SQL：

這裡採用了加gather_plan_statistics提示的方法來執行，並在執行後，在同一個會話窗口中，立即通過dbms_xplan.display_cursor()的方法來獲取相應的執行計劃及每一步的邏輯讀消耗。

查看執行後的計劃和統計信息

在分區表上執行後，得到的執行計劃和運行統計信息如下：

如上圖所示，我們可以看到只訪問了第4個分區，整個執行下來，消耗的邏輯讀是380個塊次。

在普通表上執行後，得到的執行計劃和運行統計信息如下：

如上圖所示，對於普通表的訪問，雖然同樣是全表掃描，但由於這裡的全表掃描，是要對5000行數據的全掃，所以，邏輯讀是1728個。而對於分區表的全表掃描，只是對其中一個分區的1000行數據進行全表掃描。

因此，發生了分區裁剪的分區表上的資源開銷是更小的。

從WHERE子句中去除分區鍵的條件

分區表查詢用SQL：

在分區表上執行後，得到的執行計劃和運行統計信息如下：

從上圖可見，由於去除了分區鍵列條件，造成對分區表的掃描是要訪問全部5個分區。可以看到此時的邏輯讀消耗是1896個塊次。

普通表查詢用SQL：

在普通表上執行後，得到的執行計劃和運行統計信息如下：

從上圖可知，仍然是全表掃描。與未去除分區鍵列條件時的情況是一樣的。邏輯讀的開銷仍然是1728個塊次。

我們可以看到，由於分區表要訪問所有的分區，其邏輯讀的開銷是高於同樣數據量的普通表的。

在stu_no學號列上創建索引

在分區表的STU_NO列上創建本地分區索引：

在分區表上執行後，得到的執行計劃和運行統計信息如下：

如上圖所示，只訪問了單個分區（即第4個分區）。而且，不僅只訪問了分區表上的第4個分區，而且，也只訪問了5個本地分區索引中的第4個。即在分區表和分區索引上，都發生了分區裁剪。由於上述原因，邏輯讀的開銷大幅下降到只有4個塊次了。

在普通表的STU_NO列上創建索引：

在普通表上執行後，得到的執行計劃和運行統計信息如下：

如上圖所示，這裡採取了對普通表上的STU_NO列上的索引的範圍掃描。其邏輯讀的開銷是8個塊次。

相比較而言，我們可以看到通過普通索引對錶訪問所需的邏輯讀開銷，比通過分區索引對錶訪問的要大。

無分區鍵列條件時的情況

在分區表上執行後，得到的執行計劃和運行統計信息如下：

如上圖所示，由於沒有了分區鍵列條件的限制，需要對全部分區進行掃描。即先對5個本地分區的索引進行掃描，根據得到的滿足學號條件的記錄的ROWID，再回表去獲取STU_NAME列的數據並返回。所以，對分區表也是全部5個分區都訪問到了。其邏輯讀的全部開銷是16個塊次。

在普通表上執行後，得到的執行計劃和運行統計信息如下：

如上圖所示，其結果與未去除分區鍵列條件時的情況是一樣的。邏輯讀的開銷是8個塊次。相較於前面分區表的開銷，普通表的開銷更小。

通過上面的測試，我們可以得到一個結論：當未發生分區裁剪時（即所有分區均要訪問時），其資源開銷要高於同樣行數的普通表和普通索引。

那麼，如果在分區表的STU_NO列上，創建一個非分區索引，還會有分區裁剪的作用嗎？

首先，我們在STU_NO列上創建非分區索引。由於此前已經在該列上創建過本地分區索引，所以，需要先把該索引刪除後，才能創建同一列上的非分區索引。

然後執行帶分區鍵列條件的SQL，得到如下的執行計劃和運行統計信息：

如上圖中紅框中內容所示，我們可知，該SQL的執行順序是先通過索引IND_STU_PART_NO_GLOBAL找到5條學號為00001的記錄（對應執行計劃中ID為2的步驟）。由於最終查詢結果是學生姓名，且還要滿足SUT_YEAR=2015這個條件，而這兩列並未在索引中出現，所以，需要通過索引中存儲的對應記錄ROWID回表，從表中獲取相應5行記錄的相關信息，並使用STU_YEAR列，過濾出等於2015的記錄（對應執行計劃中ID為1的步驟）。

同時，我們可以看到ID為2的步驟，發生了3個塊次的邏輯讀。通過前面的數據構建，我們可知，會有5條學號為00001的記錄。所以，通過索引，我們可以找到5條索引鍵值為00001的索引記錄，並得到這5條記錄的ROWID。在ID為1的步驟，通過這5個ROWID，直接訪問對應的5個數據塊。因為這5條記錄，是屬於不同的入學年份，所以，一定是在不同的5個分區中，因此，一定是5個數據塊。

此外，通過下面的操作，我們也可以驗證這5條記錄，確實屬於不同的數據塊：

加上上一步中消耗的3個塊次邏輯讀，至此，總共消耗了8（3+5）個塊次的邏輯讀。

細心或者是有經驗的同學，一定發現了這次執行的查詢中，由於在分區鍵列條件中傳入的值的數據類型（數值型）與分區鍵列的數據類型（字元型）不一致，從而發生了隱式轉換，並造成了分區鍵並未發揮出分區裁剪的作用。這一點，可以從執行計劃中謂詞信息中確認。如下圖中紅框所示：

但考慮到這裡訪問的是非分區索引，且索引列上也沒有分區鍵列，這個分區鍵列應該發揮不出什麼作用。

但我們實際驗證後，發現情況不是我們想的那樣了。如下圖所示：

從上圖中，我們可以發現，在ID為2的那一步，與之前沒有區別，仍是訪問同樣的索引，消耗的邏輯讀仍是3。但ID為1的那一步，顯示至此步止，共發生了4個塊次的邏輯讀。減去其上一步的3個塊次的邏輯讀，實際這一步，只發生了一個塊次的邏輯讀。相比於此前的寫法，整整少了4個。其原因應該是由於我們這次的寫法，沒有在分區鍵列上發生隱式轉換，使得分區鍵列發揮了作用（這一點，從Pstart和Pstop列中的數值4，也可以證明，在這次執行過程中，只訪問了第4個分區。）。

但是我的執行計劃的第一步是訪問一個非分區的索引，獲得了5條學號為00001的記錄的ROWID，由於還要用STU_YEAR列上的條件進一步過濾，以及要獲取STU_NAME列上的值，所以，需要通過ROWID回表，去獲取這兩列上的值，並進行過濾，但回表這一步只訪問了一個數據塊。

Oracle是通過什麼樣的方法，知道這五行記錄中只有一行是需要回表的。那麼它又是如何做到的呢？

經過思考，其實，由於不同的分區有不同的對象號（OBJECT_ID），ROWID又是由對象號、文件號、塊號和塊內行號所組成的。如下圖所示：

所以，在進行這一步操作時，根據條件，資料庫就知道只有相應分區內的數據才可能滿足這個條件。

通過數據字典，還可以知道這個分區的對象ID，從獲取到的5個ROWID中，可以解析出相應的對象ID。所以，屬於另外4個分區的ROWID是沒有必要回表訪問的。這也就造成索引雖然返回了5個ROWID，但只有其中1個ROWID才是可能滿足條件的，才是需要回表的。因此，也就發生了我們前邊看到的，在這一步中，只發生了1個塊次的邏輯讀。

結論

在分區表上，通過訪問非分區索引回表時，分區鍵列條件依舊有分區裁剪的作用，可以明顯減少數據訪問的開銷。

對於分區表，我們在編寫SQL時，要儘可能地把分區鍵列上的條件帶上，並正確書寫，即避免在分區鍵列上進行函數或算術運算，避免出現數據類型不一致等可能造成分區鍵列失效的情況。這對於發揮分區表上的分區裁剪作用，提升SQL性能是大有裨益的。

課程

【恩墨學院】DBA實戰進階課程

DBA實戰進階課程是恩墨學院獨自研發的課程，理論部分有恩墨學院ACE專家團隊編寫。以實操訓練為主，理論學習為輔，是恩墨學院著力打造的精品特色課程。教輔人員全程指導訓練，主講老師隨時答疑解惑，通過考試便可獲得雲和恩墨一線實習機會，表現優異者直接留用。快速跨入DBA圈子，更多展現機會，更多就業機會。

參與方式：掃描邀請函中的二維碼。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！