Oracle分區介紹

一、簡介：Oracle 分區功能可以提高許多應用程序的可管理性、性能與可用性。通過分區功能，可以將表、索引和索引組織表進一步細分為段，從而能夠更精確地管理和訪問這些數據庫對象。Oracle 提供了種類繁多的分區方案以滿足每種業務要求。而且，因為在 SQL 語句中分區是完全透明的，所以該功能幾乎可應用于任何應用程序。

分區功能的優勢：分區功能通過改善可管理性、性能和可用性，從而為各式應用程序帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的性能大大提高。此 外,分區還可以極大簡化常見的管理任務。通過分區,數據庫設計人員和管理員能夠解決前沿應用程序帶來的一些難題。分區是構建千兆字節數據系統或超高可用性系統的關鍵工具。

二、分區功能的基本知識 ：分區功能能夠將表、索引或索引組織表進一步細分為段。這些數據庫對象的段叫做分區。每個分區有自己的名稱，還可以選擇自己的存儲特性。從數據庫管理員的角度來看，一個分區后的對象具有多個段，這些段既可進行集體管理，也可單獨管理。這就使數據庫管理員在管理分區后的對象時有相當大的靈活性。但是，從應用程序的角度來看，分區后的表與非分區表完全相同，使用 SQL DML 命令訪問分區后的表時，無需任何修改。表的分區是通過“分區鍵”來實現的，分區鍵指的是一些列，這些列決定了某一行所在的分區。Oracle 數據庫 10g 提供了幾項技術用于對表進行分區：

1、范圍分區（range）：每個分區都由一個分區鍵值范圍指定（對于一個以日期列作為分區鍵的表，“2005 年 1 月”分區包含分區鍵值為從“2005 年 1 月 1 日”到“2005 年 1 月 31 日”的行）。

當使用范圍分區時，請考慮以下幾個規則：

1)每一個分區都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等于或者大于這個上限值的記錄都會被加入到下一個高一些的分區中。

2)所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。

3）在最高的分區中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高于其它分區中的任何分區鍵的值，也可以理解為高于任何分區中指定的VALUE LESS THEN的值，同時包括空值。

例一：

假設有一個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? THEN (1000) TABLESPACE? Part1_tb,

PARTITION? part2 VALUES? LESS? THEN (MAXVALUE) TABLESPACE? Part2_tb

);

2、列表分區（list）：每個分區都由一個分區鍵值列表指定（對于一個地區列作為分區鍵的表，“北美”分區可能包含值“加拿大”“美國”和“墨西哥”）。 該分區的特點是某列的值只有幾個，基于這樣的特點我們可以采用列表分區。

例一

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

);

)

3、散列分區（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 (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

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

4、組合范圍散列分區（range-hash）：范圍和散列分區技術的組合，通過該組合，首先對表進行范圍分區，然后針對每個單獨的范圍分區再使用散列分區技術進一步細分。索引組織表只能進行范圍分區。這種分區是基于范圍分區和列表分區，表首先按某列進行范圍分區，然后再按某列進行列表分區，分區之中的分區被稱為子分區。

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

)

)

5、組合范圍列表分區（range-list）：范圍和列表分區技術的組合，通過該組合，首先對表進行范圍分區，然后針對每個單獨的范圍分區再使用列表分區技術進一步細分。索引組織表可以按范圍、列表或散列進行分區。這種分區是基于范圍分區和散列分區，表首先按某列進行范圍分區，然后再按某列進行散列分區。

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)

);

三、Oracle 數據庫 10g 還提供了三種類型的分區索引：

1、本地索引：本地索引是其分區方式與其所在基礎表的分區方式一模一樣的索引。本地索引的每個分區僅對應于其所在基礎表的一個分區。

2、全局分區索引：全局分區索引是使用不同于其所在表的分區鍵進行分區的索引，其所在表可以是分區表或非分區表。全局分區的索引可以使用范圍或散列分區進行分區。例如，某個表可以按月份進行范圍分區，因此具有十二個分區，而該表上的索引則可以使用不同的分區鍵進行范圍分區，從而具有不同的分區數量。

3、全局非分區索引：全局非分區索引基本上和非分區表的索引一樣。索引結構是不分區的。

四、Oracle 提供了一系列豐富的技術，可用于對表、索引和索引組織表進行分區，因此可以針對任何業務環境中的任何應用程序進行最佳的分區 Oracle 還提供一套完整的 SQL 命令，用于管理分區表。其中包括添加新分區、刪除分區、分拆分區以及合并分區的命令。

分區的優點：

1、用分區功能提高可管理性

通過 Oracle 分區功能，可將表和索引分成更多、更小的可管理單元，從而使數據庫管理員能以“化整為零，個個擊破”的方式管理數據。使用分區功能，維護操作可集中于表的特定部分。例如，數據庫管理員可以只對表的一部分做備份，而不必對整個表做備份。對整個數據庫對象的維護操作，可以在每個分區的基礎上進行，從而將維護工作分解成更容易管理的小塊。利用分區功能提高可管理性的一個典型用法是支持數據倉庫中的‘滾動視窗’加載進程。假設數據庫管理員每周向表中加載新數據。可以對該表進行范 圍分區，使每個分區包含一周的數據。這樣加載進程只是簡單地添加新的分區。添加一個分區的操作比修改整個表效率高很多，因為 DBA 不需要修改任何其他分區。

2、用分區功能提高性能

由于限制了所檢查或操作的數據數量，同時支持并行任務執行，Oracle 分區功能實現了性能上增益。這些特性包括：

1）分區修整：分區修整是用分區功能提高性能的最簡單最有價值的手段。分區修整常常能夠將查詢性能提高幾個數量級。例如，假設某個應用程序包含一個存儲訂單 歷史記錄的 Orders 表，并且此表已按周分區。查詢一周的訂單只需訪問該訂單表的一個分區。如果該訂單表包含兩年的歷史記錄，這個查詢只需要訪問一個分區而不是一百零四個。該 查詢的執行速度因為分區修整而有可能快一百倍。分區修整能與所有其他 Oracle 性能特性協作。Oracle 能將分區修整功能與任何索引技術、聯接技術或并行訪問方法結合使用。

2）分區智能聯接：分區功能可以通過稱為分區智能聯接的技術提高多表聯接的性能。當兩個表要聯接在一起，而且每個表都用聯接鍵來分區時，就可以使用分區智能聯 接。分區智能聯接將大型聯接分解成較小的發生在各個分區間的聯接，從而用較少的時間完成全部聯接。這就給串行和并行的執行都能帶來顯著的性能改善。

3、用分區功能提高可用性

分區的數據庫對象具有分區獨立性。該分區獨立性特點可能是高可用性戰略的一個重要部分，例如，如果分區表的一個分區不能用，但該表的所有其他 分區仍然保持在線并可用。那么這個應用可以繼續針對該分區表執行查詢和事務處理，只要不是訪問不可用的分區，數據庫操作仍然能夠成功運行。數據庫管理員可以指定各分區存放在不同的表空間里，從而讓管理員隔離其它表分區針對單個分區進行備份與恢復操作。還有，分區功能可以減少計劃停機時間。由于分區功能改善了性能，使數據庫管理員能用相對較少的時間完成大型數據庫對象的維護工作。

五、有關表分區的一些維護性操作：

1、添加分區

以下代碼給SALES表添加了一個P3分區

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

注意：以上添加的分區界限應該高于最后一個分區界限。

以下代碼給SALES表的P3分區添加了一個P3SUB1子分區

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

2、刪除分區

以下代碼刪除了P3表分區：

ALTER TABLE SALES DROP PARTITION P3;

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

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果刪除的分區是表中唯一的分區，那么此分區將不能被刪除，要想刪除此分區，必須刪除表。

3、截斷分區

截斷某個分區是指刪除某個分區中的數據，并不會刪除分區，也不會刪除其它分區中的數據。當表中即使只有一個分區時，也可以截斷該分區。通過以下代碼截斷分區：

ALTER TABLE SALES TRUNCATE PARTITION P2;

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

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4、合并分區

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

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

5、拆分分區

拆分分區將一個分區拆分兩個新分區，拆分后原來分區不再存在。注意不能對HASH類型的分區進行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6、接合分區(coalesca)

結合分區是將散列分區中的數據接合到其它分區中，當散列分區中的數據比較大時，可以增加散列分區，然后進行接合，值得注意的是，接合分區只能用于散列分區中。通過以下代碼進行接合分區：

ALTER TABLE SALES COALESCA PARTITION;

7、重命名表分區

以下代碼將P21更改為P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8、相關查詢

跨分區查詢

select sum( *) from

(select count(*) cn from t_table_SS PARTITION (P200709_1)

union all

select count(*) cn from t_table_SS PARTITION (P200709_2)

);

查詢表上有多少分區

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查詢索引信息

select object_name,object_type,tablespace_name,sum(value)

from v$segment_statistics

where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'

group by object_name,object_type,tablespace_name

order by 4 desc

--顯示數據庫所有分區表的信息：

select * from DBA_PART_TABLES

--顯示當前用戶可訪問的所有分區表信息:

select * from ALL_PART_TABLES

--顯示當前用戶所有分區表的信息：

select * from USER_PART_TABLES

--顯示表分區信息 顯示數據庫所有分區表的詳細分區信息：

select * from DBA_TAB_PARTITIONS

--顯示當前用戶可訪問的所有分區表的詳細分區信息：

select * from ALL_TAB_PARTITIONS

--顯示當前用戶所有分區表的詳細分區信息：

select * from USER_TAB_PARTITIONS

--顯示子分區信息 顯示數據庫所有組合分區表的子分區信息：

select * from DBA_TAB_SUBPARTITIONS

--顯示當前用戶可訪問的所有組合分區表的子分區信息：

select * from ALL_TAB_SUBPARTITIONS

--顯示當前用戶所有組合分區表的子分區信息：

select * from USER_TAB_SUBPARTITIONS

--顯示分區列 顯示數據庫所有分區表的分區列信息：

select * from DBA_PART_KEY_COLUMNS

--顯示當前用戶可訪問的所有分區表的分區列信息：

select * from ALL_PART_KEY_COLUMNS

--顯示當前用戶所有分區表的分區列信息：

select * from USER_PART_KEY_COLUMNS

--顯示子分區列 顯示數據庫所有分區表的子分區列信息：

select * from DBA_SUBPART_KEY_COLUMNS

--顯示當前用戶可訪問的所有分區表的子分區列信息：

select * from ALL_SUBPART_KEY_COLUMNS

--顯示當前用戶所有分區表的子分區列信息：

select * from USER_SUBPART_KEY_COLUMNS

--怎樣查詢出oracle數據庫中所有的的分區表

select * from user_tables a where a.partitioned='YES'

--刪除一個表的數據是

truncate table table_name;

--刪除分區表一個分區的數據是

alter table table_name truncate partition p5;

在Oracle 11g中，組合分區功能這塊有所增強，又增加了range-range,list-range,list-list,list-hash，并且 11g里面還支持Interval分區和虛擬列分

文章轉自異步社區

軟件開發

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。