Oracle資料表空間及分區表

標籤：

(1) 資料表空間及分區表的概念
資料表空間：
　　是一個或多個資料檔案的集合，所有的資料對象都存放在指定的資料表空間中，但主要存放的是表， 所以稱作資料表空間。
分區表： 
當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分區。表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個“資料表空間”(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表而只是從當前的分區查到所要的資料大大提高了資料查詢的速度。

(2).表分區的具體作用
Oracle的表資料分割函數通過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的效能大大提高。此外,分區還可以極大簡化常見的管理工作，分區是構建千MB資料系統或超高可用性系統的關鍵工具。 資料分割函數能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分區。每個分區有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分區後的對象時有相當大的靈活性。但是，從應用程式的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 命令訪問分區後的表時，無需任何修改。
什麼時候使用分區表：
1、表的大小超過2GB。
2、表中包含曆史資料，新的資料被增加到新的分區中。

(3).表分區的優缺點
表分區有以下優點： 
1、改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。
2、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；
3、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；
4、均衡I/O：可以把不同的分區映射到不同磁碟以平衡I/O，改善整個系統效能。
缺點： 
分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能。

(4).表分區的幾種類型及操作方法

一.定界分割：
定界分割將資料基於範圍映射到每一個分區，這個範圍是你在建立分區時指定的分區鍵決定的。這種分區方式是最為常用的，並且分區鍵經常採用日期。舉個例子：你可能會將銷售資料按照月份進行分區。
當使用定界分割時，請考慮以下幾個規則：
1、每一個分區都必須有一個VALUES LESS THAN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等於或者大於這個上限值的記錄都會被加入到下一個高一些的分區中。
2、所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。
3、在最高的分區中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高於其它分區中的任何分區鍵的值，也可以理解為高於任何分區中指定的VALUE LESS THAN的值，同時包括空值。
例一：
假設有一個CUSTOMER表，表中有資料200000行，我們將此表通過CUSTOMER_ID進行分區，每個分區儲存100000行，我們將每個分區儲存到單獨的資料表空間中，這樣資料檔案就可以跨越多個物理磁碟。下面是建立表和分區的代碼，如下：

CREATE TABLE CUSTOMER 
( 
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
FIRST_NAME VARCHAR2(30) NOT NULL, 
LAST_NAME VARCHAR2(30) NOT NULL, 
PHONE VARCHAR2(15) NOT NULL, 
EMAIL VARCHAR2(80), 
STATUS CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
)

例二：按時間劃分

CREATE TABLE ORDER_ACTIVITIES 
( 
ORDER_ID NUMBER(7) NOT NULL, 
ORDER_DATE DATE, 
TOTAL_AMOUNT NUMBER, 
CUSTOTMER_ID NUMBER(7), 
PAID CHAR(1) 
) 
PARTITION BY RANGE (ORDER_DATE) 
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE(‘01- MAY -2003‘,‘DD-MON-YYYY‘)) TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(‘01-JUN-2003‘,‘DD-MON-YYYY‘)) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(‘01-JUL-2003‘,‘DD-MON-YYYY‘)) TABLESPACE ORD_TS03
)

例三：MAXVALUE

CREATE TABLE RangeTable
( 
idd INT PRIMARY KEY , 
iNAME VARCHAR(10), 
grade INT 
) 
PARTITION BY RANGE (grade) 
( 
PARTITION part1 VALUES LESS THAN (1000) TABLESPACE Part1_tb, 
PARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE Part2_tb 
);

二.列表分區：

該分區的特點是某列的值只有幾個，基於這樣的特點我們可以採用列表分區。
例一

CREATE TABLE PROBLEM_TICKETS 
( 
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, 
DESCRIPTION VARCHAR2(2000), 
CUSTOMER_ID NUMBER(7) NOT NULL, 
DATE_ENTERED DATE NOT NULL, 
STATUS VARCHAR2(20) 
) 
PARTITION BY LIST (STATUS) 
( 
PARTITION PROB_ACTIVE VALUES (‘ACTIVE‘) TABLESPACE PROB_TS01, 
PARTITION PROB_INACTIVE VALUES (‘INACTIVE‘) TABLESPACE PROB_TS02)

例二

CREATE TABLE ListTable
( 
id INT PRIMARY KEY , 
name VARCHAR (20), 
area VARCHAR (10) 
) 
PARTITION BY LIST (area) 
( 
PARTITION part1 VALUES (‘guangdong‘,‘beijing‘) TABLESPACE Part1_tb, 
PARTITION part2 VALUES (‘shanghai‘,‘nanjing‘) TABLESPACE Part2_tb 
);

三.散列分區（也稱HASH分區）：
這類分區是在列值上使用散列演算法，以確定將行放入哪個分區中。當列的值沒有合適的條件時，建議使用散列分區。
散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使得這些分區大小一致。
例一：

CREATE TABLE HASH_TABLE 
( 
COL NUMBER(8), 
INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
PARTITION PART01 TABLESPACE HASH_TS01, 
PARTITION PART02 TABLESPACE HASH_TS02, 
PARTITION PART03 TABLESPACE HASH_TS03 
)

簡寫：

CREATE TABLE emp
(
empno NUMBER (4),
ename VARCHAR2 (30),
sal NUMBER 
)
PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (tbs01,tbs02,tbs03,tbs04,tbs05,tbs06,tbs07,tbs08);

hash分區最主要的機制是根據hash演算法來計算具體某條紀錄應該插入到哪個分區中,hash演算法中最重要的是hash函數，Oracle中如果你要使用hash分區，只需指定分區的數量即可。建議分區的數量採用2的n次方，這樣可以使得各個分區間資料分布更加均勻。

四.組合範圍列表分區
這種分區是基於定界分割和列表分區，表首先按某列進行定界分割，然後再按某列進行列表分區，分區之中的分區被稱為子分區。

CREATE TABLE SALES 
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE(‘2003-01-01‘,‘YYYY-MM-DD‘))TABLESPACE rptfact2009 
( 
SUBPARTITION P1SUB1 VALUES (‘ACTIVE‘) TABLESPACE rptfact2009, 
SUBPARTITION P1SUB2 VALUES (‘INACTIVE‘) TABLESPACE rptfact2009 
), 
PARTITION P2 VALUES LESS THAN (TO_DATE(‘2003-03-01‘,‘YYYY-MM-DD‘)) TABLESPACE rptfact2009 
( 
SUBPARTITION P2SUB1 VALUES (‘ACTIVE‘) TABLESPACE rptfact2009, 
SUBPARTITION P2SUB2 VALUES (‘INACTIVE‘) TABLESPACE rptfact2009 
) 
)

五.複合範圍散列分區：
這種分區是基於定界分割和散列分區，表首先按某列進行定界分割，然後再按某列進行散列分區。

create table dinya_test 
( 
transaction_id number primary key, 
item_id number(8) not null, 
item_description varchar2(300), 
transaction_date date 
) 
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 
( 
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), 
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)), 
partition part_03 values less than(maxvalue) 
);

(5).有關表分區的一些維護性操作： 
一、添加分區 
以下代碼給SALES表添加了一個P3分區

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN (TO_DATE(‘2003-06-01‘,‘YYYY-MM-DD‘)) TABLESPACE SPACE_NAME;

注意：增加一個分區的時候，增加的分區的條件必須大於現有分區的最大值，否則系統將提示ORA-14074 partition bound must collate higher than that of the last partition 錯誤。
以下代碼給SALES表的P3分區添加了一個P3SUB1子分區

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES(‘COMPLETE‘) TABLESPACE SPACE_NAME;

二、刪除分區 
以下代碼刪除了SALES表中名為P3的分區：

ALTER TABLE SALES DROP PARTITION P3;

在以下代碼刪除了P4SUB1子分區：

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果刪除的分區是表中唯一的分區，那麼此分區將不能被刪除，要想刪除此分區，必須刪除表。
三、截斷分區 
截斷某個分區是指刪除某個分區中的資料，並不會刪除分區，也不會刪除其它分區中的資料。當表中即使只有一個分區時，也可以截斷該分區。通過以下代碼截斷分區：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通過以下代碼截斷子分區：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

四、合并分區 
合并分區是將相鄰的分區合并成一個分區，結果分區將採用較高分區的界限，值得注意的是，不能將分區合并到界限較低的分區。以下代碼實現了P1 P2分區的合并：

ALTER TABLE SALES MERGE PARTITIONS P1, P2 INTO PARTITION P2;

五、拆分分區 
拆分分區將一個分區拆分兩個新分區，拆分後原來分區不再存在但是如果表存在PMAX分區那麼原來的分區還是可以存在的。注意不能對 HASH類型的分區進行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE(‘2003-02-01‘,‘YYYY-MM-DD‘)) INTO (PARTITION P21,PARTITION P22);
此拆分語句是將P2分區拆分為PARTITION P21與PARTITION P22兩個分區，日期小於2003-02-01的資料存於 P21分區中否則存於 P22分區中。

--包含PMAX分區的表拆分分區
ALTER TABLE hs_his.HISHOLDSINFO SPLIT PARTITION PMAX AT(20100900) INTO (partition P201008, partition PMAX);
此拆分語句是將 PMAX 分區拆分為P201008與 PMAX 兩個分區，資料小於20100900 的資料存於 P201008 分區中否則存於 PMAX 分區中。

六、接合分區(coalesca) 
結合分區是將散列分區中的資料接合到其它分區中，當散列分區中的資料比較大時，可以增加散列分區，然後進行接合，值得注意的是，接合分區只能用於散列分區中。通過以下代碼進行接合分區：

ALTER TABLE SALES COALESCA PARTITION;

七、重新命名表分區 
以下代碼將P21更改為P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

Oracle資料表空間及分區表