【導讀】Oracle數(shù)據(jù)庫中分區(qū)技術。卓望數(shù)碼技術（深圳）有限公司。版本日期作者描述。2020-3-5石義海創(chuàng)建初稿

　　

【正文】 Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 37 頁 還是 企業(yè)版 ， 如果 Oracle 數(shù)據(jù)庫 是標準版的話是不沒有分區(qū)功能的 ，所以 在考慮是否用分區(qū)表前務必了解清楚實際的系統(tǒng)數(shù)據(jù)庫版本情況 。 要確認數(shù)據(jù)庫的版本情況，最簡單的方法是連上 sql*plus 或退出就能看到版本信息，見如下的過程： 很顯然，這是 Oracle 10g 的企業(yè)版數(shù)據(jù)庫（ Enterprise Edition）。 評估 表 的 數(shù)據(jù)量 數(shù)據(jù)量不大的表沒有必要啟用分區(qū)表 ， 分區(qū)適合 那些 VLDB（ Very Large Database） 的需求 。 當然，什么樣的表才算大表呢， 這里沒有一個基線給你 去衡量表的大小 。也許隨著系統(tǒng)的 深入 運營，原本數(shù)據(jù)量不大的業(yè)務表 猛然 發(fā)覺數(shù)據(jù)量可以用千萬，億為單位去計數(shù)時 ，你就能感覺到表的數(shù)據(jù)量 上 來了，該考慮分區(qū) 分區(qū) 了。 所以啟用分區(qū)表前要好好評估一下表的數(shù)據(jù)量。 體 現(xiàn) 分區(qū)特性 在處理海量數(shù)據(jù)的大表時通過分區(qū) 特性（ Pruning 和 Wise Join） 可以 很好 提升性能 ， 當然有時我們也面臨尷尬的局面， 那就是明明已經(jīng)分區(qū)的表相反查詢速度跟不分區(qū)沒 區(qū)別 ，有時還慢， 往往出現(xiàn)這種糟糕情況是由于沒能運用上分區(qū)特性 ，這在 分區(qū)Pruning 中有 提到 到。 所以在決定啟用分區(qū)表前 先確認確認能否用得上分區(qū)特性，尤其是Pruning， 看看 應用 中跟分區(qū)表有關的 sql 語句 的 where 條件中 能 否 帶上 分區(qū)鍵來 Pruning 和Wise Join，否則啟用了分區(qū)表 將不 能給你帶來性能上的提升 。 便于 管理和維護 這一方面的考慮也是需慎重的 ，因為引入了分區(qū)后 相應多了很多獨立的對象， 相比普通表而言，在管理和維護方面是簡單還是復雜了都需要充分考慮清楚 才行。由于表分區(qū)后表上的索引等會變得不那么正常起來，會導致 unusable，數(shù)據(jù)行會遷移等等 ，這些問題我們在啟用分區(qū)表時需考慮到的。 不過可以放心的是分區(qū)后在處理海量數(shù)據(jù)的大表時往往使得管理和維護都趨向簡單和方便，這在前面討論分區(qū)的三大優(yōu)勢時已經(jīng)體現(xiàn)。 應用系統(tǒng)類型 應用系統(tǒng)類型有很多，像在線事務系統(tǒng) OLTP、數(shù)據(jù)倉庫 DW、商業(yè)智能BI、決策支持系統(tǒng) DSS等，針 對不同的系統(tǒng)我們在考慮分區(qū)表時側重點 和使用頻率 可能不太一樣， 像 DW 中很普通使用分區(qū)表來聚合 數(shù)據(jù)集 和生成 統(tǒng)計日期中的 報表 ，而往往 OLTP 中不是很普遍使用 ，主要是由于這種系統(tǒng)有大量并發(fā)的事務需極短的時間內(nèi)完成， 靠的往往是索引，由于分區(qū)不會讓索引訪問速度上有更高的提升，并且 關鍵業(yè)務表分區(qū)后 應用不一定能使用上分區(qū)特性， 即使能使用上有時也不見到能 提升多大的性能 ， 這是 因為基于數(shù)據(jù)的實時分布情況不能很好利用上分區(qū)特性 。 分區(qū) 類型選擇 一旦我們確定下來要啟用分區(qū)表，緊接著需要考慮的一個策略是該采取什么類型的分區(qū)，Range的， List 的， Hash的還是組合形式。 通過 分區(qū)其實很好選擇， 分區(qū)鍵值發(fā)布在區(qū)域的就選擇 Range 分區(qū)，離散的分區(qū)鍵值的話就選擇List， 實在無法區(qū)分的就用 Hash 吧。 至于什么時候要選擇組合形式的分區(qū)那得看 如下的情況： 第一層分區(qū)后的分區(qū)表還是海量數(shù)據(jù)還需進一步“化整為 0”； 應用中可提供分區(qū)的鍵值不僅僅一個， 并且都能使用上分區(qū)特性（ Pruning 和 Wise Join）； 對于上述情況， 選擇組合分區(qū)是最好的選擇。 Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 38 頁 分區(qū) 粒度選擇 實際運用中很多分區(qū)都是建立在日期型 分區(qū)鍵 的 Range分區(qū)上， 分區(qū)時我們可以按小時、天、周、月等等跨度來分區(qū)， 至于該選擇什么樣的分區(qū)粒度需依據(jù)分區(qū) 上數(shù)據(jù)量的情況 和便于管理 來 決定 。 分區(qū)粒度越?。ò刺靵矸謪^(qū)，按小時來分區(qū)）是否性能越差？這個問題的回答 得先考慮考慮系統(tǒng)按天按小時來分區(qū)所帶來的優(yōu)勢才行。 在 Oracle 數(shù)據(jù)庫中 ，一個分區(qū)表可以支持的分區(qū)個數(shù)（ 65535）應該足夠系統(tǒng)的擴展，況且系統(tǒng)也沒必要保持那么多的表分區(qū)存在。 總之，分區(qū)粒度的選擇是建立在業(yè)務應用表數(shù)據(jù)量的時間發(fā)布上的。 分區(qū)索引 的 權衡 使用 在前面的章節(jié) 中我們已經(jīng)了解到跟分區(qū)有關的索引 有很多種類 ， 在考慮分區(qū)策略時 我們要權衡使用好分區(qū)索引才行。 如下的幾點是我們需考慮到的： 要慎重給索引分區(qū)，因為 有時 即使對索引進行了分區(qū)，就算是真的能在速度上有所提高，通過掃描較小索引所得到的性能提升也是微乎其微的 ； 若查詢使用到了索引，由于索引根本無法消除任何分區(qū)，這就使得需掃描更多的分區(qū)本地索引而不是一個較大的全局索引，這時查詢反而變慢了 ； 在對分區(qū)表進行管理和維護時可能導致分區(qū)索引 unusable，進而導致業(yè)務表的訪問失效。 6 案例分享 在本指導手冊的最后章節(jié)將通過兩個具體的案例來 跟大家分享 在 Oracle 數(shù)據(jù)庫中分區(qū)技術的靈活運用。 這兩個具體的案例 都 很 有代表性，分別體現(xiàn)著在線事務系統(tǒng)和數(shù)據(jù)倉庫系統(tǒng) ，在案例的整個場景中將綜合運用到分區(qū)的很多知識來實現(xiàn)場景目標。 第一 個案例： 在線事務系統(tǒng) 場景描述 除去 一些細節(jié)處理和 跟場景宗旨 不相關的內(nèi)容， 這個案例 場景 的大體 描述 是這樣的： 電信領域中的系統(tǒng)有 一 短信 下發(fā)模塊， 由于 下發(fā)模塊的應用 服務器 內(nèi)存 有限 和下發(fā)特定時間的限制 需將 應用中積壓的 待下發(fā) msg信息 或未到特定下發(fā)時間的 msg信息 暫時緩存到 數(shù)據(jù)庫表中 ， 當下發(fā)模塊不忙 或 特定 下發(fā)時間到來時 再從表中取出 符合條件的 待下發(fā) msg 信息 來 做處理后 下發(fā) 。 下發(fā)過后的 Msg信息 將 在數(shù)據(jù)庫中保留 1 周后才可被 清理 掉 ，未下發(fā)的 msg 信息若超過了一定期限 將不被下發(fā) 。 每天通過數(shù)據(jù)庫表來緩存的 msg 信息量 在 100萬 ～ 200 萬 之間 。 該表結構大致 主要 字段 如下所示： create table msg_database_cache ( Id number(12) not null, hashkey varchar2(100) not null, Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 39 頁 msg_content varchar2(3000) not null, msg_status varchar2(2) not null, createdate date default sysdate ) 其中 hashkey 是 主鍵列 ， createdate 是 msg 信息入庫的時間 ， 涉及到該表的操作 中 增刪改查都有， 應用 檢索數(shù)據(jù) 時主要通過 hashkey 列 和 createdate 列 上的 條件來獲取 。 模型設計 結合場景的業(yè)務模型可考慮將表 msg_database_cache 設計為按天的分區(qū)表， 數(shù)據(jù)清理通過 drop 分區(qū)的方式來完成，數(shù)據(jù)的刪改查通過引入 createdate 列上的 where條件來使用 分區(qū)特性 Partition Pruning 從而提升檢索性能， 分區(qū)的創(chuàng)建由系統(tǒng)自動來維護，主鍵列上的索引可定期在系統(tǒng)空閑時并行重建來完成。 下面是這個案例的 腳本 創(chuàng)建語句： 分區(qū)表和索引的創(chuàng)建 動態(tài)創(chuàng)建分區(qū)表， 分區(qū)日期 根據(jù)當前時間來動態(tài)創(chuàng)建，見下面的執(zhí)行語句： 分區(qū) 的 維護 首先創(chuàng)建分區(qū)維護日志表，創(chuàng)建語句如下： Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 40 頁 如下創(chuàng)建存儲包 package_manager_partition，用來管理分區(qū)表的新分區(qū)創(chuàng)建和舊分區(qū)清理，執(zhí)行語句如下： Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 41 頁 Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 42 頁 Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 43 頁 Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 44 頁 第二個案例：數(shù)據(jù)倉庫 場 景描述 在數(shù)據(jù)倉庫的 ETL 模塊中往往需將大量的外部凈化過后的數(shù)據(jù)文件裝載入數(shù)據(jù)庫中供聚合，這些裝載的數(shù)據(jù)一般很有時間性，隨著時間的推移向倉庫中增加數(shù)據(jù)到新分區(qū)，而最舊的數(shù)據(jù)會老化?，F(xiàn)狀表用來裝載新數(shù)據(jù)，而歷史表用來存放老化的數(shù)據(jù)，這些機制在數(shù)據(jù)倉庫中用來處理周報、月報等時是很有用的。比如每月的 ETL 處理過程中會涉及到如下的處理單元： 老化數(shù)據(jù)：對應最舊數(shù)據(jù)的分區(qū)要么被刪除（ drop 或 truncate），要么與空表交換（ exchange），將最舊的分區(qū)變成表并且對其歸檔； 裝載數(shù)據(jù)并建立索引：將現(xiàn)狀數(shù)據(jù)裝載到 現(xiàn)狀表中并建立索引； 關聯(lián)新數(shù)據(jù)：當裝載并處理完了現(xiàn)狀數(shù)據(jù)，將現(xiàn)狀表跟分區(qū)表中對應現(xiàn)狀日期的空分區(qū)進行交換，將現(xiàn)狀表中的新裝載數(shù)據(jù)變?yōu)榉謪^(qū)表中的一個分區(qū)。 在這個案例中， 我們以按天的分區(qū)表 partition_range為例子來進行闡述 ， 假設該分區(qū)表是數(shù)據(jù)倉庫中的一個日明細表 ，隨著日期的推移，該表的數(shù)據(jù) 也發(fā)生偏移， 每天程序都發(fā)生舊數(shù)據(jù)的老化和現(xiàn)狀日數(shù)據(jù)的裝載。 Oracle 數(shù)據(jù)庫中分區(qū)技術指導手冊 第 45 頁 分區(qū)表和索引的創(chuàng)建 同 節(jié) Range分區(qū)中的創(chuàng)建語句。 當然數(shù)據(jù)倉庫中一般不需要 創(chuàng)建 索引 ，如果需要索引的話我們需在滑入新數(shù)據(jù)后對索引重建才行。 老化舊數(shù)據(jù) 我們采用與空表交換的方式來老化歷史數(shù)據(jù) ，將老化的歷史日數(shù)據(jù)歸檔起來 ，假設空表名稱為 data_yyyymmdd_h，這里的例子類似 節(jié) exchange 分區(qū)中的 exchange操作內(nèi)容 ，把表 T_P_20200229 替換成 data_yyyymmdd_h 就可以了。歸檔我們可以采用 expdp 或 rman中備份特定表空間的方式來完成 。 裝載新數(shù)據(jù) 假設現(xiàn)狀表的每天數(shù)據(jù)都裝載到 temp_data_d 中，我們可以通過 sqlldr 工具來加載數(shù)據(jù)或通過 Oracle 10g 中 externel table 的 dbms_datadump 來實現(xiàn) 加載 。 滑入新數(shù)據(jù) 滑入新數(shù)據(jù)的過程類似老化舊數(shù)據(jù)的操作，也是通過 exchange 的方式來實現(xiàn)的，見 節(jié) exchange 分區(qū)中的 exchange 的部分操作 ，把表 T_P_20200229 替換成 temp_data_d 即可。 參考文檔： Oracle Database VLDB and Partitioning Guide 11g Release 1 () Oracle9i Database： Implement Partitioning （ Student Guide） Oracle 百皮書： Oracle 數(shù)據(jù)庫 10g 中的分區(qū)功能 Oracle Database Administrator39。s Guide 10g Release 2 () Oracle Database New Features Guide 10g Release 2 () Oracle Database SQL Reference(10g Release 2) Oracle Database Data Warehousing Guide 10g Release 2 ()