文章詳情頁

Oracle表分區(qū)詳解

瀏覽：230日期：2023-03-12 15:25:03

一、表空間及分區(qū)表的概念

表空間：

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

分區(qū)表：

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

二、表分區(qū)的具體作用

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

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

什么時候使用分區(qū)表：

1、表的大小超過2GB。

2、表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加都新的分區(qū)中。

三、表分區(qū)的優(yōu)缺點

優(yōu)點：

改善查詢性能：對分區(qū)對象的查詢可以僅搜索自己關心的分區(qū)，提高檢索速度。
增強可用性：如果表的某個分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用；
維護方便：如果表的某個分區(qū)出現(xiàn)故障，需要修復數(shù)據(jù)，只修復該分區(qū)即可；
均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整個系統(tǒng)性能。

缺點：

分區(qū)表相關：已經(jīng)存在的表沒有方法可以直接轉化為分區(qū)表。不過 Oracle 提供了在線重定義表的功能。

四、表分區(qū)的幾種類型及操作方法

1、范圍分區(qū)：

范圍分區(qū)將數(shù)據(jù)基于范圍映射到每一個分區(qū)，這個范圍是你在創(chuàng)建分區(qū)時指定的分區(qū)鍵決定的。這種分區(qū)方式是最為常用的，并且分區(qū)鍵經(jīng)常采用日期。舉個例子：你可能會將銷售數(shù)據(jù)按照月份進行分區(qū)。

當使用范圍分區(qū)時，請考慮以下幾個規(guī)則：

每一個分區(qū)都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區(qū)中的上限值。分區(qū)鍵的任何值等于或者大于這個上限值的記錄都會被加入到下一個高一些的分區(qū)中。
所有分區(qū)，除了第一個，都會有一個隱式的下限值，這個值就是此分區(qū)的前一個分區(qū)的上限值。
在最高的分區(qū)中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高于其它分區(qū)中的任何分區(qū)鍵的值，也可以理解為高于任何分區(qū)中指定的VALUE LESS THEN的值，同時包括空值。

例一：

假設有一個CUSTOMER表，表中有數(shù)據(jù)200000行，我們將此表通過CUSTOMER_ID進行分區(qū)，每個分區(qū)存儲100000行，我們將每個分區(qū)保存到單獨的表空間中，這樣數(shù)據(jù)文件就可以跨越多個物理磁盤。下面是創(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, 
    PHONEVARCHAR2(15) NOT NULL, 
    EMAILVARCHAR2(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、列表分區(qū)：

該分區(qū)的特點是某列的值只有幾個，基于這樣的特點我們可以采用列表分區(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 
);

3、散列分區(qū)：

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

散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型，因為通過在I/O設備上進行散列分區(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 
)

簡寫：

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ù)hash算法來計算具體某條紀錄應該插入到哪個分區(qū)中,hash算法中最重要的是hash函數(shù)，Oracle中如果你要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方，這樣可以使得各個分區(qū)間數(shù)據(jù)分布更加均勻。

4、復合（范圍列表）分區(qū)

這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行列表分區(qū)，分區(qū)之中的分區(qū)被稱為子分區(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 
  ) 
)

5、復合（范圍散列）分區(qū)：

這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行散列分區(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) 
 );

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

1、添加分區(qū)

以下代碼給SALES表添加了一個P3分區(qū)

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

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

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

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

2、刪除分區(qū)

以下代碼刪除了P3表分區(qū)：

ALTER TABLE SALES DROP PARTITION P3;

在以下代碼刪除了P4SUB1子分區(qū)：

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

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

3、截斷分區(qū)

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

ALTER TABLE SALES TRUNCATE PARTITION P2;

通過以下代碼截斷子分區(qū)：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4、合并分區(qū)

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

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

5、拆分分區(qū)

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

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

6、接合分區(qū)(coalesca)

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

ALTER TABLE SALES COALESCA PARTITION;

7、重命名表分區(qū)

以下代碼將P21更改為P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8、相關查詢

跨分區(qū)查詢

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)
);

查詢表上有多少分區(qū)

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

其他查詢

--顯示數(shù)據(jù)庫所有分區(qū)表的信息： 

select * from DBA_PART_TABLES 

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

select * from ALL_PART_TABLES 

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

select * from USER_PART_TABLES 

--顯示表分區(qū)信息 顯示數(shù)據(jù)庫所有分區(qū)表的詳細分區(qū)信息： 

select * from DBA_TAB_PARTITIONS 

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

select * from ALL_TAB_PARTITIONS 

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

select * from USER_TAB_PARTITIONS 

--顯示子分區(qū)信息 顯示數(shù)據(jù)庫所有組合分區(qū)表的子分區(qū)信息： 

select * from DBA_TAB_SUBPARTITIONS 

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

select * from ALL_TAB_SUBPARTITIONS 

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

select * from USER_TAB_SUBPARTITIONS 

--顯示分區(qū)列 顯示數(shù)據(jù)庫所有分區(qū)表的分區(qū)列信息： 

select * from DBA_PART_KEY_COLUMNS 

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

select * from ALL_PART_KEY_COLUMNS 

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

select * from USER_PART_KEY_COLUMNS 

--顯示子分區(qū)列 顯示數(shù)據(jù)庫所有分區(qū)表的子分區(qū)列信息： 

select * from DBA_SUBPART_KEY_COLUMNS 

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

select * from ALL_SUBPART_KEY_COLUMNS 

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

select * from USER_SUBPART_KEY_COLUMNS 

--怎樣查詢出oracle數(shù)據(jù)庫中所有的的分區(qū)表 

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

--刪除一個表的數(shù)據(jù)是 

truncate table table_name; 

--刪除分區(qū)表一個分區(qū)的數(shù)據(jù)是 

alter table table_name truncate partition p5;

到此這篇關于Oracle表分區(qū)的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。

Oracle

上一條：Oracle中的序列SEQUENCE詳解下一條：Oracle中鎖（lock）的用法

相關文章：

1. 通過Backup Exec實施Oracle來災難恢復2. Oracle 10G First Release的新特性簡介(上)3. Oracle10g R2 EMCA常用命令及實例步驟4. oracle常用經(jīng)典SQL查詢5. 使用Oracle進行數(shù)據(jù)庫備份與還原6. Oracle常見分析函數(shù)實例詳解7. 系統(tǒng)從oracle版本轉化為sqlserver版本8. 有時Oracle不用索引來查找數(shù)據(jù)的原因9. Oracle數(shù)據(jù)庫中臨時表的進一步深入研究10. 突破oracle for win2K的2G內存限制

排行榜

					
					解決db2 codepage導致連接不上數(shù)據(jù)庫問題
使用DB2look重新創(chuàng)建優(yōu)化器訪問計劃(6)
Microsoft Office Access創(chuàng)建表格的方法
DB2 Version 9.5 pureXML的增強和新特性
DB2中出現(xiàn)SQL1032N錯誤現(xiàn)象時的解決辦法
mysql啟動時報錯 ERROR! Manager of pid-file quit without
MySQL的join buffer原理
傳甲骨文將增加對MySQL投資與微軟競爭
聊聊MySQL中的存儲引擎
用三個方法設置Oracle數(shù)據(jù)庫穿越防火墻
mysql的like模式
				