怎樣理解Oracle分區(qū)表

這篇文章將為大家詳細講解有關怎樣理解Oracle分區(qū)表，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

古藺網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)！從網(wǎng)頁設計、網(wǎng)站建設、微信開發(fā)、APP開發(fā)、成都響應式網(wǎng)站建設公司等網(wǎng)站項目制作，到程序開發(fā)，運營維護。創(chuàng)新互聯(lián)成立于2013年到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設就選創(chuàng)新互聯(lián)。

一：什么是分區(qū)（Partition）？

分區(qū)是將一個表或索引物理地分解為多個更小、更可管理的部分。

分區(qū)對應用透明，即對訪問數(shù)據(jù)庫的應用而言，邏輯上講只有一個表或一個索引（相當于應用“看到”的只是一個表或索引），但在物理上這個表或索引可能由數(shù)十個物理分區(qū)組成。

每個分區(qū)都是一個獨立的對象，可以獨自處理，也可以作為一個更大對象的一部分進行處理。

--------------------------Tips：分表與分區(qū)表--------------------------

分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表（子表）；

比如一個訂單表 ORDER，采用年月分表后可能就會除 ORDER 本身外還生成許多如 ORDER_201601、ORDER_201602、ORDER_201603… 等的子表。

分表在邏輯上是多張不同的表，而分區(qū)表在邏輯上是一張表。

--------------------------------------------------------------------

二：什么時候需要分區(qū)？

來自官網(wǎng)的兩個建議：

1. Tables greater than 2GB should always be considered for partitioning.（表數(shù)據(jù)量大于2GB時應該考慮使用分區(qū)）

2. Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.（新數(shù)據(jù)均加入至最新分區(qū)中的用于存儲歷史數(shù)據(jù)的表）

三：分區(qū)帶來的好處

1. 提高數(shù)據(jù)可用性

a) 得益于每個分區(qū)的獨立性，優(yōu)化器會在查詢時有需要的去除未用到的分區(qū)（這也叫消除分區(qū)）

比如：一個查詢如果只用到了一個表三個分區(qū)中的其中一個分區(qū)的數(shù)據(jù)，那么Oracle在執(zhí)行這個查詢時只會掃描用到的這個分區(qū)的數(shù)據(jù)，不會掃描其他兩個分區(qū)的數(shù)據(jù)。

這在OLAP系統(tǒng)中很有用。

-----------------------延伸閱讀：OLTP與OLAP系統(tǒng)---------------------

OLTP（On-Line Transaction Processing）：

聯(lián)機事務處理過程，也稱為面向交易的處理過程，其基本特征是前臺接收的用戶數(shù)據(jù)可以立即傳送到計算中心進行處理，并在很短的時間內給出處理結果，實現(xiàn)對用戶操作的快速響應；

這樣的系統(tǒng)事務性要求非常高，一般都是高可用的在線系統(tǒng)，以小的事務以及小的查詢?yōu)橹?。評估其系統(tǒng)的時候，一般看其每秒執(zhí)行的 Transaction 以及 Execute SQL 的數(shù)量。單個數(shù)據(jù)庫每秒處理的 Transaction 往往超過幾百個或是幾千個，Select 語句的執(zhí)行量每秒幾千甚至幾萬個；

OLTP是傳統(tǒng)的關系型數(shù)據(jù)庫的主要應用，典型的OLTP系統(tǒng)有電子商務系統(tǒng)、銀行、證券系統(tǒng)等。

OLAP（On-Line Analytical Processing）：

聯(lián)機分析處理，是數(shù)據(jù)倉庫系統(tǒng)的主要應用，所謂數(shù)據(jù)倉庫是對于大量已經(jīng)由OLTP形成的數(shù)據(jù)的一種分析型的數(shù)據(jù)庫，用于處理商業(yè)智能、決策支持等重要的決策信息；

數(shù)據(jù)倉庫是在數(shù)據(jù)庫應用到一定程度之后而對歷史數(shù)據(jù)的加工與分析，讀取較多、更新較少；

----------------------------------------------------------------------

b) 分區(qū)還可以通過減少停機時間來提高可用性

例如：一個100GB的表，中間的數(shù)據(jù)如果遭到損壞，那么恢復起來簡直讓人抓狂。

如果這100GB的表被劃分為了50個2GB的分區(qū)，當其中某個分區(qū)數(shù)據(jù)遭到破壞時，只需要恢復一個2GB的分區(qū)數(shù)據(jù)即可。

出現(xiàn)錯誤時的停機時間將會大大減少，因為恢復所需的工作量大幅減少。

2. 方便管理

將一個大的對象分解為數(shù)個小對象，操作這些小對象明顯比直接操作原來的大對象更加容易，且占用的資源也更少。

3. 改善語句性能（多針對OLAP系統(tǒng)）

a) 并行DML（Parallel DML）：

在 Oracle 9i 以前的版本中，PDML（Parallel DML）要求必須分區(qū)；

9i 及以后的版本中這個限制已經(jīng)放松，只有兩個例外：

① 希望在一個表上執(zhí)行 PDML，而且這個表的一個 LOB列上有一個位圖索引，要并行執(zhí)行操作就必須對這個表分區(qū)；

② 對于并行訪問分區(qū)操作，取需要訪問的分區(qū)數(shù)為并行度

---------------------延伸閱讀：PDML（Parallel DML）---------------------

什么是Parallel（并行）技術？

對于一個大的任務，一般的做法是利用一個進程，串行的執(zhí)行。

但如果系統(tǒng)資源足夠，可以采用Parallel（并行）技術，把一個大的任務分成若干個小的任務，同時啟用N個進程（或線程），并行的處理這些小的任務，這些并發(fā)的進程稱為并行執(zhí)行服務器(parallel executeion server)，它們統(tǒng)一由一個稱為并發(fā)協(xié)調進程的進程來管理。

注意：

只有在需要處理一個很大的任務（如需要幾個小時的作業(yè)），并且要有足夠的系統(tǒng)資源（包括CPU、內存、I/O等）的情況下，才應該考慮使用Parallel技術。

否則，在一個多并發(fā)用戶環(huán)境下，系統(tǒng)本身資源負擔已經(jīng)很大，啟用Parallel的話，將會導致某一個會話試圖占用所有的資源，其他會話不得不等待，從而導致系統(tǒng)性能反而下降的情況。

一般情況下，OLTP系統(tǒng)中不要使用Parallel技術，OLAP系統(tǒng)中可以考慮使用。

PDML分類：

  • Parallel Query（并行查詢）

  • Parallel DML（并行DML語句執(zhí)行）

  • Parallel DDL（并行DDL語句執(zhí)行）

并行查詢：并行查詢允許將一個select語句劃分為多個較小的查詢，每個部分的查詢都并發(fā)地運行，然后將各個部分的結果組合起來，提供最終的結果。（多用于全表掃描，索引全掃描等）

并行DML：Parallel DML包括 insert、update、delete、merge，在PDML期間，Oracle可以使用多個并行執(zhí)行服務器（即并發(fā)進程）來執(zhí)行 insert、update、delete、merge，多個會話同時執(zhí)行，同時每個會話(并發(fā)進程)都有自己的undo段，都是一個獨立的事務，這些事務要么都由并發(fā)協(xié)調進程提交，要么都rollback。

-----------------------------------------------------------------------

b) 查詢性能：

分區(qū)對于不同的系統(tǒng)帶來的影響可能不同；

對OLTP系統(tǒng)而言，需要謹慎使用分區(qū)操作，因為在傳統(tǒng)的OLTP系統(tǒng)中，大多數(shù)查詢很可能立即返回結果，而且獲取大多數(shù)數(shù)據(jù)可能都通過一個很小的索引區(qū)間掃描來完成。故分區(qū)帶來的性能方面的優(yōu)點在 OLTP 系統(tǒng)中可能根本表現(xiàn)不出來。

在一個OLTP系統(tǒng)中，分區(qū)如果應用不當，甚至可能使性能下降（分區(qū)可能會提高某些類型查詢的性能，但是這些查詢通常不在OLTP系統(tǒng)中使用）；

所以有一點你必須明白：分區(qū)并不總是和“性能提升”聯(lián)系在一起。

對于OLAP系統(tǒng)而言，分區(qū)消除與并行查詢將可能帶來效率的大幅提升。

四：表分區(qū)機制

表分區(qū)的四種類型：

  • 范圍分區(qū)（Range）

  • 散列分區(qū)（Hash）

  • 列表分區(qū)（List）

  • 組合分區(qū)（Range – Hash   或者   Range - List）

1. 范圍分區(qū)：

范圍（Range）分區(qū)將數(shù)據(jù)基于指定的分區(qū)鍵映射到每一個分區(qū)中。

這種分區(qū)方式最為常用，且常常采用日期作為分區(qū)鍵。

注意：

① 每一個分區(qū)都需要有一個 VALUES LESS THEN 子句，它指定了該分區(qū)的上限值（即該分區(qū)能接受的分區(qū)鍵的最大值）。記錄里分區(qū)鍵的值小于這個上限值時，該記錄會被放入該分區(qū)；而當記錄里分區(qū)鍵的值等于或大于這個上限值時該記錄會被放入下一個上限值更高的分區(qū)中。

② 所有分區(qū)里，除了第一個分區(qū)，其他分區(qū)其實都有一個隱式的下限值（即該分區(qū)能接受的分區(qū)鍵的最小值），這個下限值就是上一個分區(qū)的上限值。

③ 在最后一個分區(qū)中，可定義上限值為 MAXVALUE（該值可理解為所有分區(qū)中的一個最大上限值，包括空值），當記錄分區(qū)鍵的值大于之前所有分區(qū)的上限值時，這條記錄會被放入這最后一個分區(qū)中。

建表語句示例：

/****************************************************范圍分區(qū)示例******************************************/ --創(chuàng)建示例表 create table range_example
(
 id number(2),
 done_date date,
 data varchar2(50)
) --創(chuàng)建分區(qū)，分區(qū)鍵為示例表（range_example）中的 done_date 字段 partition by range (done_date)
(
  partition part_1 values less than ( to_date('20160901', 'yyyymmdd') ),
  partition part_2 values less than ( to_date('20161001', 'yyyymmdd') ),
  partition part_3 values less than ( maxvalue )
) --查看range_example表的分區(qū)信息 select * from user_tab_partitions where table_name = 'RANGE_EXAMPLE';

查看表分區(qū)信息

插入數(shù)據(jù)：可以看到示例表 range_example 已經(jīng)分了三個區(qū)。

記錄1的 done_date 為 2016/8/11，小于分區(qū)part_1的上限值，則記錄1會被放入part_1分區(qū)；

記錄2的 done_date 為 2016/9/8 ，大于分區(qū)part_1的上限值但小于part_2的上限值，則記錄2會被放入part_2分區(qū)；

記錄3的 done_date 為 2016/10/20，大于前兩個分區(qū)的上限值，故會被放入最后一個maxvalue的分區(qū)（part_3）；

part_2的隱式的下限值實際就是上一個分區(qū)part_1的上限值；

2. 散列分區(qū)：

對一個表執(zhí)行散列分區(qū)時，Oracle會對分區(qū)鍵應用一個散列（Hash）函數(shù)，以此確定數(shù)據(jù)應當放在 N 個分區(qū)中的哪一個分區(qū)中。

Oracle建議 N 是 2 的一個冪（如 N = 2、4、8、16 等），從而使表數(shù)據(jù)得到最佳的總體分布。

當列的值沒有合適的范圍條件時，建議使用散列分區(qū)。

注意：

如果改變散列分區(qū)的個數(shù) （向一個散列分區(qū)表增加或刪除一個分區(qū)時），數(shù)據(jù)會在所有分區(qū)中重新分布，即所有數(shù)據(jù)都會被重寫，因為現(xiàn)在每一行可能屬于一個不同的分區(qū)。

為表選擇的散列鍵（分區(qū)鍵）應當是惟一的一個列或一組列（該列應有多個不同的值），以便行能在多個分區(qū)上均勻地分布。

如果使用散列分區(qū)，你將無法控制一行數(shù)據(jù)最終會放在哪個分區(qū)中（由散列函數(shù)控制）。

建表語句示例：

/*******************************************散列分區(qū)示例***********************************************/ --創(chuàng)建示例表 create table hash_example
(
 id number(2),
 done_date date,
 data varchar2(50)
) --創(chuàng)建散列分區(qū)，分區(qū)鍵為示例表（hash_example）中的 done_date 字段 partition by hash (done_date)
(
  partition part_1,
  partition part_2
) select * from user_tab_partitions where table_name = 'HASH_EXAMPLE';

3. 列表分區(qū)：

列表分區(qū)可以根據(jù)分區(qū)鍵的值明確指定哪些值的數(shù)據(jù)該放在哪個分區(qū)。

注意：

列表分區(qū)中如果指定了 default 分區(qū)，則分區(qū)鍵的值不在任何分區(qū)值列表中的記錄，會被放入 default 分區(qū)；

而一旦創(chuàng)建了一個 default 分區(qū)后，就不能再向這個表中增加更多的分區(qū)了；

如果未指定 default 分區(qū)，則在插入分區(qū)鍵值不在任何分區(qū)值列表中的記錄時，Oracle會報錯（ORA-14400: inserted partition key does not map to any partition）。

建表語句示例：

/*******************************************列表分區(qū)示例***********************************************/ --創(chuàng)建示例表 create table list_example
(
 id number(2),
 name varchar(30),
 data varchar2(50)
) --創(chuàng)建列表分區(qū)，分區(qū)鍵為示例表（list_example）中的 id 字段 partition by list (id)
(
  partition part_1 values ( '1', '3', '5', '7' ),
  partition part_2 values ( '2', '4', '6', '8' ),
  partition part_default values ( default )
) select * from user_tab_partitions where table_name = 'LIST_EXAMPLE';

分區(qū)信息：

如上，分區(qū)鍵（即list_example表中id字段）值為 1、3、5、7 的記錄，會被放入part_1分區(qū)；

分區(qū)鍵值為 2、4、6、8 的記錄，會被放入part_2分區(qū)；

分區(qū)鍵值為其他值的記錄，會被放入最后一個part_default分區(qū)。

4. 組合分區(qū)：

組合分區(qū)是范圍分區(qū)與散列分區(qū)的組合，或者是范圍分區(qū)與列表分區(qū)的組合。

在組合分區(qū)中，頂層分區(qū)機制總是范圍分區(qū)，第二級分區(qū)機制可能是散列分區(qū)也可能是列表分區(qū)；

數(shù)據(jù)物理的存儲在子分區(qū)段上，分區(qū)（頂層的范圍分區(qū)）成為了一個邏輯容器，或者是一個指向實際子分區(qū)的容器；

每個頂層分區(qū)不需要有相同數(shù)目的子分區(qū)。

范圍-散列組合分區(qū) 建表語句示例：

/******************************************范圍-散列分區(qū)**************************************/ create table range_hash_example
(
 id number(2),
 done_date date,
 data varchar2(50)
) --頂層范圍分區(qū)的分區(qū)鍵為 range_hash_example 表中的 done_date 字段； --第二層散列分區(qū)的分區(qū)鍵為 range_hash_example 表中的 id 字段； partition by range (done_date) subpartition by hash (id) 
(
  partition part_1 values less than ( to_date('20160901', 'yyyymmdd') )
  (
    subpartition part_1_sub_1,
    subpartition part_1_sub_2
  ),
  
  partition part_2 values less than ( to_date('20161001', 'yyyymmdd') )
  (
    subpartition part_2_sub_1,
    subpartition part_2_sub_2
  ),
  
  partition part_3 values less than ( maxvalue )
  (
    subpartition part_3_sub_1,
    subpartition part_3_sub_2
  )
) select * from user_tab_partitions where table_name = 'RANGE_HASH_EXAMPLE';

分區(qū)信息：

在如上的范圍-散列組合分區(qū)中，Oracle會首先應用范圍（Range）分區(qū)規(guī)則，得出數(shù)據(jù)屬于哪個區(qū)間，（即先通過 done_date 字段確定記錄是屬于part_1還是part_2還是part_3）；

然后再應用散列（Hash）函數(shù)，來確定數(shù)據(jù)最后要放在哪個子分區(qū)（物理分區(qū)）中，（即通過 id 字段確定記錄是屬于一個分區(qū)下的哪個子分區(qū)中 ）

范圍-列表組合分區(qū) 建表語句示例：

/******************************************范圍-列表分區(qū)**************************************/ create table range_list_example
(
 id number(2),
 done_date date,
 data varchar2(50)
) --頂層范圍分區(qū)的分區(qū)鍵為 range_list_example 表中的 done_date 字段； --第二層列表分區(qū)的分區(qū)鍵為 range_list_example 表中的 id 字段； partition by range (done_date) subpartition by list (id) 
(
  partition part_1 values less than ( to_date('20160901', 'yyyymmdd') )
  (
    subpartition part_1_sub_1 values ( '1', '3', '5' ),
    subpartition part_1_sub_2 values ( '2', '4', '6' )
  ),
  
  partition part_2 values less than ( to_date('20161001', 'yyyymmdd') )
  (
    subpartition part_2_sub_1 values ( '11', '13', '15', '17' ),
    subpartition part_2_sub_2 values ( '12', '14' ),
    subpartition part_2_sub_3 values ( '16', '18' )
  ),
  
  partition part_3 values less than ( maxvalue )
  (
    subpartition part_3_sub_1 values ( '21', '23', '25' ),
    subpartition part_3_sub_2 values ( '22', '24', '26' )
  )
) select * from user_tab_partitions where table_name = 'RANGE_LIST_EXAMPLE';

分區(qū)信息：

如圖，每個頂層的范圍分區(qū)可以有不同數(shù)目的子分區(qū)。

5. 小結

一般來講，如果需要將數(shù)據(jù)按照某個值邏輯聚集，多采用范圍分區(qū)。如基于時間數(shù)據(jù)的按“年”、“月”等分區(qū)就是很典型的例子。在許多情況下，范圍分區(qū)都能利用到分區(qū)消除特性（ =  >=  <=  between…and  等篩選條件下）。

如果在表里無法找到一個合適的屬性來按這個屬性完成范圍分區(qū)，但你又想享受分區(qū)帶來的性能與可用性的提升，則可以考慮使用散列分區(qū)。（適合使用 = IN 等篩選條件）

如果數(shù)據(jù)中有一列或有一組離散值，且按這一列進行分區(qū)很有意義，則這樣的數(shù)據(jù)就很適合采用列表分區(qū)。

如果某些數(shù)據(jù)邏輯上可以進行范圍分區(qū)，但是得到的范圍分區(qū)還是太大，不能有效管理，則可以考慮使用組合分區(qū)。

注意：

分區(qū)在最開始創(chuàng)建表時被一同創(chuàng)建，如果后期要更改分區(qū)策略的話，需要先重建表。

---------------------------延伸閱讀：自動遞增（自增）分區(qū)---------------------------

前面說到基于時間數(shù)據(jù)的按“年”、“月”進行的典型的范圍分區(qū)例子，這里再補充一個應用場景：

假如有一張商品銷售記錄表（products_table），其中簡單記錄著商品的id號，名稱，銷售時間；

當按照銷售時間進行范圍分區(qū)時，因為表里的記錄是不斷增加的（每賣出一個商品就會增加一條記錄），這時候就可以考慮創(chuàng)建自增分區(qū)；

顧名思義的，當有新記錄插入時，Oracle會根據(jù)需要自動增加新分區(qū)來存儲新記錄（當新插入的記錄里的分區(qū)鍵的值不在任何已有分區(qū)范圍內時，Oracle會自動創(chuàng)建一個新的分區(qū)）

你可以根據(jù)需要來指定自增分區(qū)的自動遞增策略，比如按天自增、按周自增、按月自增、按年自增等等（具體自增語句百度一下即可知道）；

商品銷售記錄表創(chuàng)建按月自增的范圍分區(qū)示例：

create table products_table
(
 id number(2),
 name varchar2(50),
 sale_date date
)
partition by range(sale_date)
  interval (numtoyminterval(1,'month'))
  (
    partition p_month_1 values less than (to_date('2016-01-01','yyyy-mm-dd'))
  )

如圖，取 products_table 中的 sale_date 列作為分區(qū)鍵創(chuàng)建按月自增分區(qū)；

所有銷售時間在 ‘2016-01-01’之前的記錄都會被放入 p_month_1 分區(qū)；

銷售時間在‘2016-01-01’之后的記錄在插入時Oracle會自動創(chuàng)建記錄所屬月的分區(qū)；

比如當有銷售時間分別為 2016年1月20日 與 2016年2月20日 的兩條記錄插入時，Oracle會分別創(chuàng)建一個上限值為 ‘2016-01-31’的分區(qū)和一個上限值為‘2016-02-29’的分區(qū)來存儲這兩條記錄

關于怎樣理解Oracle分區(qū)表就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

本文名稱：怎樣理解Oracle分區(qū)表
網(wǎng)頁鏈接：http://bm7419.com/article8/pcijip.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設、用戶體驗、Google、外貿建站、營銷型網(wǎng)站建設、軟件開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)

手機網(wǎng)站建設