創(chuàng)建分區(qū)表：

oracle分區(qū)方法：range、hash、list和composite partition；

range分區(qū)表示例：

CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30),

sales_amount NUMBER(10), sales_date DATE) COMPRESS PARTITION BY RANGE(sales_date)

(

PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),

PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),

PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),

PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));

分區(qū)字段values less than必須是確定值,最后一個可以是maxvalue,每個分區(qū)可以單獨指定物理屬性

range分區(qū)特點：

最早、最經(jīng)典的分區(qū)方法

Range分區(qū)通過對分區(qū)字段值的范圍進行分區(qū)

Range分區(qū)特別適合于按時間周期進行數(shù)據(jù)的存儲：日、周、月、年等

數(shù)據(jù)管理能力強

數(shù)據(jù)遷移

數(shù)據(jù)備份

數(shù)據(jù)交換

范圍分區(qū)的數(shù)據(jù)可能不均勻

范圍分區(qū)與記錄值有關(guān)，實施難度和可維護性相對較差

hash分區(qū)表示例：

create table emp_t(empno integer,ename varchar2(20))

partition by hash(empno)

partitions 4;--指定分區(qū)所在表空間(partition part_01 tablespace test,partition part_02 tablespace sys);

list分區(qū)表示例：

CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30),

sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE)

PARTITION BY LIST(sales_state)

(

PARTITION sales_west VALUES('California', 'Hawaii'),

PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),

PARTITION sales_central VALUES('Texas', 'Illinois'),

PARTITION sales_other VALUES(DEFAULT));

list分區(qū)特點：

List分區(qū)通過對分區(qū)字段的離散值進行分區(qū)

List分區(qū)是不排序的，而且分區(qū)之間也沒有關(guān)聯(lián)

List分區(qū)適合于對數(shù)據(jù)離散值進行控制

List分區(qū)只支持單個字段

List分區(qū)具有與range分區(qū)相似的優(yōu)缺點：

數(shù)據(jù)管理能力強

各分區(qū)的數(shù)據(jù)可能不均勻

composite分區(qū)表示例：

CREATE TABLE quarterly_regional_sales (deptno NUMBER, item_no VARCHAR2(20),

txn_date DATE, txn_amount NUMBER, state VARCHAR2(2))

PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state)

( PARTITION q1_1999 VALUES LESS THAN(TO_DATE('1-APR-1999','DD-MON-YYYY'))

(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),

SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),

SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),

SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),

SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),

SUBPARTITION q1_1999_southcentral VALUES ('NM', 'TX')),

PARTITION q2_1999 VALUES LESS THAN(TO_DATE('1-JUL-1999','DD-MON-YYYY'))

(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),

SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),

SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),

SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),

SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),

SUBPARTITION q2_1999_southcentral VALUES ('NM', 'TX')),

PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))

(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),

… ….

SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),

SUBPARTITION q4_1999_southcentral VALUES ('NM', 'TX')));

composite分區(qū)特點：

Oracle支持的Composite分區(qū)：

range-hash

Range-list

既適合于歷史數(shù)據(jù)，又適合于數(shù)據(jù)均勻分布

與范圍分區(qū)一樣提高可用性和可管理性

更好的PDML和partition-wise joins特性

實現(xiàn)粒度更細(xì)的操作

支持符合local indexes

不支持符合 global indexes

分區(qū)表設(shè)計原則：

表的大小：當(dāng)表的大小超過2GB，或?qū)τ贠LTP系統(tǒng)，表記錄超過1000萬時，都應(yīng)該考慮對表進行分區(qū)

數(shù)據(jù)訪問特性：基于表的大部分查詢應(yīng)用，只訪問表中少量的數(shù)據(jù)。對于這樣的表進行分區(qū)，可充分利用分區(qū)排除無關(guān)數(shù)據(jù)查詢的特性

數(shù)據(jù)維護：按時間段刪除成批的數(shù)據(jù)，對于這樣的表需要考慮進行分區(qū)，以滿足維護需要

數(shù)據(jù)備份和恢復(fù)：按時間周期進行表空間備份時，將分區(qū)與表空間建立對應(yīng)關(guān)系

只讀數(shù)據(jù)：如果一個表中大部分?jǐn)?shù)據(jù)都是只讀，通過對表進行分區(qū)，可將只讀數(shù)據(jù)存儲在只讀表空間，對于數(shù)據(jù)的備份有利

OLAP并行數(shù)據(jù)操作

分區(qū)表及索引分區(qū)數(shù)據(jù)字典信息：

--1、查詢當(dāng)前用戶下有哪些是分區(qū)表：

SELECT * FROM USER_PART_TABLES;

--2、查詢當(dāng)前用戶下有哪些分區(qū)索引：

SELECT * FROM USER_PART_INDEXES;

--3、查詢當(dāng)前用戶下分區(qū)索引的分區(qū)信息：

SELECT *

FROM USER_IND_PARTITIONS T

WHERE T.INDEX_NAME = ?

--4、查詢當(dāng)前用戶下分區(qū)表的分區(qū)信息：

SELECT * FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME = ?;

--5、查詢某分區(qū)下的數(shù)據(jù)量：

SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);

--6、查詢索引、表上在那些列上創(chuàng)建了分區(qū)：

SELECT * FROM USER_PART_KEY_COLUMNS;

--7、查詢某用戶下二級分區(qū)的信息(只有創(chuàng)建了二級分區(qū)才有數(shù)據(jù))：

SELECT * FROM USER_TAB_SUBPARTITIONS;

--查看某一分區(qū)上的數(shù)據(jù)

select * from user_table partition(partitionname);

分區(qū)表維護：

--刪除分區(qū)

ALTER TABLE table_name DROP PARTITION partition_name;

--說明：此語句不可用于hash分區(qū)表，如果是全局索引，因為全局索引的分區(qū)結(jié)構(gòu)和表可以不一致，若不一致的情況下，會導(dǎo)致整個全局索引失效，在刪除分區(qū)的時候，語句修改為：

ALTER TABLE table_name DROP PARTITION partition_name UPDATE GLOBAL INDEXES;

--合并分區(qū)(coalesce partition)：合并分區(qū)是用來操作HASH分區(qū)表和hash全局索引的，它會重新分配刪除的分區(qū)的數(shù)據(jù)到現(xiàn)有的分區(qū)中

ALTER TABLE table_name COALESCE PARTITION;

--合并分區(qū)和刪除中間的RANGE有點像，但是合并分區(qū)是不會刪除數(shù)據(jù)的，對于LIST、HASH分區(qū)也是和RANGE分區(qū)不一樣的，其語法為：

ALTER TABLE table_name MERGE PARTITIONS??? partition_name1,partition_name2 INTO PARTITION MERGED_PARTITION;

--創(chuàng)建新的分區(qū)(分區(qū)數(shù)據(jù)若不能提供范圍，則插入時會報錯，需要增加分區(qū)來擴大范圍)

ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN(2500000);--list分區(qū)or RANGE分區(qū)

ALTER TABLE table_name ADD PARTITION partition_name;--HASH分區(qū)

--創(chuàng)建子分區(qū)：在分區(qū)下創(chuàng)建新的子分區(qū)大致如下(RANGE分區(qū)，若為LIST或HASH分區(qū)，將創(chuàng)建方式修改為對應(yīng)的方式即可)

ALTER TABLE MODIFY PARTITION ADD SUBPARTITION VALUES LESS THAN(....);

--修改分區(qū)名稱(修改相關(guān)的屬性信息):

ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;

--交換分區(qū)(快速交換數(shù)據(jù)，其實是交換段名稱指針)，首先創(chuàng)建一個交換表，和原表結(jié)構(gòu)相同，如果有數(shù)據(jù)，必須符合所交換對應(yīng)分區(qū)的條件

CREATE TABLE TABLE_PARTITION_2

AS SELECT * FROM TABLE_PARTITION WHERE 1=2;

--然后將第一個分區(qū)的數(shù)據(jù)交換出去

ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01

WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES;

--此時會發(fā)現(xiàn)第一個分區(qū)的數(shù)據(jù)和表TABLE_PARTITION_2做了瞬間交換，比TRUNCATE還要快，因為這個過程沒有進行數(shù)據(jù)轉(zhuǎn)存，只是段名稱的修改過程，和實際的數(shù)據(jù)量沒有關(guān)系。

--如果是子分區(qū)也可以與外部的表進行交換，只需要將關(guān)鍵字修改為：SUBPARTITION 即可。

--清空分區(qū)數(shù)據(jù)

ALTER TABLE TRUNCATE PARTITION ;

ALTER TABLE TRUNCATE subpartition ;

總結(jié)

以上是生活随笔為你收集整理的oracle表分区设计_论oracle分区表的创建与维护的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。