oracle partition的方法

本文章的目的

     瞭解Oracle Partition的使用和技巧

本文物件

     具有Oracle的基礎知識，具有一定的Oracle管理知識的DBA管理人員

系統環境說明

作業系統 , Win2000

Oracle 版本: 8.1.7

分割槽的使用

為什麼要使用分割槽特性.

使用分割槽的主要三個原因﹕

。提高系統的可用性.

。減輕管理負擔.

。提高DML和查詢操作. 

分割槽的模式

在Oracle8.1.7版本下存在以下三種分割槽的方法:

﹑範圍分割槽

    partition by range (column_name)

﹑雜湊分割槽

    partition by hash (column_name)

﹑混合分割槽

    partition by range (column_name)

    subpartition  by hash(hash_key_column)

在9i後續的版本中新增一種列表分割槽. 以關鍵字List標示.

分割槽的索引

像表一樣﹐索引也可以進行分割槽﹐但只有兩種可能的索引分割槽方法。可以﹕

﹑對已分割槽的表中的索引進行均勻分割槽﹐亦稱區域性索引。

    [即索引的分割槽數目與表分割槽的數目是等同的﹐建立這 

     類索引以關索字 Local 來標示]

﹑按範圍對索引進行分割槽﹐亦稱全域性索引。

    [即自已重新進行佈局範圍的大小﹐注意這裡僅能利用

     range型﹐不能用 hash 或 list 型別的方法]

分割槽的優勢之-提高可用性

可用性來源於這樣一個事實﹕其表雖然在邏輯上是統一的﹐但各分割槽在物理上是獨立的實體﹐可分別存放在不同的磁碟空間上。Oracle優化器知道實現分割槽的方案﹐並且將會相應地從查詢方案中剔除不可用的分割槽。例証﹕

Create table emp2(id ,ename) partition by hash(id) (partition p_1 tablespace indx,partition p_2 tablespace indx1) as select empno,ename from emp;

SQL> select *  from emp2 partition (p_1);

   ID ENAME

----- ----------

 7789 SCOTT

 7839 KING

 7876 ADAMS

 7934 MILLER

select *  from emp2 partition (p_2);

ID ENAME

----- ----------

 7788 SCOTT

 7844 TURNER

 7900 JAMES

 7902 FORD

Alter tablespace indx offline﹔我們來使表空間 Indx離線.

SQL> select *  from emp2;  這樣查詢會出錯。

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: ‘/orabase/oradata/app/indx01.dbf‘  

SQL> select *  from emp2 where id =7902; 查沒離線的可以

 ID ENAME

----- ----------

 7902 FORD

總結﹕將表分割槽後放在不同的所屬表空間是很有必要的﹐當磁碟發生物理損壞時能將故障時間降到最低。例如﹐有一個100G的大表﹐被分割成 50個分割槽﹐當其中一個 分割槽受損﹐那麼現在的恢復時間僅只是恢復這個2G分割槽的所花費的時間﹐也降低了恢復的難度﹐而其它的分割槽的資料依然可能使用當然這要使用有條件的分割槽排除才行。

分割槽的優勢之-減輕管理負擔

管理負擔的減輕來源於這樣的事實﹕小表的管理始終優於對大型表的管理。相對於大型物件而言﹐對較小的物件執行操作自然更容易﹑更快且需要的資料會更少。

所謂有管理如我們日常所做的﹕

alter table move…..

Index rebuild ….

表中舊資料的歸檔….

利用分割槽的 split 屬性與單獨表中的資料交換….

分割槽的優勢之-DML與查詢增強

增強的DML效能指的是執行並行DML(PDML)的可能。在PDML中﹐Oracle使用非單個連續程式而是多個程式進行處理INSERT﹑UPDATE﹑DELETE。在一個有足夠I/O頻寬的多CPU機器中﹐對於大量的DML操作﹐其潛在的加速效能是巨大的。

PDML雖然有其巨大的效能特別是在 DDS環境下的系統﹐但是我們不能將PDML看作是加速OLTP應用程式的一個特性。因為某些特定的操作須在其特定的環境下才可以的﹐對於某些操作而言並行操作比序列執行可能會慢許多倍。我們知道在並行時會有許多開銷﹐系統要作更多的協調工作。理解OLTP系統﹐其目的是最大程度上地使用機器。

它們用於使單個使用者能完全使用機器上所有的磁碟﹑CPU和記憶體。使用者只是在做短時的﹑快速的事務處理。這樣利用並行來增大伸縮性在這兒就完全沒有用途了。因此將其這個特性利用到DDS將會事半功倍。

在查詢效能方面﹐分割槽與兩種型別的特殊操作一起發揮作用﹕

﹑分割槽排除﹕在處理查詢任務時﹐利用謂詞來過濾一些不考慮的分割槽﹐如離線的表空間存在仍能查詢就是利用這

    特性。

﹑並行操作﹕並行索引掃描﹐由此索引分割槽能被並行掃描

分割槽的優勢之總結

如果要以某種順序來敘述分割槽的好處﹐將會是﹕

1]提高資料的可用性。這對所有的系統型別都有利.

2]通過從資料庫中去除大型物件來減輕對大物件的管理—對所有的系統型別都有得.

3]提高某些DML語句和查詢任務的效能—主要對大型資料倉儲環境有利.

4]通過將插入操作擴充套件到放多獨立的分割槽[熱點散開]﹐減少高插入所帶到的系統資料爭用現象。

因此﹐通過上述應可以在何種情況下利用分割槽將帶來好的系統效能作出很好的判斷。

分割槽的模式理解

範圍分割槽[Range]﹕以這種模式進行分割槽是一種可預見性的對資料範圍的分佈。在後續的管理中簡單方便。特別是在對於歷史資料的處理上很有優勢。但在儲存資料方面不能均勻的分佈。

雜湊分割槽[Hash]﹕這種模式適用於沒有可用於分割槽的自然範圍的資料﹐用於對範圍沒有意義的屬性。但這種分割槽能很好的處理I/o問題﹐資料均勻分佈在儲存空間上。

濁合分割槽{Range/Hash}﹕這種模式用於當資料合理﹐能以範圍進行區分﹐但分割槽後資料仍然太大難以進行有效的管理這樣用雜湊函式進一步劃分每一個分割槽這將允許在任意個大分割槽中I/O請求擴充套件到多個磁碟。

Oracle9i的list分割槽暫不考慮。

分割槽的索引

前面有講﹐像表一樣﹐索引也可以進行分割槽﹐有兩種可能的索引分割槽。

﹑Local Index ﹕這種索引只徵對已經分割槽的表來講的﹐即在已有分割槽的表中的各個小區對應建立索引分割槽。

    create index idx_ptt on p_tt(nm) local﹔

 SQL> select Table_name,PARTITION_NAME ,HIGH_VALUE

     From user_tab_partitions where table_name ='P_TT';

TABLE_NAME     PARTITION_NAME       HIGH_VALUE

--------------------------------------------

P_TT          P_1                   10

P_TT          P_2                   20

以上顯示建有一個含兩個分割槽的表.

SQL> select INDEX_NAME,PARTITION_NAME ,HIGH_VALUE

from user_ind_partitions a where a.index_name='IDX_PTT';

INDEX_NAME        PARTITION_NAME           HIGH_VALUE

---------------- ------------------- ----------------

IDX_PTT           P_1                    10

IDX_PTT           P_2                    20

上述顯示同樣的這個索引也是含有兩個分割槽的分割槽索引。

然而就分割槽索引建立時是否含有分割槽鍵又將索引分為﹕

區域性字首索引與區域性非字首索引。

Create index idx_pre on p_tt(id,nm) local;

Create index idx_nopre on p_tt(nm) local;

如果Table p_tt 以id 來為分割槽Key ﹐那麼第一個索引將稱之為含字首索引﹐第二個為非字首索引。下面來講它們的區別﹕

分割槽的索引

這兩種型別的索引都可利用分割槽排除的方法﹐兩者都支援惟一性(只要非字首引包括分割槽碼雖然不是放在前導位置)事實上使用區域性字首索引的查詢任務將總是允許索引分割槽的使用﹐而使用區域性非字首的查詢可能不允許除非在謂詞中有將所有的分割槽鍵含括在其中﹐這就是為什麼區域性非字首索引據說“較慢”的原因﹕它們不強調分割槽排除﹐雖然支援。所以 Oracle文件強調這個﹕“區域性字首索引比區域性非字首引具有更好的效能﹐因它們將檢查的索引數目最小化”

下面來例証一下﹕…..

分割槽的索引之區域性索引例証…

CREATE TABLE partitioned_table

( a int,  b int)  PARTITION BY RANGE (a)

(PARTITION part_1 VALUES LESS THAN(2) ,

PARTITION part_2 VALUES LESS THAN(3))

insert into partitioned_table values ( 1, 1 );

create index local_prefixed on partitioned_table (a,b) local;

alter index local_prefixed modify partition part_2 unusable;

SQL> insert into partitioned_table values ( 2, 1 );

ORA-01502: index 'SCOTT.LOCAL_NONPREFIXED' or partition of such index is in unusable state

上面錯誤可以看出由於分割槽2的索引不可用﹐所以不能新增資料到裡面﹐看sql計劃:

SQL> select * from partitioned_table where a = 1 and b = 1;

執行計畫

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=26)

   1    0   INDEX (RANGE SCAN) OF ‘LOCAL_PREFIXED’ (NON-UNIQUE) (Cost=1 Card=1 Bytes=26)  使用字首索引查詢成功﹐能夠將par_2 從中排除﹐因為指定了 a=1。

我們再看第二個例子….. 建立非字首索引.

區域性索引例証…

create index local_nonprefixed on partitioned_table (b) local;

alter index local_nonprefixed modify partition part_2 unusable;

SQL> select * from partitioned_table where b = 1;

ERROR:ORA-01502: index 'SCOTT.LOCAL_NONPREFIXED' or partition of such

index is in unusable state

這裡看到因為 第二index 是非字首索引﹐它不能將分割槽2從查詢中排除﹐所以Error。

再來看看﹐當我們將第一個索引-字首索引 drop 後再來查詢會有什麼結果.

drop index local_prefixed;

SQL> select * from partitioned_table where a = 1 and b = 1;

執行計畫

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=26)

   1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PARTITIONED_TABLE'

           (Cost=1 Card=1 Bytes=26)

   2    1     INDEX (RANGE SCAN) OF 'LOCAL_NONPREFIXED' (NON-UNIQUE) (

          Cost=1 Card=1)

因為這對於前面那個Error的查詢而言﹐這幾乎是一個相同的查詢方案﹐但這次成功了。那是因為即使沒有字首的索引﹐但因為謂詞中有包含分割槽鍵﹐它能夠將那些用不到的分割槽排除之外。

從上面的例子中可能看出﹐區域性的字首索引與非字首索引在可能情況下沒有什麼很大的區別。 但是在有進行表連線的查詢則結果就可能不同。例﹑﹑﹑

CREATE TABLE range_example

( range_key_column date,  x     int,data      varchar2(20))

PARTITION BY RANGE (range_key_column)

( PARTITION part_1 VALUES LESS THAN (to_date('01-01-1995','dd-mm-yyyy')),

  PARTITION part_2 VALUES LESS THAN (to_date('01-01-1996','dd-mm-yyyy')))

alter table range_example add constraint range_example_pk primary key (range_key_column,x) using index local

alter table range_example

add constraint range_example_pk

primary key (range_key_column,x) using index local

insert into range_example values ( to_date('01-01-1994','dd-mm-yyyy'), 1, 'xxx' );

insert into range_example values ( to_date('01-01-1995','dd-mm-yyyy'), 2, 'xxx' );

建立一個區域性字首索引﹐再建立另一個測試所用的表:

create table test ( pk , range_key_column , x,   constraint test_pk primary key(pk) )

as select rownum, range_key_column, x from range_example

SQL> select * from test, range_example  where test.pk = 1

  3    and test.range_key_column = range_example.range_key_column

  4    and test.x = range_example.x;

執行計畫

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=47)

   1    0   NESTED LOOPS (Cost=2 Card=1 Bytes=47)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=1 Bytes=35)

   3    2       INDEX (RANGE SCAN) OF 'TEST_PK' (UNIQUE) (Cost=1 Card=1)

   4    1     PARTITION RANGE (ITERATOR)

   5    4       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'RANGE_EXAMPLE'(Cost=1 Card=2 Bytes=24)

   6    5         INDEX (UNIQUE SCAN) OF 'RANGE_EXAMPLE_PK' (UNIQUE)

上面查詢方案很清楚的表述了這條sql 所走的執行路徑﹐但當我將前面分割槽表的那個字首索引換成一個非字首的看看有什麼變化。

SQL> alter table range_example

  2  drop constraint range_example_pk

  3  /

已更改表格.

alter table range_example add constraint range_example_pk

primary key (x,range_key_column)  using index local;

已更改表格.

SQL> select * from test, range_example   where test.pk = 1

 2    and test.range_key_column = range_example.range_key_column

 3    and test.x = range_example.x;

執行計畫

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=23)

   1    0   NESTED LOOPS (Cost=2 Card=1 Bytes=23)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=1 Bytes=11)

3    2       INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)

   4    1     PARTITION RANGE (ITERATOR)

   5    4       TABLE ACCESS (FULL) OF 'RANGE_EXAMPLE' (Cost=1 Card=46 bytes=552)

從這裡可以看出因為非字首索引在這裡它會認為代價太高﹐所以舍棄用它而用全表掃描來替代。

分割槽的索引之全域性索引

全域性索引是使用與它們所在的表不同的模式來進行分割槽的的﹐例這個表可能按timestamp 來分成 10個分割槽﹐而其表中的全域性索引則可以按 region來分成5個分割槽。與區域性索引不同﹐在oracle 中只有一類全域性索引﹐即字首全域性索引。Oracle不支援索引碼不以分割槽碼開頭的全域性索引。例﹕

CREATE TABLE partitioned

( timestamp date,  id        int,  nn        int)

PARTITION BY RANGE (timestamp)

(PARTITION part_1 VALUES LESS THAN

( to_date('01-01-2000','dd-mm-yyyy') ) ,

PARTITION part_2 VALUES LESS THAN

( to_date('01-01-2001','dd-mm-yyyy') ))

上面的表中建立了以 timestamp列來為分割槽鍵的分割槽表﹐有分割槽兩個。接下來我們來建立全域性索引。

create index partitioned_index

on partitioned(id)

GLOBAL

partition  by range(id)

(partition part_1 values less than(1000),

partition part_2 values less than (MAXVALUE))

這裡我們建立了在id列上面的全域性索引。注意這個索引中的 maxvalue關鍵字的使用﹐這是因為在全域性索引中必須用於其中﹐它代表的範圍是“無限上界”﹐確保所有的行都能放至於這個索引之中。

前面有說過在非分割槽的表中也可以存在分割槽索引﹐條件是僅能存在全域性分割槽索引。當然全域性索引的限制[字首方式存在﹑只以基於 range 分割槽型別]也同樣受用。例﹕

SQL> create table test (id number,nm  char);

SQL> create index idx_test on test(id)

global partition by range (id)

(partition p1 values less than (10),

 partition p2 values less than (maxvalue))

全域性索引有一個特點﹐當操作其中任一個表分割槽都會造成全域性索引無效則須要重建。

SQL> alter table empp drop partition p4 ;

Table altered

SQL> select *  from empp where job ='ANALYST';

select *  from empp where job ='ANALYST'

ORA-01502: index 'SCOTT.EMP_JOB_IDX' or partition of such index is in unusable state

SQL> alter session set skip_unusable_indexes =true;

Session altered

SQL> select *  from empp where job ='ANALYST';

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO LOC

----- ---------- --------- ----- ----------- --------- --------- ------ -------------

可以利用8.1.5後新加入的引數skip_unusable_indexes 躍過不可用的索引﹐但是這樣就失去了索引所帶來的效能提高。因此最終的方法還是重建索引。

SQL> alter index EMP_JOB_IDX rebuild;

SQL> select *  from empp where job ='ANALYST';

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO LOC

分割槽索引之 總結

已經列舉了分割槽索引的兩種型別﹐local/global

根據自己所用的系統來決定那種分割槽索引較適合使用。專家建議﹕資料倉儲應是與區域性索引相匹配。OLTP系統應是與全域性索引相區配。

因為在oltp系統中﹐我們要達到的目的是﹕快速訪問﹑資料完整性﹑可用性。全域性索引可以做這件事。Oltp系統的特點與資料倉儲非常不同。我們或許不會做滑動視窗﹐不會分割分割槽﹐不會移動資料等 等。我們在資料倉儲中執行的操作在整體上不會在有效的oltp系統中完成。

如果我們應用的是全域性索引那麼對分割槽任何操作都將引起全域性索引失效﹐那麼全域性索引必須重構{當然在區域性索引中索引也要重構只是僅僅對當事的那個分割槽}等等。

例証系統中分割槽的使用

現行的一些系統中就有可能對分割槽的運用﹐如應付憑單檔﹑帳單檔﹑傳票檔等等﹑﹑﹑

所採用的分割槽是以 range 的型別﹐按年月來分﹐每一個季度來切割一個分割槽﹐歷史資料無限期保留沒有進行歸檔處理。對表中建立的索引是沒有進行分割槽處理即是全域性性的索引。

系統中分割槽運用分析﹕

表中資料較大﹐但沒有對歷史資料進行有效的歸檔處理﹐如往年的傳票﹑憑單資料對現階段的操作應沒有很大的關聯﹐所以應單獨開立一個新表進行轉存。這樣能將這些大表始終控制在一個範圍範圍大小之中﹐方便日常管理操作

此外﹐這些表中的索引建立的全都是普通型的全域性索引﹐對 oltp 來講這是好的﹐但卻沒有高效利用分割槽鍵來作為索引的一部份且從未在謂詞中將分割槽鍵作為條件。所以所有的查詢與 dml操作都是在徵對全分割槽進行掃描而沒有利用分割槽剔除的特性﹐這樣給系統帶來很多不必要的I/O操作。

所以系統現在所建立的分割槽表僅只用到了其中的特性之一﹕高可用性。當發生物理故障時可以離線某一表空間有限制的對其它資料時行操作僅此而已。 

分割槽管理示例

以下來示例幾個常用的分割槽管理操作指令﹕

1﹑增加分割槽﹕

SQL> create table pp (id  int,nm  char)

  2  partition by range (id)

  3  (partition p1 values less than (10),

  4   partition p11 values less than (20));

 Table created

SQL> alter table pp add partition p111 values less than (30);

Table altered

以上是基於 range 方式分割槽的增加

需注意的是分割槽的增加只能在最後一個分割槽之後﹐即所界的值必須大於最後一個分割槽的的臨界值﹐這就 add 指令的限定。

SQL> alter table pp2 add partition p111 ;

Table altered

以上是基於 hash 方式分割槽的增加﹐只需指定分割槽的名稱就可以了。

2﹑分割槽與表交換。

alter table partitioned

exchange partition fy_1999

with table fy_1999

including indexes

without validation

表示將分割槽 fy_1999與單獨表 fy_1999 間的資料時行至換並且包括索引在內。

3﹑分割槽的滑離

SQL> alter table pp split partition p11 at (15) into (partition sp1,partition sp2);

以上操作僅只能對 range 型態的分割槽表才有用

我們一般會在建立的分割槽表中取最後一個分割槽的臨界值為maxvalue﹐這樣在後續在操作中如果資料範圍變化大的話可以從中滑離出另外的分割槽。

所以這樣看來的話 add 與 split 似乎功能差不多﹐差異在於 split 可以分離另任一個分割槽﹐而 add 僅只能在最後一個分割槽後再新增一個分割槽而已.

4﹑drop ﹑truncate

這幾類操作同表的操作意思一樣﹑分別表徵刪除其不必要的分割槽或截斷某一分割槽中的資料。

alter table pp drop partitoin sp1

alter table pp truncate  partitoin sp1;

5﹑index 的管理 rebuild﹑unusable

Rebuild 表示當對分割槽進行某些操作後造成 index 無效﹐利用 rebuild來重建這個分割槽索引。Unusable 表示將這個分割槽索引置為無效。

Alter index idx_par1 rebuild partition p_1;

Alter index idx_part1 unusable partiton p_1;

……….

總結﹕上述就是分割槽的大體內容﹐還有很多功能沒有一一列舉﹐可能通來如google,baidu等網站來查詢相關方面的內容介紹及網友提供的各種技巧﹐在管理和實踐中提高水平。

後記

   文件日期:2006年11月01日