基于oracle下航空系統(tǒng)之qar數(shù)據(jù)庫應(yīng)用實(shí)例開發(fā)13管理分區(qū)表

1、管理分區(qū)表管理分區(qū)表- indiana jones北風(fēng)網(wǎng)LIST 概述 分區(qū)類型 范圍分區(qū) 列表分區(qū) Hash分區(qū) 復(fù)合分區(qū) 訪問分區(qū)表的數(shù)據(jù) 管理維護(hù)分區(qū)表北風(fēng)網(wǎng)分區(qū)表概述分區(qū)表概述單表多段，每個(gè)段是一個(gè)分區(qū)普通表的局限性,存儲海量數(shù)據(jù)會帶來性能問題。沒有分區(qū)表之前，海量數(shù)據(jù)存儲采用的方法使用分區(qū)表能加速海量數(shù)據(jù)查詢與維護(hù)的性能 訪問數(shù)據(jù)時(shí)一般會在某個(gè)分區(qū)中找數(shù)據(jù)。不會對表的所有分區(qū)進(jìn)行查找【例如qar事件的超限信息按年分區(qū)】 分區(qū)可以存儲在不同的磁盤，提高訪問性能和安全性 可以獨(dú)立地備份和恢復(fù)每個(gè)分區(qū) 選擇合適的分區(qū)方法，正確的使用才能有用北風(fēng)網(wǎng)分區(qū)的類型分區(qū)的類型北風(fēng)網(wǎng)分區(qū)方法分區(qū)方法范

2、圍分區(qū)散列分區(qū)列表分區(qū)復(fù)合分區(qū)范圍分區(qū)范圍分區(qū) 范圍分區(qū)范圍分區(qū) 以表中的一個(gè)列或一組列的值的范圍分區(qū)以表中的一個(gè)列或一組列的值的范圍分區(qū) 用于分區(qū)的字段可以是數(shù)值型、日期型、字符型用于分區(qū)的字段可以是數(shù)值型、日期型、字符型 例如：移動通話記錄能夠按照月份來分區(qū)例如：移動通話記錄能夠按照月份來分區(qū) 優(yōu)點(diǎn)：便于管理優(yōu)點(diǎn)：便于管理 范圍分區(qū)的語法：范圍分區(qū)的語法：北風(fēng)網(wǎng)PARTITION BY RANGE (column_name)( PARTITION part1 VALUES LESS THAN(range1), PARTITION part2 VALUES LESS THAN(range2)

3、, . PARTITION partN VALUES LESS THAN(MAXVALUE);Maxvalue：如果數(shù)據(jù)范圍是確定的：如果數(shù)據(jù)范圍是確定的(例如月份例如月份12)，不用設(shè)置，不用設(shè)置maxvalue，但是有的需求范圍不確定但是有的需求范圍不確定(薪水薪水)。范圍分區(qū)范圍分區(qū) 范圍分區(qū)示例范圍分區(qū)示例北風(fēng)網(wǎng)SQL CREATE TABLE Sales( Product_ID varchar2 (5), Sales_Cost number (10)PARTITION BY RANGE (Sales_Cost)( PARTITION P1 VALUES LESS THAN (1000

4、), PARTITION P2 VALUES LESS THAN (2000), PARTITION P3 VALUES LESS THAN (3000);SQL CREATE TABLE SALES2 (PRODUCT_ID VARCHAR2(5),SALES_DATE DATE NOT NULL,SALES_COST NUMBER(10)PARTITION BY RANGE (SALES_DATE)( PARTITION P1 VALUES LESS THAN (TO_DATE(2003-01-01), PARTITION P2 VALUES LESS THAN (TO_DATE(2004

5、-01-01), PARTITION P3 VALUES LESS THAN (MAXVALUE);范圍分區(qū)注意范圍分區(qū)注意 分區(qū)的關(guān)鍵字能夠是最多16個(gè)列的復(fù)合的關(guān)鍵字。 范圍一定要連續(xù)，例如不允許, 13, 56, 810 如果沒有maxvalue的設(shè)置，當(dāng)插入的值大于最大關(guān)鍵字的分區(qū)時(shí)會報(bào)14400錯(cuò)誤。 如果分區(qū)的字段是date類型。那么一定要用to_date來使年份的表示正確。北風(fēng)網(wǎng)散列分區(qū)散列分區(qū)散列分區(qū)散列分區(qū) 【hashhash分區(qū)分區(qū)】允許用戶對不具有邏輯范圍的數(shù)據(jù)進(jìn)行分區(qū)允許用戶對不具有邏輯范圍的數(shù)據(jù)進(jìn)行分區(qū) 例如：數(shù)據(jù)分布不均勻，例如：數(shù)據(jù)分布不均勻，1月份月份10條記錄

6、，條記錄，2月份月份1千萬記錄。這時(shí)使千萬記錄。這時(shí)使用范圍分區(qū)沒有意義。用范圍分區(qū)沒有意義。 Oracle利用自己的利用自己的hash算法，能夠保證數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)算法，能夠保證數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布散列分區(qū)語法中平均分布散列分區(qū)語法 特點(diǎn)：能把數(shù)據(jù)平均分配到各個(gè)分區(qū)特點(diǎn)：能把數(shù)據(jù)平均分配到各個(gè)分區(qū)北風(fēng)網(wǎng)PARTITION BY HASH (column_name)PARTITIONS number_of_partitions;或PARTITION BY HASH (column_name)( PARTITION part1 TABLESPACE tbs1, PARTIT

7、ION part2 TABLESPACE tbs2, . PARTITION partN TABLESPACE tbsN);散列分區(qū)散列分區(qū) 散列分區(qū)散列分區(qū) 示例示例北風(fēng)網(wǎng)SQL CREATE TABLE Employee( Employee_ID varchar2 (5), Employee_Name varchar2(20), Department varchar2 (10)PARTITION BY HASH (Department)( Partition D1, Partition D2, Partition D3); SQL CREATE TABLE EMPLOYEE( EMP_ID

8、 NUMBER(4), EMP_NAME VARCHAR2(14), EMP_ADDRESS VARCHAR2(15), DEPARTMENT VARCHAR2(10)PARTITION BY HASH (DEPARTMENT)PARTITIONS 4; 散列分區(qū)散列分區(qū) 不必清楚不必清楚hash如何分區(qū)，所以只如何分區(qū)，所以只要設(shè)置要設(shè)置2的的n次方個(gè)分區(qū)就可以了。次方個(gè)分區(qū)就可以了。 等值運(yùn)算時(shí)，等值運(yùn)算時(shí)，hash算法很快能找算法很快能找到對應(yīng)的分區(qū)。到對應(yīng)的分區(qū)。 Where column_name = &var1 有大量的等值運(yùn)算的時(shí)候適合建立有大量的等值運(yùn)算的時(shí)候適合建立h

9、ash 如果有范圍運(yùn)算就應(yīng)該避免使用如果有范圍運(yùn)算就應(yīng)該避免使用hash 散列分區(qū)的關(guān)鍵字應(yīng)該盡量是唯散列分區(qū)的關(guān)鍵字應(yīng)該盡量是唯一或者接近唯一的字段。一或者接近唯一的字段。北風(fēng)網(wǎng)列表分區(qū)列表分區(qū)北風(fēng)網(wǎng)n列表分區(qū)列表分區(qū)當(dāng)需要在如部門、城市這種列進(jìn)行分區(qū)時(shí)，難以按照范圍進(jìn)行分區(qū)，此時(shí)可以用列表分區(qū)列中的值是有限的若干個(gè)n列表分區(qū)的語法：列表分區(qū)的語法：PARTITION BY LIST (column_name)( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), . PARTITION

10、partN VALUES (DEFAULT);列表分區(qū)列表分區(qū)北風(fēng)網(wǎng)SQL CREATE TABLE Employee1( Emp_ID number (4), Emp_Name varchar2 (14), Emp_Address varchar2 (15)PARTITION BY LIST (Emp_Address)( Partition north values (芝加哥), Partition west values (舊金山, 洛杉磯), Partition south values (亞特蘭大, 達(dá)拉斯, 休斯頓), Partition east values (紐約, 波斯頓),

11、 Partition other values (default);列表分區(qū)列表分區(qū) 需要明確的寫出列表值。需要明確的寫出列表值。 在列表分區(qū)中在列表分區(qū)中DEFAULT 代表所有代表所有在其他分區(qū)中不包括的值在其他分區(qū)中不包括的值 北風(fēng)網(wǎng)復(fù)合分區(qū)復(fù)合分區(qū) 復(fù)合分區(qū)復(fù)合分區(qū) 范圍分區(qū)與散列分區(qū)或列表分區(qū)的組合，范圍分區(qū)與散列分區(qū)或列表分區(qū)的組合，range+hash、range+list 復(fù)合分區(qū)的語法：復(fù)合分區(qū)的語法：先根據(jù)先根據(jù)范圍范圍對數(shù)據(jù)進(jìn)行分區(qū)，然后再這些對數(shù)據(jù)進(jìn)行分區(qū)，然后再這些分區(qū)內(nèi)分區(qū)內(nèi)創(chuàng)建創(chuàng)建散列散列子分區(qū)子分區(qū)。北風(fēng)網(wǎng)PARTITION BY RANGE (column_n

12、ame1)SUBPARTITION BY HASH (column_name2)SUBPARTITIONS number_of_partitions( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), . PARTITION partN VALUE LESS THAN(MAXVALUE);復(fù)合分區(qū)復(fù)合分區(qū)復(fù)合分區(qū)示例復(fù)合分區(qū)示例北風(fēng)網(wǎng)SQL CREATE TABLE SALES( PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL,

13、SALES_COST NUMBER (10)PARTITION BY RANGE (SALES_DATE)SUBPARTITION BY HASH (PRODUCT_ID)SUBPARTITIONS 5( PARTITION S1 VALUES LESS THAN (TO_DATE(01/4/2001, DD/Mm/YYYY), PARTITION S2 VALUES LESS THAN (TO_DATE(01/7/2001, DD/Mm/YYYY), PARTITION S3 VALUES LESS THAN (TO_DATE(01/9/2001, DD/Mm/YYYY), PARTITIO

14、N S4 VALUES LESS THAN (MAXVALUE)先按照先按照Sales_Date進(jìn)行范圍分區(qū)，共創(chuàng)建進(jìn)行范圍分區(qū)，共創(chuàng)建4個(gè)分區(qū)個(gè)分區(qū)s1,s2,s3,s4；再在每；再在每個(gè)分區(qū)中分別創(chuàng)建個(gè)分區(qū)中分別創(chuàng)建5個(gè)個(gè)Hash子分區(qū)，共子分區(qū)，共4*5=20個(gè)分區(qū)個(gè)分區(qū)訪問已分區(qū)的表訪問已分區(qū)的表 在在sales表上對表上對sales_cost進(jìn)行范圍分區(qū)進(jìn)行范圍分區(qū)select * from sales where sales_cost =2 ;select * from sales where sales_cost between 2000 and 3000;select * fr

15、om sales partition (P2);INSERT INTO SALES VALUES (P004, 1080);DELETE FROM SALES PARTITION (P2);分區(qū)表的方便之處在于，所有的分區(qū)表的方便之處在于，所有的select與與dml操作不需要指定分區(qū)名，操作不需要指定分區(qū)名，只要知道表名就可以。只要知道表名就可以。Oracle會自動地判斷需要的數(shù)據(jù)在那個(gè)分區(qū)中。會自動地判斷需要的數(shù)據(jù)在那個(gè)分區(qū)中。查詢、修改和刪除分區(qū)表時(shí)可以顯式指定要操作的分區(qū)查詢、修改和刪除分區(qū)表時(shí)可以顯式指定要操作的分區(qū) 北風(fēng)網(wǎng)分區(qū)維護(hù)操作分區(qū)維護(hù)操作 分區(qū)維護(hù)操作修改已分區(qū)表的分區(qū)。分

16、區(qū)維護(hù)操作修改已分區(qū)表的分區(qū)。 分區(qū)維護(hù)的類型：分區(qū)維護(hù)的類型：計(jì)劃事件計(jì)劃事件 定期刪除最舊的分區(qū)定期刪除最舊的分區(qū)非計(jì)劃事件非計(jì)劃事件 最初分區(qū)的不夠合理，比如，范圍分區(qū)時(shí)把分區(qū)最初分區(qū)的不夠合理，比如，范圍分區(qū)時(shí)把分區(qū)弄得太大，我們需要拆分一下。或者分區(qū)太小，需要合并多個(gè)分弄得太大，我們需要拆分一下?；蛘叻謪^(qū)太小，需要合并多個(gè)分區(qū)。區(qū)。 分區(qū)維護(hù)操作有：分區(qū)維護(hù)操作有：添加分區(qū)添加分區(qū)刪除分區(qū)刪除分區(qū)截?cái)喾謪^(qū)截?cái)喾謪^(qū)合并分區(qū)合并分區(qū)拆分分區(qū)拆分分區(qū)北風(fēng)網(wǎng)維護(hù)分區(qū)維護(hù)分區(qū) 2-1 添加分區(qū)添加分區(qū) 在最后一個(gè)分區(qū)之后添加新分在最后一個(gè)分區(qū)之后添加新分區(qū)區(qū)北風(fēng)網(wǎng)SQL ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);n刪除分區(qū)刪除分區(qū) 刪除一個(gè)指定的分區(qū)，分區(qū)的數(shù)據(jù)也隨之刪刪除一個(gè)指定的分區(qū)，分區(qū)的數(shù)據(jù)也隨之刪除除SQL ALTER TABLE SALES DROP PARTITION P4;n截?cái)喾謪^(qū)截?cái)喾謪^(qū) 刪除指定分區(qū)中的所有記錄刪除指定分區(qū)中的所有記錄 SQL ALTER TABLE SALES TRUNCATE PARTITION P3;維護(hù)分