共計(jì) 7646 個(gè)字符，預(yù)計(jì)需要花費(fèi) 20 分鐘才能閱讀完成。
這篇文章將為大家詳細(xì)講解有關(guān) oracle 中分表之表分區(qū)的示例分析，丸趣 TV 小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。
此文從以下幾個(gè)方面來(lái)整理關(guān)于分區(qū)表的概念及操作:
1. 表空間及分區(qū)表的概念
2. 表分區(qū)的具體作用
3. 表分區(qū)的優(yōu)缺點(diǎn)
4. 表分區(qū)的幾種類(lèi)型及操作方法
5. 對(duì)表分區(qū)的維護(hù)性操作.
(1.) 表空間及分區(qū)表的概念
表空間：
是一個(gè)或多個(gè)數(shù)據(jù)文件的集合，所有的數(shù)據(jù)對(duì)象都存放在指定的表空間中，但主要存放的是表，所以稱(chēng)作表空間。
分區(qū)表：
當(dāng)表中的數(shù)據(jù)量不斷增大，查詢(xún)數(shù)據(jù)的速度就會(huì)變慢，應(yīng)用程序的性能就會(huì)下降，這時(shí)就應(yīng)該考慮對(duì)表進(jìn)行分區(qū)。表進(jìn)行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個(gè)表空間 (物理文件上)，這樣查詢(xún)數(shù)據(jù)時(shí)，不至于每次都掃描整張表。
(2). 表分區(qū)的具體作用
Oracle 的表分區(qū)功能通過(guò)改善可管理性、性能和可用性，從而為各式應(yīng)用程序帶來(lái)了極大的好處。通常，分區(qū)可以使某些查詢(xún)以及維護(hù)操作的性能大大提高。此外, 分區(qū)還可以極大簡(jiǎn)化常見(jiàn)的管理任務(wù)，分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用性系統(tǒng)的關(guān)鍵工具。
分區(qū)功能能夠?qū)⒈怼⑺饕蛩饕M織表進(jìn)一步細(xì)分為段，這些數(shù)據(jù)庫(kù)對(duì)象的段叫做分區(qū)。每個(gè)分區(qū)有自己的名稱(chēng)，還可以選擇自己的存儲(chǔ)特性。從數(shù)據(jù)庫(kù)管理員的角度來(lái)看，一個(gè)分區(qū)后的對(duì)象具有多個(gè)段，這些段既可進(jìn)行集體管理，也可單獨(dú)管理，這就使數(shù)據(jù)庫(kù)管理員在管理分區(qū)后的對(duì)象時(shí)有相當(dāng)大的靈活性。但是，從應(yīng)用程序的角度來(lái)看，分區(qū)后的表與非分區(qū)表完全相同，使用 SQL DML 命令訪(fǎng)問(wèn)分區(qū)后的表時(shí)，無(wú)需任何修改。
什么時(shí)候使用分區(qū)表：
1、表的大小超過(guò) 2GB。
2、表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加都新的分區(qū)中。
(3). 表分區(qū)的優(yōu)缺點(diǎn)
表分區(qū)有以下優(yōu)點(diǎn)：
1、改善查詢(xún)性能：對(duì)分區(qū)對(duì)象的查詢(xún)可以?xún)H搜索自己關(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ū)映射到磁盤(pán)以平衡 I /O，改善整個(gè)系統(tǒng)性能。
缺點(diǎn)：
分區(qū)表相關(guān)：已經(jīng)存在的表沒(méi)有方法可以直接轉(zhuǎn)化為分區(qū)表。不過(guò) Oracle 提供了在線(xiàn)重定義表的功能。
(4). 表分區(qū)的幾種類(lèi)型及操作方法
一. 范圍分區(qū)：
范圍分區(qū)將數(shù)據(jù)基于范圍映射到每一個(gè)分區(qū)，這個(gè)范圍是你在創(chuàng)建分區(qū)時(shí)指定的分區(qū)鍵決定的。這種分區(qū)方式是最為常用的，并且分區(qū)鍵經(jīng)常采用日期。舉個(gè)例子：你可能會(huì)將銷(xiāo)售數(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 代表了一個(gè)不確定的值。這個(gè)值高于其它分區(qū)中的任何分區(qū)鍵的值，也可以理解為高于任何分區(qū)中指定的 VALUE LESS THEN 的值，同時(shí)包括空值。
例一：
假設(shè)有一個(gè) CUSTOMER 表，表中有數(shù)據(jù) 200000 行，我們將此表通過(guò) CUSTOMER_ID 進(jìn)行分區(qū)，每個(gè)分區(qū)存儲(chǔ) 100000 行，我們將每個(gè)分區(qū)保存到單獨(dú)的表空間中，這樣數(shù)據(jù)文件就可以跨越多個(gè)物理磁盤(pán)。下面是創(chuàng)建表和分區(qū)的代碼，如下：
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
)例二：按時(shí)間劃分
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
);二. 列表分區(qū)：
該分區(qū)的特點(diǎn)是某列的值只有幾個(gè)，基于這樣的特點(diǎn)我們可以采用列表分區(qū)。
例一
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
)三. 散列分區(qū)：
這類(lèi)分區(qū)是在列值上使用散列算法，以確定將行放入哪個(gè)分區(qū)中。當(dāng)列的值沒(méi)有合適的條件時(shí)，建議使用散列分區(qū)。
散列分區(qū)為通過(guò)指定分區(qū)編號(hào)來(lái)均勻分布數(shù)據(jù)的一種分區(qū)類(lèi)型，因?yàn)橥ㄟ^(guò)在 I / O 設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小一致。
例一：
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
)簡(jiǎn)寫(xiě)：
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 分區(qū)最主要的機(jī)制是根據(jù) hash 算法來(lái)計(jì)算具體某條紀(jì)錄應(yīng)該插入到哪個(gè)分區(qū)中,hash 算法中最重要的是 hash 函數(shù)，Oracle 中如果你要使用 hash 分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用 2 的 n 次方，這樣可以使得各個(gè)分區(qū)間數(shù)據(jù)分布更加均勻。
四. 組合范圍散列分區(qū)
這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進(jìn)行范圍分區(qū)，然后再按某列進(jìn)行列表分區(qū)，分區(qū)之中的分區(qū)被稱(chēng)為子分區(qū)。
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
)
)五. 復(fù)合范圍散列分區(qū)：
這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進(jìn)行范圍分區(qū)，然后再按某列進(jìn)行散列分區(qū)。
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). 有關(guān)表分區(qū)的一些維護(hù)性操作：
一、添加分區(qū)
以下代碼給 SALES 表添加了一個(gè) P3 分區(qū)
復(fù)制代碼 代碼如下:
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE( 2003-06-01 , YYYY-MM-DD
注意：以上添加的分區(qū)界限應(yīng)該高于最后一個(gè)分區(qū)界限。
以下代碼給 SALES 表的 P3 分區(qū)添加了一個(gè) P3SUB1 子分區(qū)
復(fù)制代碼 代碼如下:
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES(COMPLETE
二、刪除分區(qū)
以下代碼刪除了 P3 表分區(qū)：
ALTER TABLE SALES DROP PARTITION P3;在以下代碼刪除了 P4SUB1 子分區(qū)：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;注意：如果刪除的分區(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ū)。通過(guò)以下代碼截?cái)喾謪^(qū)：
ALTER TABLE SALES TRUNCATE PARTITION P2;通過(guò)以下代碼截?cái)嘧臃謪^(qū)：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;四、合并分區(qū)
合并分區(qū)是將相鄰的分區(qū)合并成一個(gè)分區(qū)，結(jié)果分區(qū)將采用較高分區(qū)的界限，值得注意的是，不能將分區(qū)合并到界限較低的分區(qū)。以下代碼實(shí)現(xiàn)了 P1 P2 分區(qū)的合并：
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;五、拆分分區(qū)
拆分分區(qū)將一個(gè)分區(qū)拆分兩個(gè)新分區(qū)，拆分后原來(lái)分區(qū)不再存在。注意不能對(duì) HASH 類(lèi)型的分區(qū)進(jìn)行拆分。
復(fù)制代碼 代碼如下:
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE( 2003-02-01 , YYYY-MM-DD)) INTO (PARTITION P21,PARTITION P22);
六、接合分區(qū) (coalesca)
結(jié)合分區(qū)是將散列分區(qū)中的數(shù)據(jù)接合到其它分區(qū)中，當(dāng)散列分區(qū)中的數(shù)據(jù)比較大時(shí)，可以增加散列分區(qū)，然后進(jìn)行接合，值得注意的是，接合分區(qū)只能用于散列分區(qū)中。通過(guò)以下代碼進(jìn)行接合分區(qū)：
ALTER TABLE SALES COALESCA PARTITION;七、重命名表分區(qū)
以下代碼將 P21 更改為 P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;八、相關(guān)查詢(xún)
跨分區(qū)查詢(xún)
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)
);查詢(xún)表上有多少分區(qū)
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME= tableName查詢(xún)索引信息
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– 顯示數(shù)據(jù)庫(kù)所有分區(qū)表的信息：
select * from DBA_PART_TABLES– 顯示當(dāng)前用戶(hù)可訪(fǎng)問(wèn)的所有分區(qū)表信息:
select * from ALL_PART_TABLES– 顯示當(dāng)前用戶(hù)所有分區(qū)表的信息：
select * from USER_PART_TABLES– 顯示表分區(qū)信息 顯示數(shù)據(jù)庫(kù)所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from DBA_TAB_PARTITIONS– 顯示當(dāng)前用戶(hù)可訪(fǎng)問(wèn)的所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from ALL_TAB_PARTITIONS– 顯示當(dāng)前用戶(hù)所有分區(qū)表的詳細(xì)分區(qū)信息：
select * from USER_TAB_PARTITIONS– 顯示子分區(qū)信息 顯示數(shù)據(jù)庫(kù)所有組合分區(qū)表的子分區(qū)信息：
select * from DBA_TAB_SUBPARTITIONS– 顯示當(dāng)前用戶(hù)可訪(fǎng)問(wèn)的所有組合分區(qū)表的子分區(qū)信息：
select * from ALL_TAB_SUBPARTITIONS– 顯示當(dāng)前用戶(hù)所有組合分區(qū)表的子分區(qū)信息：
select * from USER_TAB_SUBPARTITIONS– 顯示分區(qū)列 顯示數(shù)據(jù)庫(kù)所有分區(qū)表的分區(qū)列信息：
select * from DBA_PART_KEY_COLUMNS– 顯示當(dāng)前用戶(hù)可訪(fǎng)問(wèn)的所有分區(qū)表的分區(qū)列信息：
select * from ALL_PART_KEY_COLUMNS– 顯示當(dāng)前用戶(hù)所有分區(qū)表的分區(qū)列信息：
select * from USER_PART_KEY_COLUMNS– 顯示子分區(qū)列 顯示數(shù)據(jù)庫(kù)所有分區(qū)表的子分區(qū)列信息：
select * from DBA_SUBPART_KEY_COLUMNS– 顯示當(dāng)前用戶(hù)可訪(fǎng)問(wèn)的所有分區(qū)表的子分區(qū)列信息：
select * from ALL_SUBPART_KEY_COLUMNS– 顯示當(dāng)前用戶(hù)所有分區(qū)表的子分區(qū)列信息：
select * from USER_SUBPART_KEY_COLUMNS– 怎樣查詢(xún)出 oracle 數(shù)據(jù)庫(kù)中所有的的分區(qū)表
select * from user_tables a where a.partitioned= YES– 刪除一個(gè)表的數(shù)據(jù)是
truncate table table_name;– 刪除分區(qū)表一個(gè)分區(qū)的數(shù)據(jù)是
alter table table_name truncate partition p5;關(guān)于“oracle 中分表之表分區(qū)的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。
