Oracle的分區(qū)① - 概念
?
??? 今天來學(xué)習(xí)一下分區(qū)表。分區(qū)表是關(guān)系型數(shù)據(jù)庫(kù)發(fā)展中很重要的一個(gè)環(huán)節(jié)，充分解決了大表的性能問題。因?yàn)橐话愕钠髽I(yè)數(shù)據(jù)都是以幾張超大表和一些小表組成，所以分區(qū)技術(shù)的應(yīng)用對(duì)于解決實(shí)際問題很有作用。可惜目前公司的數(shù)據(jù)量實(shí)在是太少，也沒必要做分區(qū)，而且這東西也不好模擬，所以只能先學(xué)習(xí)一下理論知識(shí)了。
?
一、分區(qū)表、分區(qū)索引概念
?
??? 為了滿足而非常大的數(shù)據(jù)庫(kù)的管理，需要?jiǎng)?chuàng)建和使用分區(qū)表和分區(qū)索引，分區(qū)表允許將數(shù)據(jù)分成成為分區(qū)甚至子分區(qū)的更小的、更好管理的塊。每個(gè)分區(qū)可以單獨(dú)管理，可以不依賴其他分區(qū)而單獨(dú)發(fā)揮作用，因此可以提供更有利于可用性和性能的結(jié)構(gòu)。
?
??? 表或索引可以共享相同的邏輯屬性，但是可以有不同的物理屬性。例如所有分區(qū)/子分區(qū)可以共享相同的列和約束，但是可以有不同的表空間。
?
??? 最好可以將表或者索引的分區(qū)存儲(chǔ)到不同的表空間，這樣的好處是：
??? ● 減少數(shù)據(jù)在多個(gè)分區(qū)中沖突的可能性
??? ● 可以單獨(dú)備份和恢復(fù)每個(gè)分區(qū)
??? ● 控制分區(qū)與磁盤驅(qū)動(dòng)器之間的映射(平衡I/O負(fù)載)
??? ● 改善可管理性、可用性和性能
?
?
二、分區(qū)方法
?
??? 1、范圍分區(qū)
?
??? 當(dāng)列數(shù)據(jù)可以被劃分為邏輯范圍時(shí)(例如年度中的月份)，就可以使用范圍分區(qū)。當(dāng)數(shù)據(jù)在整個(gè)范圍中能被均等地劃分時(shí)性能最好。如果所劃分的分區(qū)范圍大小明顯不同時(shí)，則需要考慮其他的分區(qū)方法了。
?
??? 創(chuàng)建范圍分區(qū)時(shí)，需要指定分區(qū)列、表示分區(qū)邊界，例如：
?
??? 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 );
?
??? 注：要注意使用不同字符集的數(shù)據(jù)庫(kù)時(shí)，最字符的分類序列有時(shí)是不同的。
?
??? 2、散列分區(qū)
?
??? 當(dāng)數(shù)據(jù)不太容易進(jìn)行范圍劃分時(shí)，為了性能和管理的原因又想分區(qū)時(shí)，就可以使用散列分區(qū)方法。散列分區(qū)將在指定數(shù)量的分區(qū)中均等得劃分?jǐn)?shù)據(jù)。創(chuàng)建散列分區(qū)需要指定分區(qū)列、分區(qū)數(shù)量(或單獨(dú)的分區(qū)描述)舉例如下：
?
??? CREATE TABLE scubagear
??? (id NUMBER,
??? name VARCHAR2(60))
??? PARTITION BY HASH (id)
??? PARTITIONS 4
??? STORE IN (gear1, gear2, gear3, gear4);
?
??? 3、列表分區(qū)
?
??? 當(dāng)需要明確得控制如何將行映射到分區(qū)時(shí)，就需要使用列表分區(qū)。可以在每個(gè)分區(qū)表述中為該分區(qū)指定一列離散值。
?
??? 列表分區(qū)與范圍分區(qū)、散列分區(qū)的區(qū)別在于
??? ● 范圍分區(qū)為分列假設(shè)了一個(gè)值的自然范圍，無法將該值范圍之外的分區(qū)組織在一起
??? ● 散列分區(qū)無法對(duì)數(shù)據(jù)的劃分進(jìn)行控制，在邏輯上是無須的
?
??? 需要注意的是：列表分區(qū)無法支持多列分區(qū)。具體舉例如下：
?
??? 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、組合分區(qū)
?
??? 組合分區(qū)是在分區(qū)中使用范圍分區(qū)，而在子分區(qū)中使用散列分區(qū)。組合分區(qū)很適合于歷史數(shù)據(jù)和條塊數(shù)據(jù)兩者，改善了范圍分區(qū)及其數(shù)據(jù)放置的管理型。例如一下舉例：
?
??? 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));
?
?
三、分區(qū)表的創(chuàng)建
?
??? 1、創(chuàng)建范圍分區(qū)表
?
??? 使用PARTITION BY RANGE子句來表明范圍分區(qū)，使用PARTITION子句標(biāo)識(shí)各個(gè)分區(qū)范圍，另外PARTITION子句下級(jí)子句可以指定特別用于該分區(qū)段的物理屬性，如果沒有重載，則自動(dòng)繼承基礎(chǔ)表的屬性。
?
??? 重新修改上面的例子：
?
??? 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;
?
??? 說明：在表層級(jí)指定了存儲(chǔ)參數(shù)個(gè)LOGGING屬性，而在分區(qū)sale_q1中的存儲(chǔ)參數(shù)進(jìn)行重置，原因是第一季度業(yè)務(wù)較少。另外使用ENABLE ROW MOVMENT子句，表示如果鍵值更改了，就允許將行遷移到新分區(qū)。
?
??? 另外創(chuàng)建一個(gè)范圍分區(qū)的全局索引如下：
?
??? 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、創(chuàng)建散列分區(qū)表
?
??? 使用PARTITION BY HASH子句來表明散列分區(qū)，使用PARTITIONS子句來指定要?jiǎng)?chuàng)建的分區(qū)數(shù)量，另外使用PARTITION子句來命名各個(gè)分區(qū)及其表空間，但是只能指定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);
?
??? 為上表創(chuàng)建局部索引，則Oracle會(huì)自動(dòng)創(chuàng)建一個(gè)與基礎(chǔ)表同分區(qū)的索引。
?
??? CREATE INDEX locd_dept_ix ON dept(deptno) LOCAL
?
??? 3、創(chuàng)建列表分區(qū)表
?
??? 使用PARTITION BY LIST子句來表明列表分區(qū)，使用PARTITION子句指定一串文字值，即為分區(qū)列的離散值。另外PARTITION子句下級(jí)子句可以指定特別用于該分區(qū)段的物理屬性，如果沒有重載，則自動(dòng)繼承基礎(chǔ)表的屬性。
?
??? 此類型基本與上面的舉例相同，不再重新舉例。
?
??? 4、創(chuàng)建組合分區(qū)表
?
??? 先使用PARTITION BY RANGE子句，然后指定一個(gè)與PARTITION BY HASH語句遵從語法和規(guī)則的SUBPARTITION BY HASH子句來表明組合分區(qū)，各個(gè)PARTITION子句后面緊跟SUBPARTITION或SUBPARTITIONS子句。
?
??? 另外可以為每個(gè)(范圍)分區(qū)指定不同的屬性，另外還可以使用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));
?
??? 另創(chuàng)建一個(gè)局部索引，且分段分布于表空間ts7、ts8、ts9
?
??? CREATE INDEX emp_ix ON emp(deptno)
??? LOCAL STORE IN (ts7, ts8, ts9);
?
??? 5、創(chuàng)建分局索引結(jié)構(gòu)表
?
??? 可以對(duì)索引結(jié)構(gòu)表使用范圍分區(qū)或散列分區(qū)，只有范圍分區(qū)索引結(jié)構(gòu)表才能包含LOB數(shù)據(jù)類型的列。
?
??? 創(chuàng)建范圍分區(qū)或散列分區(qū)索引結(jié)構(gòu)表與創(chuàng)建普通表相似，但也有區(qū)別，區(qū)別在于：
??? ● 創(chuàng)建該表時(shí)需要指定ORGANIZATION INDEX子句，需要時(shí)還要指定INCLUDING和OVERFLOW子句
??? ● PARTITION或PARTITIONS子句可以有OVERFLOW下級(jí)子句，允許在分區(qū)層次上指定溢出段的屬性
?
??? 注：索引結(jié)構(gòu)表的分區(qū)列集合必須是主鍵列的子集，因?yàn)樗饕Y(jié)構(gòu)表的行是按表的主鍵索引存儲(chǔ)的，通過將分區(qū)鍵選成主鍵的子集，插入操作就只需要校驗(yàn)在單個(gè)分區(qū)中的主鍵的唯一性，因此對(duì)分區(qū)的維護(hù)就互不依賴了。
?
???
a.創(chuàng)建范圍分區(qū)索引結(jié)構(gòu)表
?
??? 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列之后的所有列都存儲(chǔ)在溢出段中。
??? 2、每個(gè)分區(qū)有一個(gè)溢出段，都存儲(chǔ)在相同的表空間(overflow_here)中。
??? 3、通過OVERFLOW TABLESPACE子句指定各個(gè)分區(qū)層次的溢出表空間。
?
?
??? b.
創(chuàng)建散列分區(qū)索引結(jié)構(gòu)表
?
??? 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);
?
??? 建議在創(chuàng)建具有可變分區(qū)鍵的散列分區(qū)索引結(jié)構(gòu)表時(shí)，明確指定ROW MOVEMENT ENABLE子句，因?yàn)橐粋€(gè)好的散列函數(shù)會(huì)將各行做一個(gè)很好的平衡分布，所以改變主鍵列很有可能會(huì)移動(dòng)到其他分區(qū)。
?
??? 6
、多個(gè)數(shù)據(jù)塊大小的分區(qū)限制
?
??? 若在具有多個(gè)數(shù)據(jù)塊大小的表空間中創(chuàng)建分區(qū)對(duì)象時(shí)需要特別留意，因?yàn)榉謪^(qū)對(duì)象存儲(chǔ)到這些表空間時(shí)會(huì)受到某些限制。例如以下的分區(qū)必須存儲(chǔ)在具有相同數(shù)據(jù)塊大小的表空間中：
?
??? ● 常規(guī)表
??? ● 索引
??? ● 索引結(jié)構(gòu)表的主鍵索引段
??? ● 索引結(jié)構(gòu)表的溢出段
??? ● 在外存儲(chǔ)的LOB列
?