OraclePartition詳解優(yōu)缺點(diǎn)

1、Oracle表分區(qū)詳解此文從以下幾個(gè)方面來整理關(guān)于分區(qū)表的概念及操作1. 表空間及分區(qū)表的概念2. 表分區(qū)的具體作用3. 表分區(qū)的優(yōu)缺點(diǎn)4. 表分區(qū)的幾種類型及操作方法5. 對(duì)表分區(qū)的維護(hù)性操作.(1.)表空間及分區(qū)表的概念表空間:是一個(gè)或多個(gè)數(shù)據(jù)文件的集合，所有的數(shù)據(jù)對(duì)象都存放在指定的 表空間中，但主要存放的是表， 所以稱作表空間。分區(qū)表: 當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會(huì)變慢，應(yīng)用程序的性 能就會(huì)下降，這時(shí)就應(yīng)該考慮對(duì)表進(jìn)行分區(qū)。表進(jìn)行分區(qū)后，邏輯上 表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個(gè)表空 間(物理文件上)，這樣查詢數(shù)據(jù)時(shí)，不至于每次都掃描整張表。(2).表

2、分區(qū)的具體作用Oracle的表分區(qū)功能通過改善可管理性、性能和可用性，從而為各式應(yīng)用程序帶來了極大的好處。 通常，分區(qū)可以使某些查詢以及 維護(hù)操作的性能大大提高。此外，分區(qū)還可以極大簡(jiǎn)化常見的管理任 務(wù)，分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用性系統(tǒng)的關(guān)鍵工具。分區(qū)功能能夠?qū)⒈?、索引或索引組織表進(jìn)一步細(xì)分為段， 這些數(shù) 據(jù)庫對(duì)象的段叫做分區(qū)。每個(gè)分區(qū)有自己的名稱，還可以選擇自己的 存儲(chǔ)特性。從數(shù)據(jù)庫管理員的角度來看，一個(gè)分區(qū)后的對(duì)象具有多個(gè) 段，這些段既可進(jìn)行集體管理，也可單獨(dú)管理，這就使數(shù)據(jù)庫管理員 在管理分區(qū)后的對(duì)象時(shí)有相當(dāng)大的靈活性。 但是，從應(yīng)用程序的角度來看，分區(qū)后的表與非分區(qū)表完全相同

3、，使用SQL DML命令訪問分區(qū)后的表時(shí)，無需任何修改。什么時(shí)候使用分區(qū)表:1、表的大小超過2GB。2、表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加都新的分區(qū)中。(3).表分區(qū)的優(yōu)缺點(diǎn)表分區(qū)有以下優(yōu)點(diǎn):1、改善查詢性能：對(duì)分區(qū)對(duì)象的查詢可以僅搜索自己關(guān)心的分區(qū),提高檢索速度。2、增強(qiáng)可用性：如果表的某個(gè)分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用;3、維護(hù)方便：如果表的某個(gè)分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可;4、均衡I/O :可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整 個(gè)系統(tǒng)性能。缺點(diǎn): 分區(qū)表相關(guān)：已經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過Oracle提供了在線重定義表的功能。(4).表分

4、區(qū)的幾種類型及操作方法一.范圍分區(qū):范圍分區(qū)將數(shù)據(jù)基于范圍映射到每一個(gè)分區(qū)， 這個(gè)范圍是你在創(chuàng)建分 區(qū)時(shí)指定的分區(qū)鍵決定的。這種分區(qū)方式是最為常用的，并且分區(qū)鍵 經(jīng)常采用日期。舉個(gè)例子：你可能會(huì)將銷售數(shù)據(jù)按照月份進(jìn)行分區(qū)。當(dāng)使用范圍分區(qū)時(shí)，請(qǐng)考慮以下幾個(gè)規(guī)則:1、每一個(gè)分區(qū)都必須有一個(gè) VALUES LESS THEN子句，它指定了一個(gè)不包括在該分區(qū)中的上限值。 分區(qū)鍵的任何值等于或者大于 這個(gè)上限值的記錄都會(huì)被加入到下一個(gè)高一些的分區(qū)中。2、所有分區(qū)，除了第一個(gè)，都會(huì)有一個(gè)隱式的下限值，這個(gè)值就是此分區(qū)的前一個(gè)分區(qū)的上限值。3、在最高的分區(qū)中，MAXVALUE被定義。MAXVALUE代表了一

5、個(gè)不確定的值。這個(gè)值高于其它分區(qū)中的任何分區(qū)鍵的值，也可以 理解為高于任何分區(qū)中指定的 VALUE LESS THEN的值，同時(shí)包括空值。例一:假設(shè)有一個(gè)CUSTOMER表，表中有數(shù)據(jù)200000行，我們將此 表通過CUSTOMER。進(jìn)行分區(qū)，每個(gè)分區(qū)存儲(chǔ)100000行，我們 將每個(gè)分區(qū)保存到單獨(dú)的表空間中，這樣數(shù)據(jù)文件就可以跨越多個(gè)物 理磁盤。下面是創(chuàng)建表和分區(qū)的代碼，如下:CREATE TABLE CUSTOMERCUSTOMER。NUMBER NOT NULL P RIMAR Y KEY,FIRST_NAME VARCHAR2(30) NOT NULL,LAST_NAME VARCHAR

6、2(30) NOT NULL,PH ONEVARCHAR2(15) NOT NULL,EMAILVARCHAR2(80),STATUSCHAR(1)PARTITION BY RANGE (CUSTOMER_ID)PARTITION CUS_PART1 VALUES LESS THAN (100000)TABLES PACE CUS_TS01,PARTITION CUS_PART2 VALUES LESS THAN (200000)TABLES PACE CUS TS02例二：按時(shí)間劃分CREATE TABLE ORDER ACTIVITIESORDER_ID NUMBER(7) NOT NUL

7、L,ORDER_DATE DATE,TOTAL_AMOUNT NUMBER,CUSTOTMER_ID NUMBER(7),P AIDCHAR(1)PARTITION BY RANGE (ORDER_DATE)PARTITION ORD ACT PART01 VALUES LESS THAN(TO_DATEC01-MAY-2003,DD-M ON-YYYY)TABLES PACEORD_TS01,TABLESPACEPARTITION ORD ACT PART02 VALUES LESS THAN(TO_DATE(01-JUN-2003,DD-MON-YYYY )ORD_TS02,TABLESP

8、ACEPARTITION ORD ACT PART02 VALUES LESS THAN(TO_DATE(01-JUL-2003,DD-MON-YYYY)ORD TS03例三：MAXVALUECREATE TABLE Ra ngeTableiddINT P RIMAR Y KEY ,iNAME VARCHAR(IO),grade INTPARTITION BY RANGE (grade)P ARTITIONp art1VALUES LESS THEN (1000)TABLES PACE Part1_tb,P ARTITION p art2 VALUES LESS THEN (MAXVALUE)

9、TABLES PACE Part2 tb );二列表分區(qū):該分區(qū)的特點(diǎn)是某列的值只有幾個(gè)，基于這樣的特點(diǎn)我們可以采 用列表分區(qū)。例一CREATE TABLE PROBLEM TICKETSP ROBLEM_ID NUMBER(7) NOT NULL P RIMAR Y KEY,DESCR IP TION VARCHAR2(2000),CUSTOMER。 NUMBER(7) NOT NULL,DATE_ENTERED DATE NOT NULL,STATUS VARCHAR2(20)P ARTITION BY LIST (STATUS)PARTITIONPROB ACTIVEVALUES (AC

10、TIVE)TABLES PACE PROB_TS01,PARTITIONPROB INACTIVEVALUES (INACTIVE)TABLES PACE PROB TS02例二CREATE TABLE ListTableid INT P RIMAR Y KEY ,name VARCHAR (20),area VARCHAR (10)PARTITION BY LIST (area)P ARTITIONpart1VALUES(gua ngdo ng,beiji ng)TABLES PACE Part1_tb,P ARTITIONpart2VALUES(sha nghai, nanji ng)TA

11、BLES PACE Part2_tb ）；三散列分區(qū): 這類分區(qū)是在列值上使用散列算法， 以確定將行放入哪個(gè)分區(qū)中。當(dāng) 列的值沒有合適的條件時(shí)，建議使用散列分區(qū)。散列分區(qū)為通過指定分區(qū)編號(hào)來均勻分布數(shù)據(jù)的一種分區(qū)類型,因?yàn)橥ㄟ^在I/O設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小一致。例一:CREATE TABLE HASH TABLECOL NUMBER(8),INF VARCHAR2(100)PARTITION BY HASH (COL)P ARTITION P ART01 TABLES PACE HASH_TS01,P ARTITION P ART02 TABLES PACE HASH_TS02,

12、P ARTITION P ART03 TABLES PACE HASH TS03簡(jiǎn)寫:CREATE TABLE empempno NUMBER (4),en ame VARCHAR2 (30),sal NUMBERPARTITION BY HASH (empno) P ARTITIONS 8STORE IN (emp 1,e mp 2,e mp 3,e mp 4,e mp 5,e mp 6,e mp 7,e mp8);hash分區(qū)最主要的機(jī)制是根據(jù) hash算法來計(jì)算具體某條紀(jì)錄應(yīng)該插入到哪個(gè)分區(qū)中,hash算法中最重要的是hash函數(shù)，Oracle數(shù)量采用2的n次方，這樣可以使得各個(gè)分區(qū)間數(shù)

13、據(jù)分布更加均勻。中如果你要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的四組合范圍散列分區(qū) 這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進(jìn)行范圍分區(qū), 然后再按某列進(jìn)行列表分區(qū)，分區(qū)之中的分區(qū)被稱為子分區(qū)。CREATE TABLE SALESP RODUCT_ID VARCHAR2(5),SALES_DATE DATE,SALES_COST NUMBER(IO),STATUS VARCHAR2(20)P ARTITION BY RANGE(SALES_DATE) SUB PARTITION BYLIST (STATUS)P ARTITIONP1VALUESLESSthan(to_dat

14、e(2003-01-01, yyyy-m m-dd)tables pacerptfact2009SUB PARTITIONP1SUB1VALUES (ACTIVE)TABLES PACE rptfact2009.SUB PARTITIONP1SUB2VALUES (INACTIVE)TABLES PACE rptfact2009),P ARTITIONP2VALUESLESSTHANTABLESPACE(TO_DATE(2003-03-01, YYYY-M M-DD)rptfact2009SUB PARTITIONP 2SUB1VALUES (ACTIVE)TABLES PACE rptfac

15、t2009,SUB PARTITIONP 2SUB2VALUES (INACTIVE)TABLES PACE rptfact2009五復(fù)合范圍散列分區(qū):這種分區(qū)是基于范圍分區(qū)和散列分區(qū),表首先按某列進(jìn)行范圍分區(qū)，然后再按某列進(jìn)行散列分區(qū)。create table diny a_testtran sactio n_id nu mber p rimary key,item_id nu mber(8) n ot n ull,item_descri pti on varchar2(300),transaction date datep artiti onbyran ge(tra nsacti on _

16、date)sub partiti onbyhash(tra nsacti on_id)sub partiti onsstorein(di nya_sp ace01,di nya_sp ace02,di nya_sp ace03)2006-01-01 2010-01-01 ,p artiti on p art_01 values less tha n( to_date( yyyy-mm-dd ),p artiti on p art_02 values less tha n(to_date( yyyy-mm-dd ),p artiti on p art_03 values less tha n(

17、maxvalue) );(5).有關(guān)表分區(qū)的一些維護(hù)性操作:一、添加分區(qū)以下代碼給SALES表添加了一個(gè)P3分區(qū)ALTER TABLE SALES ADD PARTITION P3 VALUES LESSTHAN(TO_DATE(2003-06-01, YYYY-M M-DD);注意：以上添加的分區(qū)界限應(yīng)該高于最后一個(gè)分區(qū)界限。以下代碼給SALES表的P3分區(qū)添加了一個(gè)P3SUB1子分區(qū)ALTER TABLE SALES MODIF Y PARTITION P3 ADDSUB PARTITION P 3SUB1 VALUES(CO MP LETE);二、刪除分區(qū) 以下代碼刪除了 P3表分區(qū):A

18、LTER TABLE SALES DROP P ARTITION P3;在以下代碼刪除了 P4SUB1子分區(qū):ALTER TABLE SALES DROP SUB PARTITION P 4SUB1;注意：如果刪除的分區(qū)是表中唯一的分區(qū)， 那么此分區(qū)將不能被 刪除，要想刪除此分區(qū)，必須刪除表。三、截?cái)喾謪^(qū) 截?cái)嗄硞€(gè)分區(qū)是指刪除某個(gè)分區(qū)中的數(shù)據(jù)， 并不會(huì)刪除分區(qū)，也不會(huì) 刪除其它分區(qū)中的數(shù)據(jù)。當(dāng)表中即使只有一個(gè)分區(qū)時(shí)，也可以截?cái)嘣摲謪^(qū)。通過以下代碼截?cái)喾謪^(qū):ALTER TABLE SALES TRUNCATE P ARTITION P2;通過以下代碼截?cái)嘧臃謪^(qū):ALTER TABLE SALES

19、 TRUNCATE SUB PARTITION P 2SUB2;四、合并分區(qū) 合并分區(qū)是將相鄰的分區(qū)合并成一個(gè)分區(qū)， 結(jié)果分區(qū)將采用較高分區(qū) 的界限，值得注意的是，不能將分區(qū)合并到界限較低的分區(qū)。以下代 碼實(shí)現(xiàn)了 P1 P2分區(qū)的合并:ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTOP ARTITION P2;五、拆分分區(qū) 拆分分區(qū)將一個(gè)分區(qū)拆分兩個(gè)新分區(qū)， 拆分后原來分區(qū)不再存在。注意不能對(duì)HASH類型的分區(qū)進(jìn)行拆分。ALTER TABLE SALES SBLITPARTITION P2INTO (P ARTITIONAT(TO_DATE(2OO3-

20、O2-O1, YYYY-M M-DD)P 21, PARTITION P22);六、接合分區(qū)（coalesca） 結(jié)合分區(qū)是將散列分區(qū)中的數(shù)據(jù)接合到其它分區(qū)中， 當(dāng)散列分區(qū)中的數(shù)據(jù)比較大時(shí)，可以增加散列分區(qū)，然后進(jìn)行接合，值得注意的是, 接合分區(qū)只能用于散列分區(qū)中。通過以下代碼進(jìn)行接合分區(qū):ALTER TABLE SALES COALESCA P ARTITION;七、重命名表分區(qū) 以下代碼將P21更改為P2ALTER TABLE SALES RENAME P ARTITION P21 TO P2;八、相關(guān)查詢 跨分區(qū)查詢select sum( *) from(selectcou nt(*)c

21、n from t_table_SS PARTITION (P200709_1)union allselect cou nt(*) cn from t_table_SS P ARTITION (P200709_2);查詢表上有多少分區(qū)SELECT * FROM useR TAB PARTITIONSWHERETABLE NAME二tableName查詢索引信息select objectname,object_t yp enables pace_ name,sum(value)from v$segme nt_statisticswherestatisticnameIN (p hysical rea

22、ds, physical write,logical reads)a nd object_t yp e-INDEXgroup by objectname,object_t yp e,tables pace_ nameorder by 4 desc-顯示數(shù)據(jù)庫所有分區(qū)表的信息:select * from DBA PART TABLES-顯示當(dāng)前用戶可訪問的所有分區(qū)表信息select * from ALL PART TABLESselect * from USER PART TABLES-顯示表分區(qū)信息 顯示數(shù)據(jù)庫所有分區(qū)表的詳細(xì)分區(qū)信息:select * from DBA TAB PARTITIONS-顯示當(dāng)前用戶可訪問的所有分區(qū)表的詳細(xì)分區(qū)信息:select * from ALL TAB PARTITIONS-顯