Oracle分區(qū)表詳細(xì)講解.ppt

1、Oracle數(shù)據(jù)庫的表分區(qū),分區(qū)表的由來,當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應(yīng)用程序的性能就會下降，這時就應(yīng)該考慮對表進(jìn)行分區(qū)。表進(jìn)行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上)，這樣查詢數(shù)據(jù)時，不至于每次都掃描整張表。 從應(yīng)用程序的角度來看，分區(qū)后的表與非分區(qū)表完全相同，使用 SQL DML 命令訪問分區(qū)后的表時，無需任何修改。,分區(qū)表概述,大數(shù)據(jù)對象（表、索引）被分成小物理段 當(dāng)分區(qū)表建立時，記錄基于分區(qū)字段值被存儲到相應(yīng)分區(qū) 分區(qū)字段值可以修改.(row movement enabled) 分區(qū)可以存儲在不同的表空間 分區(qū)可以有

2、不同的物理存儲參數(shù) 分區(qū)支持IOT表、對象表、LOB字段Varrays等,什么時候使用分區(qū)表,表的大小：當(dāng)表的大小超過2GB，或?qū)τ贠LTP系統(tǒng)，表記錄超過1000萬時，都應(yīng)該考慮對表進(jìn)行分區(qū) 數(shù)據(jù)訪問特性：基于表的大部分查詢應(yīng)用，只訪問表中少量的數(shù)據(jù)。對于這樣的表進(jìn)行分區(qū)，可充分利用分區(qū)排除無關(guān)數(shù)據(jù)查詢的特性 數(shù)據(jù)維護(hù)：按時間段刪除成批的數(shù)據(jù)，對于這樣的表需要考慮進(jìn)行分區(qū)，以滿足維護(hù)需要,性能 Select和DML只訪問指定分區(qū),提高檢索速度 并行DML操作 Partitionwise join 可管理性 歷史數(shù)據(jù)清除 提高備份性能 指定分區(qū)的數(shù)據(jù)維護(hù)操作 可用性 將故障局限在分區(qū)中 縮短恢

3、復(fù)時間 分區(qū)目標(biāo)優(yōu)先級 高性能-數(shù)據(jù)維護(hù)能力-實(shí)施難度-高可用性(故障屏蔽能力）,使用分區(qū)表的好處,分區(qū)表的分類,1、范圍分區(qū) (Range) 2、列表分區(qū) (List) 3、散列分區(qū) (Hash) 4、組合范圍散列分區(qū) 5、復(fù)合范圍散列分區(qū),范圍分區(qū),范圍分區(qū)將數(shù)據(jù)基于范圍映射到每一個分區(qū)，這個范圍是你在創(chuàng)建分區(qū)時指定的分區(qū)鍵決定的。這種分區(qū)方式是最為常用的，并且分區(qū)鍵經(jīng)常采用日期。如：將銷售數(shù)據(jù)按照月份進(jìn)行分區(qū),范圍分區(qū)時的規(guī)則,使用范圍分區(qū)時，需要注意以下幾個規(guī)則 1、每一個分區(qū)都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區(qū)中的上限值。分區(qū)鍵的任何值等于或者

4、大于這個上限值的記錄都會被加入到下一個高一些的分區(qū)中。 2、所有分區(qū)，除了第一個，都會有一個隱式的下限值，這個值就是此分區(qū)的前一個分區(qū)的上限值。 3、在最高的分區(qū)中定義了MAXVALUE。MAXVALUE代表了一個不確定的值。這個值高于其它分區(qū)中的任何分區(qū)鍵的值，也可以理解為高于任何分區(qū)中指定的VALUE LESS THEN的值，同時包括空值。,范圍分區(qū)例1,假設(shè)有一個CUSTOMER表，表中有數(shù)據(jù)200000行，我們將此表通過CUSTOMER_ID進(jìn)行分區(qū)，每個分區(qū)存儲100000行，我們將每個分區(qū)保存到單獨(dú)的表空間中，這樣數(shù)據(jù)文件就可以跨越多個物理磁盤,CREATE TABLE CUSTO

5、MER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART

6、2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 ),按時間劃分 CREATE TABLE ORDER_ACTIVITIES ( ORDER_ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE(01- MAY -2003,DD-MON-

7、YYYY) TABLESPACEORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(01-JUN-2003,DD-MON-YYYY) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(01-JUL-2003,DD-MON-YYYY) TABLESPACE ORD_TS03 ),MAXVALUE CREATE TABLE RangeTable( idd INT PRIMARY KEY , iNAME VARCHAR(10), grad

8、e INT ) PARTITION BY RANGE (grade) ( PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb, PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb );,范圍分區(qū)的特點(diǎn),最早、最經(jīng)典的分區(qū)方法 Range分區(qū)通過對分區(qū)字段值的范圍進(jìn)行分區(qū) Range分區(qū)特別適合于按時間周期進(jìn)行數(shù)據(jù)的存儲：日、周、月、年等 數(shù)據(jù)管理能力強(qiáng) 數(shù)據(jù)遷移 數(shù)據(jù)備份 數(shù)據(jù)交換 范圍分區(qū)的數(shù)據(jù)可能不均勻 范圍分區(qū)與記錄值有關(guān)，實(shí)施難度和可維護(hù)性相對

9、較差,列表(List)分區(qū),如果某列的值只有幾個，可以采用列表分區(qū) CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state) ( PARTITION sales_west VALUES(California, Hawaii), PARTITION sales_east VALUES (New York, V

10、irginia, Florida), PARTITION sales_central VALUES(Texas, Illinois), PARTITION sales_other VALUES(DEFAULT);,CREATE TABLE ListTable( id INT PRIMARY KEY , name VARCHAR (20), area VARCHAR (10) ) PARTITION BY LIST (area) ( PARTITION part1 VALUES (guangdong,beijing) TABLESPACE Part1_tb, PARTITION part2 VA

11、LUES (shanghai,nanjing) TABLESPACE Part2_tb ); ),列表分區(qū)的特點(diǎn),List分區(qū)通過對分區(qū)字段的離散值進(jìn)行分區(qū) List分區(qū)是不排序的，而且分區(qū)之間也沒有關(guān)聯(lián) List分區(qū)適合于對數(shù)據(jù)離散值進(jìn)行控制 List分區(qū)只支持單個字段 List分區(qū)具有與range分區(qū)相似的優(yōu)缺點(diǎn)： 數(shù)據(jù)管理能力強(qiáng) 各分區(qū)的數(shù)據(jù)可能不均勻,散列(hash)分區(qū),這類分區(qū)是在列值上使用散列算法，以確定將行放入哪個分區(qū)中。當(dāng)列的值沒有合適的條件時，建議使用散列分區(qū)。 散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型，因?yàn)橥ㄟ^在I/O設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小一致

12、。,例一： CREATE TABLE HASH_TABLE ( COL NUMBER(8), INF VARCHAR2(100) ) PARTITION BY HASH (COL) ( PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03 ),簡寫： CREATE TABLE emp( empno NUMBER (4), ename VARCHAR2 (30), sal NUMBER )PARTITION BY HAS

13、H (empno) PARTITIONS 8STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);,hash分區(qū)最主要的機(jī)制是根據(jù)hash算法來計算具體某條紀(jì)錄應(yīng)該插入到哪個分區(qū)中,hash算法中最重要的是hash函數(shù)，Oracle中如果你要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方，這樣可以使得各個分區(qū)間數(shù)據(jù)分布更加均勻。,Hash分區(qū)的特點(diǎn),基于分區(qū)字段的hash值，自動將記錄插入到相應(yīng)分區(qū) 分區(qū)一般是2的冪 易于實(shí)施 總體性能最佳 適合于靜態(tài)數(shù)據(jù) Hash 分區(qū)適合于數(shù)據(jù)的均勻存儲 Hash分區(qū)特別適合于PDML

14、和Partition-wise join 支持（hash）local/global indexes 數(shù)據(jù)管理能力強(qiáng) Hash分區(qū)對數(shù)據(jù)值無法控制,組合(Composite)分區(qū),CREATE TABLE quarterly_regional_sales (deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) ( PARTITION q1_1999 VALU

15、ES LESS THAN(TO_DATE(1-APR-1999,DD-MON-YYYY) (SUBPARTITION q1_1999_northwest VALUES (OR, WA), SUBPARTITION q1_1999_southwest VALUES (AZ, UT, NM), SUBPARTITION q1_1999_northeast VALUES (NY, VM, NJ), SUBPARTITION q1_1999_southeast VALUES (FL, GA), SUBPARTITION q1_1999_northcentral VALUES (SD, WI), SUB

16、PARTITION q1_1999_southcentral VALUES (NM, TX), PARTITION q2_1999 VALUES LESS THAN(TO_DATE(1-JUL-1999,DD-MON-YYYY) (SUBPARTITION q2_1999_northwest VALUES (OR, WA), SUBPARTITION q2_1999_southwest VALUES (AZ, UT, NM), SUBPARTITION q2_1999_northeast VALUES (NY, VM, NJ), SUBPARTITION q2_1999_southeast V

17、ALUES (FL, GA), SUBPARTITION q2_1999_northcentral VALUES (SD, WI), SUBPARTITION q2_1999_southcentral VALUES (NM, TX), PARTITION q3_1999 VALUES LESS THAN (TO_DATE(1-OCT-1999,DD-MON-YYYY) (SUBPARTITION q3_1999_northwest VALUES (OR, WA), . SUBPARTITION q4_1999_northcentral VALUES (SD, WI), SUBPARTITION

18、 q4_1999_southcentral VALUES (NM, TX);,組合分區(qū)的特點(diǎn),Oracle支持的Composite分區(qū)： range-hash Range-list 既適合于歷史數(shù)據(jù)，又適合于數(shù)據(jù)均勻分布 與范圍分區(qū)一樣提高可用性和可管理性 更好的PDML和partition-wise joins特性 實(shí)現(xiàn)粒度更細(xì)的操作 支持符合local indexes 不支持符合 global indexes,組合范圍散列分區(qū),這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進(jìn)行范圍分區(qū)， 然后再按某列進(jìn)行散列分區(qū)， 分區(qū)之中的分區(qū)被稱為子分區(qū)。,create table dinya_tes

19、t ( transaction_id number primary key, item_id number(8) not null, item_description varchar2(300), transaction_date date ) partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) ( partition part_01 values less tha

20、n(to_date(2006-01-01,yyyy-mm-dd), partition part_02 values less than(to_date(2010-01-01,yyyy-mm-dd), partition part_03 values less than(maxvalue) );,組合范圍列表分區(qū),這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進(jìn)行范圍分區(qū)， 然后再按某列進(jìn)行列表分區(qū),CREATE TABLE SALES ( PRODUCT_ID VARCHAR2(5), SALES_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHA

21、R2(20) ) PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) ( PARTITION P1 VALUES LESS THAN(TO_DATE(2003-01-01,YYYY-MM-DD)TABLESPACE rptfact2009 ( SUBPARTITION P1SUB1 VALUES (ACTIVE) TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES (INACTIVE) TABLESPACE rptfact2009 ), PARTITION P2 VALUES

22、 LESS THAN (TO_DATE(2003-03-01,YYYY-MM-DD) TABLESPACE rptfact2009 ( SUBPARTITION P2SUB1 VALUES (ACTIVE) TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES (INACTIVE) TABLESPACE rptfact2009 ) ),如何選定不同類型的分區(qū)表,何時選擇范圍分區(qū)必須可以將表的記錄按照某一列值的范圍進(jìn)行劃分。你想處理一些數(shù)據(jù)，這些數(shù)據(jù)經(jīng)常是屬于某一個范圍內(nèi)，例如月份。如果數(shù)據(jù)能夠按照分區(qū)的范圍均勻分布的話，那會獲得最佳性能。如果數(shù)據(jù)

23、分布很不均勻的話，你可能不得不選擇其他分區(qū)方式。,何時選擇HASH分區(qū),如果數(shù)據(jù)不容易用范圍分區(qū)，但你想提升性能和表的易管理性。 Hash分區(qū)提供了一個在指定數(shù)量的分區(qū)內(nèi)交叉均勻分布數(shù)據(jù)的方法。行根據(jù)分區(qū)鍵的hash值映射到相應(yīng)分區(qū)中。 創(chuàng)建和使用hash分區(qū)你可以靈活放置數(shù)據(jù)，可以通過交叉訪問在不同I/O設(shè)備上的分區(qū)提升性能。 CREATE TABLE scubagear (id NUMBER, name VARCHAR2 (60)PARTITION BY HASH (id)PARTITIONS 4 STORE IN (gear1, gear2, gear3, gear4);,何時選擇列表分

24、區(qū),使用LIST分區(qū)你可以直接控制某些數(shù)據(jù)映射到某些分區(qū)。你可以為某個分區(qū)指定不連續(xù)的分區(qū)鍵值。這不同于RANGE分區(qū)（用鍵值的范圍劃分分區(qū)），也不同于HASH分區(qū)（不能控制某行映射到哪個分區(qū)）。,不同類型的分區(qū)表對性能的影響,關(guān)于范圍分區(qū)Range： 要想將行映射到基于列值范圍的分區(qū)，就使用范圍分區(qū)方法。當(dāng)數(shù)據(jù)可以被劃分成邏輯范圍時如年度中的月份，這種類型的分區(qū)就有用了。當(dāng)數(shù)據(jù)在整個范圍中能被均等地劃分時性能最好。如果靠范圍的分區(qū)會由于不均等的劃分而導(dǎo)致分區(qū)在大小上明顯不同時，就需要考慮其他的分區(qū)方法。,不同類型的分區(qū)表對性能的影響,關(guān)于散列分區(qū)Hash： 如果數(shù)據(jù)不那么容易進(jìn)行范圍分區(qū)，但

25、為了性能和管理的原因又想分區(qū)時，就使用散列分區(qū)方法。散列分區(qū)提供了一種在指定數(shù)量的分區(qū)中均等地劃分?jǐn)?shù)據(jù)的方法?；诜謪^(qū)鍵的散列值將行映射到分區(qū)中。創(chuàng)建和使用散列分區(qū)會給你提供了一種很靈活的放置數(shù)據(jù)的方法，因?yàn)槟憧梢酝ㄟ^在I/O 驅(qū)動器之間播撒(摘掉)這些均等定量的分區(qū)，來影響可用性和性能。,關(guān)于列表分區(qū)List： 當(dāng)你需要明確地控制如何將行映射到分區(qū)時，就使用列表分區(qū)方法?？梢栽诿總€分區(qū)的描述中為該分區(qū)列指定一列離散值，這不同于范圍分區(qū)，在那里一個范圍與一個分區(qū)相關(guān)，這也不同于散列分區(qū)，在那里用戶不能控制如何將行映射到分區(qū)。列表分區(qū)方法是特意為遵從離散值的模塊化數(shù)據(jù)劃分而設(shè)計的。范圍分區(qū)或散列

26、分區(qū)不那么容易做到這一點(diǎn)。進(jìn)一步說列表分區(qū)可以非常自然地將無序的和不相關(guān)的數(shù)據(jù)集進(jìn)行分組和組織到一起。 與范圍分區(qū)和散列分區(qū)所不同，列表分區(qū)不支持多列分區(qū)。如果要將表按列分區(qū)，那么分區(qū)鍵就只能由表的一個單獨(dú)的列組成，然而可以用范圍分區(qū)或散列分區(qū)方法進(jìn)行分區(qū)的所有的列,都可以用列表分區(qū)方法進(jìn)行分區(qū)。,不同類型的分區(qū)表對性能的影響,關(guān)于組合范圍-散列分區(qū)： 范圍和散列技術(shù)的組合，首先對表進(jìn)行范圍分區(qū)，然后用散列技術(shù)對每個范圍分區(qū)再次分區(qū)。給定的范圍分區(qū)的所有子分區(qū)加在一起表示數(shù)據(jù)的邏輯子集。,不同類型的分區(qū)表對性能的影響,關(guān)于組合范圍-列表分區(qū)： 范圍和列表技術(shù)的組合，首先對表進(jìn)行范圍分區(qū)，然后用

27、列表技術(shù)對每個范圍分區(qū)再次分區(qū)。與組合范圍-散列分區(qū)不同的是，每個子分區(qū)的所有內(nèi)容表示數(shù)據(jù)的邏輯子集，由適當(dāng)?shù)姆秶土斜矸謪^(qū)設(shè)置來描述。 創(chuàng)建或更改分區(qū)表時可以指定行移動子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ，當(dāng)其鍵被更改時，該子句啟用或停用將行遷移到一個新的分區(qū)。默認(rèn)值為DISABLE ROW MOVEMENT。,分區(qū)索引,分區(qū)表索引的分類 Local prefixed index Local non-prefixed index Global prefixed index Non partition index Global inde

28、x 的分區(qū)不同于表分區(qū) Local索引的分區(qū)與表分區(qū)相同 An index is prefixed if it is partitioned on a left prefix of the index columns 分區(qū)表上的非分區(qū)索引等同于global 索引,分區(qū)索引,Global index必須是范圍分區(qū)9i以前 Global index可以是hash 分區(qū)10g新特性 Global index不支持bitmap index Unique index 必須是prefixed，或者包含分區(qū)字段 Local index（non-prefixed,non-unique)可以不包含分區(qū)字段,分區(qū)

29、索引示例,CREATE INDEX month_ix ON sales(sales_month) GLOBAL PARTITION BY RANGE(sales_month) (PARTITION pm1_ix VALUES LESS THAN (2) PARTITION pm2_ix VALUES LESS THAN (3) PARTITION pm9_ix VALUES LESS THAN (10) PARTITION pm10_ix VALUES LESS THAN (11) PARTITION pm11_ix VALUES LESS THAN (12) PARTITION pm12_i

30、x VALUES LESS THAN (MAXVALUE);,分區(qū)表索引的使用,OLTP系統(tǒng)中的建議 Global和unique local index性能優(yōu)于nonunique local index Local index提供了更好的可用性 決策支持系統(tǒng)中的建議 Local index更適合于數(shù)據(jù)裝載和分區(qū)維護(hù) 在大量數(shù)據(jù)統(tǒng)計時，能充分利用local index的并行查詢能力 在性能、高可用和可管理性之間進(jìn)行平衡,分區(qū)索引策略選擇,索引字段是表分區(qū)字段的前綴？,Local prefixed,yes,該非分區(qū)字段是唯一索引？,global prefixed,yes,是否性能在可承受范圍，而分

31、區(qū)的可管理性、可用性更重要？,Local non-prefixed,是數(shù)據(jù)倉庫/交易系統(tǒng)？,yes,Local non-prefixed,Global prefixed,DSS,OLTP,NO,NO,NO,分區(qū)表設(shè)計原則,表的大?。寒?dāng)表的大小超過2GB，或?qū)τ贠LTP系統(tǒng)，表記錄超過1000萬時，都應(yīng)該考慮對表進(jìn)行分區(qū) 數(shù)據(jù)訪問特性：基于表的大部分查詢應(yīng)用，只訪問表中少量的數(shù)據(jù)。對于這樣的表進(jìn)行分區(qū)，可充分利用分區(qū)排除無關(guān)數(shù)據(jù)查詢的特性 數(shù)據(jù)維護(hù)：按時間段刪除成批的數(shù)據(jù)，對于這樣的表需要考慮進(jìn)行分區(qū)，以滿足維護(hù)需要 數(shù)據(jù)備份和恢復(fù)：按時間周期進(jìn)行表空間備份時，將分區(qū)與表空間建立對應(yīng)關(guān)系 只讀數(shù)