Oracle的分區① - 概念
?
??? 今天來學習一下分區表。分區表是關系型數據庫發展中很重要的一個環節，充分解決了大表的性能問題。因為一般的企業數據都是以幾張超大表和一些小表組成，所以分區技術的應用對于解決實際問題很有作用。可惜目前公司的數據量實在是太少，也沒必要做分區，而且這東西也不好模擬，所以只能先學習一下理論知識了。
?
一、分區表、分區索引概念
?
??? 為了滿足而非常大的數據庫的管理，需要創建和使用分區表和分區索引，分區表允許將數據分成成為分區甚至子分區的更小的、更好管理的塊。每個分區可以單獨管理，可以不依賴其他分區而單獨發揮作用，因此可以提供更有利于可用性和性能的結構。
?
??? 表或索引可以共享相同的邏輯屬性，但是可以有不同的物理屬性。例如所有分區/子分區可以共享相同的列和約束，但是可以有不同的表空間。
?
??? 最好可以將表或者索引的分區存儲到不同的表空間，這樣的好處是：
??? ● 減少數據在多個分區中沖突的可能性
??? ● 可以單獨備份和恢復每個分區
??? ● 控制分區與磁盤驅動器之間的映射(平衡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
、多個數據塊大小的分區限制
?
??? 若在具有多個數據塊大小的表空間中創建分區對象時需要特別留意，因為分區對象存儲到這些表空間時會受到某些限制。例如以下的分區必須存儲在具有相同數據塊大小的表空間中：
?
??? ● 常規表
??? ● 索引
??? ● 索引結構表的主鍵索引段
??? ● 索引結構表的溢出段
??? ● 在外存儲的LOB列
?