oracle表分區詳解

　　此文從以下幾個方面來整理關於分區表的概念及操作:

　　表空間及分區表的概念

　　表分區的具體作用

　　表分區的優缺點

　　表分區的幾種類型及操作方法

　　對表分區的維護性操作

　　() 表空間及分區表的概念

　　表空間

　　是一個或多個數據文件的集合所有的數據對象都存放在指定的表空間中但主要存放的是表 所以稱作表空間

　　分區表

　　當表中的數據量不斷增大查詢數據的速度就會變慢應用程序的性能就會下降這時就應該考慮對表進行分區表進行分區後邏輯上表仍然是一張完整的表只是將表中的數據在物理上存放到多個表空間(物理文件上)這樣查詢數據時不至於每次都掃描整張表

　　( )表分區的具體作用

　　Oracle的表分區功能通過改善可管理性性能和可用性從而為各式應用程序帶來了極大的好處通常分區可以使某些查詢以及維護操作的性能大大提高此外分區還可以極大簡化常見的管理任務分區是構建千兆字節數據系統或超高可用性系統的關鍵工具

　　分區功能能夠將表索引或索引組織表進一步細分為段這些數據庫對象的段叫做分區每個分區有自己的名稱還可以選擇自己的存儲特性從數據庫管理員的角度來看一個分區後的對象具有多個段這些段既可進行集體管理也可單獨管理這就使數據庫管理員在管理分區後的對象時有相當大的靈活性但是從應用程序的角度來看分區後的表與非分區表完全相同使用 SQL DML 命令訪問分區後的表時無需任何修改

　　什麼時候使用分區表

　　表的大小超過GB

　　表中包含歷史數據新的數據被增加都新的分區中

　　()表分區的優缺點

　　表分區有以下優點

　　改善查詢性能對分區對象的查詢可以僅搜索自己關心的分區提高檢索速度

　　增強可用性如果表的某個分區出現故障表在其他分區的數據仍然可用

　　維護方便如果表的某個分區出現故障需要修復數據只修復該分區即可

　　均衡I/O可以把不同的分區映射到磁盤以平衡I/O改善整個系統性能

　　缺點

　　分區表相關已經存在的表沒有方法可以直接轉化為分區表不過 Oracle 提供了在線重定義表的功能

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

　　一范圍分區

　　范圍分區將數據基於范圍映射到每一個分區這個范圍是你在創建分區時指定的分區鍵決定的這種分區方式是最為常用的並且分區鍵經常采用日期舉個例子你可能會將銷售數據按照月份進行分區

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

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

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

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

　　例一

　　假設有一個CUSTOMER表表中有數據行我們將此表通過CUSTOMER_ID進行分區每個分區存儲行我們將每個分區保存到單獨的表空間中這樣數據文件就可以跨越多個物理磁盤下面是創建表和分區的代碼如下

　　CREATE TABLE CUSTOMER

　　(

　　CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY

　　FIRST_NAME  VARCHAR() NOT NULL

　　LAST_NAME   VARCHAR() NOT NULL

　　PHONE        VARCHAR() NOT NULL

　　EMAIL        VARCHAR()

　　STATUS       CHAR()

　　)

　　PARTITION BY RANGE (CUSTOMER_ID)

　　(

　　PARTITION CUS_PART VALUES LESS THAN () TABLESPACE CUS_TS

　　PARTITION CUS_PART VALUES LESS THAN () TABLESPACE CUS_TS

　　)

　　例二按時間劃分

　　CREATE TABLE ORDER_ACTIVITIES

　　(

　　ORDER_ID      NUMBER() NOT NULL

　　ORDER_DATE    DATE

　　TOTAL_AMOUNT NUMBER

　　CUSTOTMER_ID NUMBER()

　　PAID           CHAR()

　　)

　　PARTITION BY RANGE (ORDER_DATE)

　　(

　　PARTITION ORD_ACT_PART VALUES LESS THAN (TO_DATE( MAY DDMONYYYY)) TABLESPACEORD_TS

　　PARTITION ORD_ACT_PART VALUES LESS THAN (TO_DATE(JUNDDMONYYYY)) TABLESPACE ORD_TS

　　PARTITION ORD_ACT_PART VALUES LESS THAN (TO_DATE(JULDDMONYYYY)) TABLESPACE ORD_TS

　　)

　　例三MAXVALUE

　　CREATE TABLE RangeTable

　　(

　　idd   INT PRIMARY KEY

　　iNAME VARCHAR()

　　grade INT

　　)

　　PARTITION  BY  RANGE (grade)

　　(

　　PARTITION  part VALUES  LESS  THEN () TABLESPACE  Part_tb

　　PARTITION  part VALUES  LESS  THEN (MAXVALUE) TABLESPACE  Part_tb

　　);

　　二列表分區

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

　　例一

　　CREATE TABLE PROBLEM_TICKETS

　　(

　　PROBLEM_ID   NUMBER() NOT NULL PRIMARY KEY

　　DESCRIPTION  VARCHAR()

　　CUSTOMER_ID  NUMBER() NOT NULL

　　DATE_ENTERED DATE NOT NULL

　　STATUS       VARCHAR()

　　)

　　PARTITION BY LIST (STATUS)

　　(

　　PARTITION PROB_ACTIVE   VALUES (ACTIVE) TABLESPACE PROB_TS

　　PARTITION PROB_INACTIVE VALUES (INACTIVE) TABLESPACE PROB_TS

　　例二

　　CREATE  TABLE  ListTable

　　(

　　id    INT  PRIMARY  KEY

　　name  VARCHAR ()

　　area  VARCHAR ()

　　)

　　PARTITION  BY  LIST (area)

　　(

　　PARTITION  part VALUES (guangdongbeijing) TABLESPACE  Part_tb

　　PARTITION  part VALUES (shanghainanjing)  TABLESPACE  Part_tb

　　);

　　)

　　三散列分區

　　這類分區是在列值上使用散列算法以確定將行放入哪個分區中當列的值沒有合適的條件時建議使用散列分區

　　散列分區為通過指定分區編號來均勻分布數據的一種分區類型因為通過在I/O設備上進行散列分區使得這些分區大小一致

　　例一

　　CREATE TABLE HASH_TABLE

　　(

　　COL NUMBER()

　　INF VARCHAR()

　　)

　　PARTITION BY HASH (COL)

　　(

　　PARTITION PART TABLESPACE HASH_TS

　　PARTITION PART TABLESPACE HASH_TS

　　PARTITION PART TABLESPACE HASH_TS

　　)

　　簡寫

　　CREATE TABLE emp

　　(

　　empno NUMBER ()

　　ename VARCHAR ()

　　sal   NUMBER

　　)

　　PARTITION BY  HASH (empno) PARTITIONS

　　STORE IN (empempempempempempempemp);

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

　　四組合范圍散列分區

　　這種分區是基於范圍分區和列表分區表首先按某列進行范圍分區然後再按某列進行列表分區分區之中的分區被稱為子分區

　　CREATE TABLE SALES

　　(

　　PRODUCT_ID VARCHAR()

　　SALES_DATE DATE

　　SALES_COST NUMBER()

　　STATUS VARCHAR()

　　)

　　PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)

　　(

　　PARTITION P VALUES LESS THAN(TO_DATE(YYYYMMDD))TABLESPACE rptfact

　　(

　　SUBPARTITION PSUB VALUES (ACTIVE) TABLESPACE rptfact

　　SUBPARTITION PSUB VALUES (INACTIVE) TABLESPACE rptfact

　　)

　　PARTITION P VALUES LESS THAN (TO_DATE(YYYYMMDD)) TABLESPACE rptfact

　　(

　　SUBPARTITION PSUB VALUES (ACTIVE) TABLESPACE rptfact

　　SUBPARTITION PSUB VALUES (INACTIVE) TABLESPACE rptfact

　　)

　　)

　　五復合范圍散列分區

　　這種分區是基於范圍分區和散列分區表首先按某列進行范圍分區然後再按某列進行散列分區

　　create table dinya_test

　　(

　　transaction_id number primary key

　　item_id number() not null

　　item_description varchar()

　　transaction_date date

　　)

　　partition by range(transaction_date)subpartition by hash(transaction_id)  subpartitions store in (dinya_spacedinya_spacedinya_space)

　　(

　　partition part_ values less than(to_date(yyyymmdd))

　　partition part_ values less than(to_date(yyyymmdd))

　　partition part_ values less than(maxvalue)

　　);

　　()有關表分區的一些維護性操作

　　一添加分區

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

　　ALTER TABLE SALES ADD PARTITION P VALUES LESS THAN(TO_DATE(YYYYMMDD));

　　注意以上添加的分區界限應該高於最後一個分區界限

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

　　ALTER TABLE SALES MODIFY PARTITION P ADD SUBPARTITION PSUB VALUES(COMPLETE);

　　二刪除分區

　　以下代碼刪除了P表分區

　　ALTER TABLE SALES DROP PARTITION P;

　　在以下代碼刪除了PSUB子分區

　　ALTER TABLE SALES DROP SUBPARTITION PSUB;

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

　　三截斷分區

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

　　ALTER TABLE SALES TRUNCATE PARTITION P;

　　通過以下代碼截斷子分區

　　ALTER TABLE SALES TRUNCATE SUBPARTITION PSUB;

　　四合並分區

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

　　ALTER TABLE SALES MERGE PARTITIONS PP INTO PARTITION P;

　　五拆分分區

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

　　ALTER TABLE SALES SBLIT PARTITION P AT(TO_DATE(YYYYMMDD)) INTO (PARTITION PPARTITION P);

　　六接合分區(coalesca)

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

　　ALTER TABLE SALES COALESCA PARTITION;

　　七重命名表分區

　　以下代碼將P更改為P

　　ALTER TABLE SALES RENAME PARTITION P TO P;

　　八相關查詢

　　跨分區查詢

　　select sum( *) from

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

　　union all

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

　　);

　　查詢表上有多少分區

　　SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME=tableName

　　查詢索引信息

　　select object_nameobject_typetablespace_namesum(value)

　　from v$segment_statistics

　　where statistic_name IN (physical readsphysical writelogical reads)and object_type=INDEX

　　group by object_nameobject_typetablespace_name

　　order by 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 apartitioned=YES

　　刪除一個表的數據是

　　truncate table table_name;

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

　　alter table table_name truncate partition p;

From:http://tw.wingwit.com/Article/program/Oracle/201311/18208.html