文章詳情頁

Oracle表分區(qū)詳解

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

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

表空間：

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

分區(qū)表：

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

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

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

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

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

1、表的大小超過2GB。

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

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

優(yōu)點(diǎn)：

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

缺點(diǎn)：

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

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

1、范圍分區(qū)：

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

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

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

例一：

假設(shè)有一個CUSTOMER表，表中有數(shù)據(jù)200000行，我們將此表通過CUSTOMER_ID進(jìn)行分區(qū)，每個分區(qū)存儲100000行，我們將每個分區(qū)保存到單獨(dú)的表空間中，這樣數(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ū)的特點(diǎn)是某列的值只有幾個，基于這樣的特點(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 
);

3、散列分區(qū)：

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

散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型，因?yàn)橥ㄟ^在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 
)

簡寫：

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

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

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

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

1、添加分區(qū)

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

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

注意：以上添加的分區(qū)界限應(yīng)該高于最后一個分區(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ù)。當(dāng)表中即使只有一個分區(qū)時，也可以截斷該分區(qū)。通過以下代碼截斷分區(qū)：

ALTER TABLE SALES TRUNCATE PARTITION P2;

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

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4、合并分區(qū)

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

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

5、拆分分區(qū)

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

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

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

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

ALTER TABLE SALES COALESCA PARTITION;

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

以下代碼將P21更改為P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8、相關(guān)查詢

跨分區(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 

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

select * from ALL_PART_TABLES 

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

select * from USER_PART_TABLES 

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

select * from DBA_TAB_PARTITIONS 

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

select * from ALL_TAB_PARTITIONS 

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

select * from USER_TAB_PARTITIONS 

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

select * from DBA_TAB_SUBPARTITIONS 

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

select * from ALL_TAB_SUBPARTITIONS 

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

select * from USER_TAB_SUBPARTITIONS 

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

select * from DBA_PART_KEY_COLUMNS 

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

select * from ALL_PART_KEY_COLUMNS 

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

select * from USER_PART_KEY_COLUMNS 

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

select * from DBA_SUBPART_KEY_COLUMNS 

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

select * from ALL_SUBPART_KEY_COLUMNS 

--顯示當(dāng)前用戶所有分區(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;

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

Oracle

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

相關(guān)文章：

1. 我的oracle筆記六（oracle優(yōu)化方面）2. Oracle 認(rèn)證學(xué)習(xí)筆記3. 相同軟件環(huán)境下Oracle9i數(shù)據(jù)庫遷移方法4. Oracle數(shù)據(jù)庫中分區(qū)表的操作方法詳解5. Oracle存儲過程的幾種調(diào)用方式圖文詳解6. Oracle rac環(huán)境的數(shù)據(jù)庫導(dǎo)入操作步驟7. Oracle XQuery查詢、構(gòu)建和轉(zhuǎn)換XML8. ORACLE常用傻瓜問題1000問(之十二)9. Oracle建表與創(chuàng)建序列詳細(xì)實(shí)例10. 上海Oracle OpenWorld大會紀(jì)事-07.19

排行榜

					
					DB2數(shù)據(jù)庫更新執(zhí)行計劃的幾個常見的方法
MySQL兩千萬數(shù)據(jù)優(yōu)化&遷移
MySQL5.7 mysqldump備份與恢復(fù)的實(shí)現(xiàn)
MySQL Community Server 5.1.49
MySQL分區(qū)的優(yōu)點(diǎn)
MySQL 性能優(yōu)化，讓數(shù)據(jù)庫跑的更快
啟動MYSQL出錯 Manager of pid-file quit without updating file.
使用MySqldump命令導(dǎo)出數(shù)據(jù)時的注意
mysql數(shù)據(jù)庫中最常用的時間轉(zhuǎn)換函數(shù)的用法
傳甲骨文將增加對MySQL投資與微軟競爭
Delphi中的Access技巧集
				