資料庫分區分表

標籤：

http://blog.163.com/[email protected]/blog/static/12257726201051735823602/

 

一、分區表、分區索引概念
 
    為了滿足而大型資料庫的管理，需要建立和使用分區表和分區索引，分區表允許將資料分成成為分區甚至子分區的更小的、更好管理的塊。每個分區可以單獨管理，可以不依賴其他分區而單獨發揮作用，因此可以提供更有利於可用性和效能的結構。 
 
    表或索引可以共用相同的邏輯屬性，但是可以有不同的物理屬性。例如所有分區/子分區可以共用相同的列和約束，但是可以有不同的資料表空間。 
 
    最好可以將表或者索引的分區儲存到不同的資料表空間，這樣的好處是： 
    ● 減少資料在多個分區中衝突的可能性 
    ● 可以單獨備份和恢複每個分區 
    ● 控制分區與磁碟機之間的映射(平衡I/O負載) 
    ● 改善可管理性、可用性和效能 
 
 
二、分區方法 
 
    1、定界分割 
 
    當列資料可以被劃分為邏輯範圍時(例如年度中的月份)，就可以使用定界分割。當資料在整個範圍中能被均等地劃分時效能最好。如果所劃分的分區範圍大小明顯不同時，則需要考慮其他的分區方法了。 
 
    建立定界分割時，需要指定分區列、表示分區邊界，例如： 
 
    CREATE TABLE sales 
    ( invoice_no NUMBER, 
    sale_year INT NOT NULL, 
    sale_month INT NOT NULL, 
    sale_day INT NOT NULL ) 
    PARTITION BY RANGE (sale_year, sale_month, sale_day) 
    ( PARTITION sale_q1 VALUES LESS THAN (1999, 04, 01) 
    TABLLESPACE tsa, 
    PARTITION sale_q2 VALUES LESS THAN (1999, 07, 01) 
    TABLLESPACE tsb, 
    PARTITION sale_q3 VALUES LESS THAN (1999, 10, 01) 
    TABLLESPACE tsc, 
    PARTITION sale_q4 VALUES LESS THAN (2000, 01, 01) 
    TABLLESPACE tsd ); 
 
    註：要注意使用不同字元集的資料庫時，最字元的分類序列有時是不同的。 
 
    2、散列分區 
 
    當資料不太容易進行範圍劃分時，為了效能和管理的原因又想分區時，就可以使用散列分區方法。散列分區將在指定數量的分區中均等得劃分資料。建立散列分區需要指定分區列、分區數量(或單獨的分區描述)舉例如下： 
 
    CREATE TABLE scubagear 
    (id NUMBER, 
    name VARCHAR2(60)) 
    PARTITION BY HASH (id) 
    PARTITIONS 4 
    STORE IN (gear1, gear2, gear3, gear4); 
 
    3、列表分區 
 
    當需要明確地控制如何將行映射到分區時，就需要使用列表分區。可以在每個分區表述中為該分區指定一列離散值。 
 
    列表分區與定界分割、散列分區的區別在於 
    ● 定界分割為分列假設了一個值的自然範圍，無法將該值範圍之外的分區組織在一起 
    ● 散列分區無法對資料的劃分進行控制，在邏輯上是無須的 
 
    需要注意的是：列表分區無法支援多列分區。具體舉例如下： 
 
    CREATE TABLE sales_by_region 
    (deptno number, 
    deptname varchar2(20), 
    quarterly_sales number(10,2), 
    state varchar2(2)) 
    PARTITION BY LIST (state) 
    (PARTITION q1_northwest VALUES (‘OR‘, ‘WA‘), 
    PARTITION q1_southwest VALUES (‘AZ‘, ‘UT‘, ‘NM‘), 
    PARTITION q1_northeast VALUES (‘NY‘, ‘VM‘, ‘NJ‘), 
    PARTITION q1_southeast VALUES (‘FL‘, ‘GA‘), 
    PARTITION q1_northcentral VALUES (‘SD‘, ‘WI‘), 
    PARTITION q1_southcentral VALUES (‘OK‘, ‘TX‘)); 
 
    4、組合分區 
 
    組合分區是在分區中使用定界分割，而在子分區中使用散列分區。組合分區很適合於曆史資料和條塊資料兩者，改善了定界分割及其資料放置的管理型。例如一下舉例： 
 
    CREATE TABLE scubaqear (equipno NUMBER, equipname VARCHAR(32), price NUMBER) 
    PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname) 
    SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4) 
    (PARTITION p1 VALUES LESS THAN (1000), 
    PARTITION p2 VALUES LESS THAN (2000), 
    PARTITION p3 VALUES LESS THAN (MAXVALUE)); 
 
 
三、分區表的建立 
 
    1、建立定界分割表 
 
    使用PARTITION BY RANGE子句來表明定界分割，使用PARTITION子句標識各個分區範圍，另外PARTITION子句下級子句可以指定特別用於該分區段的物理屬性，如果沒有重載，則自動繼承基礎資料表的屬性。 
 
    重新修改上面的例子： 
 
    CREATE TABLE sales 
    ( invoice_no NUMBER, 
    sale_year INT NOT NULL, 
    sale_month INT NOT NULL, 
    sale_day INT NOT NULL ) 
    STORAGE (INITIAL 100K NEXT 50K) LOGGING 
    PARTITION BY RANGE (sale_year, sale_month, sale_day) 
    ( PARTITION sale_q1 VALUES LESS THAN (1999, 04, 01) 
    TABLLESPACE tsa STORAGE (INITIAL 20K, NEXT 10K), 
    PARTITION sale_q2 VALUES LESS THAN (1999, 07, 01) 
    TABLLESPACE tsb, 
    PARTITION sale_q3 VALUES LESS THAN (1999, 10, 01) 
    TABLLESPACE tsc, 
    PARTITION sale_q4 VALUES LESS THAN (2000, 01, 01) 
    TABLLESPACE tsd ) 
    ENABLE ROW MOVMENT; 
 
    說明：在表層級指定了儲存參數個LOGGING屬性，而在分區sale_q1中的儲存參數進行重設，原因是第一季度業務較少。另外使用ENABLE ROW MOVMENT子句，表示如果索引值更改了，就允許將行遷移到新分區。 
 
    另外建立一個定界分割的全域索引如下： 
 
    CREATE INDEX month_ix ON sales(sales_month) 
    GROBAL PARTITION BY RANGE(sales_month) 
    (PARTITION pm1_ix VALUES LESS THAN (2) 
    PARTITION pm2_ix VALUES LESS THAN (3) 
    PARTITION pm3_ix VALUES LESS THAN (4) 
    PARTITION pm4_ix VALUES LESS THAN (5) 
    PARTITION pm5_ix VALUES LESS THAN (6) 
    PARTITION pm6_ix VALUES LESS THAN (7) 
    PARTITION pm7_ix VALUES LESS THAN (8) 
    PARTITION pm8_ix VALUES LESS THAN (9) 
    PARTITION pm9_ix VALUES LESS THAN (10) 
    PARTITION pm10_ix VALUES LESS THAN (11) 
    PARTITION pm11_ix VALUES LESS THAN (12) 
    PARTITION pm12_ix VALUES LESS THAN (MAXVALUE)); 
 
    2、建立散列分區表 
 
    使用PARTITION BY HASH子句來表明散列分區，使用PARTITIONS子句來指定要建立的分區數量，另外使用PARTITION子句來命名各個分區及其資料表空間，但是只能指定TABLESPACE屬性，其他的屬性只能繼承於表層次。舉例如下： 
 
    CREATE TABLE dept (deptno NUMBER, dept name VARCHAR2(32)) 
    STORAGE (INITIAL 10K) 
    PARTITION BY HASH (deptno) 
    (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2, 
    PARTITION p3 TABLESPACE ts3, PARTITION p4 TABLESPACE ts4); 
 
    為上表建立局部索引，則Oracle會自動建立一個與基礎資料表同分區的索引。 
 
    CREATE INDEX locd_dept_ix ON dept(deptno) LOCAL 
 
    3、建立列表分區表 
 
    使用PARTITION BY LIST子句來表明列表分區，使用PARTITION子句指定一串文字值，即為分區列的離散值。另外PARTITION子句下級子句可以指定特別用於該分區段的物理屬性，如果沒有重載，則自動繼承基礎資料表的屬性。 
 
    此類型基本與上面的舉例相同，不再重新舉例。 
 
    4、建立組合分區表 
 
    先使用PARTITION BY RANGE子句，然後指定一個與PARTITION BY HASH語句遵從文法和規則的SUBPARTITION BY HASH子句來表明組合分區，各個PARTITION子句後面緊跟SUBPARTITION或SUBPARTITIONS子句。 
 
    另外可以為每個(範圍)分區指定不同的屬性，另外還可以使用STORE IN子句來指定不同的不同的資料表空間。 
 
    CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER) 
    PARTITION BY RANGE (deptno) SUBPARTITION BY HASH(empname) 
    SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7) 
    (PARTITION p1 VALUES LESS THAN (1000) PCTFREE 40, 
    PARTITION p2 VALUES LESS THAN (2000) 
    STORE IN (ts2, ts4, ts6, ts8), 
    PARTITION p3 VALUES LESS THAN (MAXVALUE) 
    (SUBPARTITION p3_s1 TABLESPACE ts4, 
    SUBPARTITION p3_s2 TABLESPACE ts5)); 
 
    另建立一個局部索引，且分段分佈於資料表空間ts7、ts8、ts9 
 
    CREATE INDEX emp_ix ON emp(deptno) 
    LOCAL STORE IN (ts7, ts8, ts9); 
 
    5、建立分局索引結構表 
 
    可以對索引結構表使用定界分割或散列分區，只有定界分割索引結構表才能包含LOB資料類型的列。 
 
    建立定界分割或散列分區索引結構表與建立普通表相似，但也有區別，區別在於： 
    ● 建立該表時需要指定ORGANIZATION INDEX子句，需要時還要指定INCLUDING和OVERFLOW子句 
    ● PARTITION或PARTITIONS子句可以有OVERFLOW下級子句，允許在分區層次上指定溢出段的屬性 
 
    註：索引結構表的分區列集合必須是主鍵列的子集，因為索引結構表的行是按表的主鍵索引儲存的，通過將分區鍵選成主鍵的子集，插入操作就只需要校正在單個分區中的主鍵的唯一性，因此對分區的維護就互不依賴了。 
 
    a.建立定界分割索引結構表 
 
    CREATE TABLE sales(acct_no NUMBER(5), 
    acct_name CHAR(30), 
    amount_of_sale NUMBER(6), 
    week_no INTEGER, 
    SALE_DETAILES varchar2(1000), 
    PRIMARY KEY (acct_no, acct_name, week_no)) 
    ORGANIZATION INDEX 
    INCLUDING week_no 
    OVERFLOW TABLESPACE overflow_here 
    PARTITION BY RANGE (week_no) 
    (PARTITION VALUES LESS THAN (5) 
    TABLESPACE ts1, 
    PARTITION VALUES LESS THAN (9) 
    TABLESPACE ts2 OVERFLOW TABLESPACE overflow_ts2, 
    ... 
    PARTITION VALUES LESS THAN (MAXVALUE) 
    TABLESPACE ts13); 
 
    說明： 
    1、INCLUDING子句指定將week_no列之後的所有列都儲存在溢出段中。 
    2、每個分區有一個溢出段，都儲存在相同的資料表空間(overflow_here)中。 
    3、通過OVERFLOW TABLESPACE子句指定各個分區層次的溢出資料表空間。 
 
 
    b. 建立散列分區索引結構表 
 
    CREATE TABLE sales(acct_no NUMBER(5), 
    acct_name CHAR(30), 
    amount_of_sale NUMBER(6), 
    week_no INTEGER, 
    sale_details VARCHAR2(1000), 
    PRIMARY KEY (acct_no, acct_name, week_no)) 
    ORGANIZATION INDEX 
    INCLUDING week_no 
    OVERFLOW 
    PARTITION BY HASH (week_no) 
    PARTITIONS 16 
    STORE IN (ts1, ts2, ts3, ts4) 
    OVERFLOW STORE IN (ts3, ts6, ts9); 
 
    建議在建立具有可變分區鍵的散列分區索引結構表時，明確指定ROW MOVEMENT ENABLE子句，因為一個好的散列函數會將各行做一個很好的平衡分布，所以改變主鍵列很有可能會移動到其他分區。 
 
    6 、多個資料區塊大小的分區限制 
 
    若在具有多個資料區塊大小的資料表空間中建立分區對象時需要特別留意，因為分區Object Storage Service到這些資料表空間時會受到某些限制。例如以下的分區必須儲存在具有相同資料區塊大小的資料表空間中： 
 
    ● 常規表 
    ● 索引 
    ● 索引結構表的主鍵索引段 
    ● 索引結構表的溢出段 
    ● 在外儲存的LOB列 

資料庫分區分表