ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle partition的方法

oracle partition的方法

原创 Linux操作系统 作者:zhengbao_jun 时间:2009-03-04 11:03:20 0 删除 编辑

本文章的目的

     了解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使用非單個連續進程而是多個進程進行處理INSERTUPDATEDELETE。在一個有足夠I/O帶寬的多CPU機器中﹐對于大量的DML操作﹐其潛在的加速性能是巨大的。

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

 

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

在查詢性能方面﹐分區與兩種類型的特殊操作一起發揮作用﹕

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

    特性。

﹑并行操作﹕并行索引掃描﹐由此索引分區能被并行掃描

 

 

分區的優勢之總結

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

1]提高數據的可用性。這對所有的系統類型都有利.

2]通過從數據庫中去除大型對象來減輕對大對象的管理對所有的系統類型都有得.

3]提高某些DML語句和查詢任務的性能主要對大型數據倉庫環境有利.

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

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

 

分區的模式理解

范圍分區[Range]﹕以這種模式進行分區是一種可預見性的對數據范圍的分布。在后續的管理中簡單方便。特別是在對于歷史數據的處理上很有優勢。但在存儲數據方面不能均勻的分布。

 

散列分區[Hash]﹕這種模式適用于沒有可用于分區的自然范圍的數據﹐用于對范圍沒有意義的屬性。但這種分區能很好的處理I/o問題﹐數據均勻分布在存儲空間上。

 

濁合分區{Range/Hash}﹕這種模式用于當數據合理﹐能以范圍進行區分﹐但分區后數據仍然太大難以進行有效的管理這樣用散列函數進一步划分每一個分區這將允許在任意個大分區中I/O請求擴展到多個磁盤。

Oracle9ilist分區暫不考慮。

 

分區的索引

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

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 僅只能在最后一個分區后再新增一個分區而已.

 

4drop truncate

這几類操作同表的操作意思一樣﹑分別表征刪除其不必要的分區或截斷某一分區中的數據。

alter table pp drop partitoin sp1

alter table pp truncate  partitoin sp1;

5index 的管理 rebuildunusable

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

Alter index idx_par1 rebuild partition p_1;

Alter index idx_part1 unusable partiton p_1;

……….

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

後記

   文檔日期:20061101 

---------------------------------------------------------------------------
轉載于網上: http://bbs.stcore.com/thread-1764-1-2.html

关于exchange partition


   exchange partition提供了快速转换普通成分区表的方法,它通过更新数据字典来实现分区与普通表的置换,所以速度相当快。
  
  create table t1 as select sysdate dt, all_objects.* from all_objects;
  
  create table t2 as select sysdate dt, all_objects.* from all_objects;
  
  create table t3 as select add_months(sysdate,-24) dt, all_objects.* from all_objects;
  
  create table t( dt, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
  LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
  GENERATED, SECONDARY )
  partition by range(dt) (
  partition part2003 values less than ( to_date( '01-jan-2004',
  'dd-mon-yyyy') ),
  partition part2004 values less than ( to_date( '01-jan-2005',
  'dd-mon-yyyy') ),
  partition part2005 values less than ( to_date( '01-jan-2006',
  'dd-mon-yyyy') )
  )
  as
  select sysdate dt, all_objects.* from all_objects where 1=0;
  /
  
  SQL 10G>set timing on
  SQL 10G>alter table t
   2 exchange partition part2003
   3 with table t3
   4 /
  Table altered.
  Elapsed: 00:00:00.07
  
  SQL 10G>alter table t
   2 exchange partition part2005
   3 with table t1
   4 /
  Table altered.
  Elapsed: 00:00:00.07
  
  
  SQL 10G>alter table t
   2 exchange partition part2004
   3 with table t2
   4 /
  with table t2
        *
  ERROR at line 3:
  ORA-14099: all rows in table do not qualify for specified partition
  
  Elapsed: 00:00:00.00
  
  因为t2不符合分区规则,所以当进行exchange将会报错,我们可以指定without validation子句来强行禁止oracle检查合理性
  
  SQL 10G>alter table t
   2 exchange partition part2004
   3 with table t2
   4 without validation
   5 /
  Table altered.
  Elapsed: 00:00:00.03
  
  由于without validation不需要校验数据的正确性,所以不会对t2做全表扫描,因此exchange的时间将会缩短,without validation子句在进行大表的exchange时效率将会特别高,如果你能确定普通表数据的正确性,那么请大胆得使用without validation吧。
  
  我们也可以看一下当验证数据正确性的时候oracle内部是怎么做的
  
  通过10046 trace events,我们可以看到当进行exchange partition with validation时
  
  select 1 from "T1" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58626;
  
  select 1 from "T2" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58625;
  
  select 1 from "T3" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58624;
  
  oracle通过TBL$OR$IDX$PART$NUM函数来判断是否普通表满足分区置换的条件
  
  
  SQL 10G>select count(*) from "T1" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58626;
   COUNT(*)
  ----------
       0
  Elapsed: 00:00:00.00
  
  SQL 10G>select count(*) from "T2" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58625;
  
   COUNT(*)
  ----------
     49496
  Elapsed: 00:00:00.04
  
  SQL 10G>select count(*) from "T3" where TBL$OR$IDX$PART$NUM("T", 0, 3,1048576,"DT") != 58624;
  
   COUNT(*)
  ----------
       0
  
  t2是不符合分区条件的,所以exchange的时候会报
  ORA-14099: all rows in table do not qualify for specified partition

--------------------------------------------------------------------------
http://enjoytime.blog.hexun.com/2741515_d.html

分区表PARTITION table,以及把已存在数据的表转为分区表。 1.1 分区表PARTITION table 在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 1.1.1 分区表的建立:某公司的每年产生巨大的销售记录,DBA向公司建议每季度的数据放在一个分区内,以下示范的是该公司1999年的数据(假设每月产生30M的数据),操作如下: 范围分区表: CREATE TABLE sales (invoice_no NUMBER, ... sale_date DATE NOT NULL ) PARTITION BY RANGE (sale_date) (PARTITION sales1999_q1 VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’) TABLESPACE ts_sale1999q1, PARTITION sales1999_q2 VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’) TABLESPACE ts_sale1999q2, PARTITION sales1999_q3 VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’) TABLESPACE ts_sale1999q3, PARTITION sales1999_q4 VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’) TABLESPACE ts_sale1999q4 ); --values less than (maxvalue) 列表分区表: create table emp ( empno number(4), ename varchar2(30), location varchar2(30)) partition by list (location) (partition p1 values ('北京'), partition p2 values ('上海','天津','重庆'), partition p3 values ('广东','福建') partition p0 values (default) ); 哈希分区: 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); 组合分区:范围哈希组合分区: create table emp ( empno number(4), ename varchar2(30), hiredate date) partition by range (hiredate) subpartition by hash (empno) subpartitions 2 (partition e1 values less than (to_date('20020501','YYYYMMDD')), partition e2 values less than (to_date('20021001','YYYYMMDD')), partition e3 values less than (maxvalue)); 范围列表组合分区: CREATE TABLE customers_part ( customer_id NUMBER(6), cust_first_name VARCHAR2(20), cust_last_name VARCHAR2(20), nls_territory VARCHAR2(30), credit_limit NUMBER(9,2)) PARTITION BY RANGE (credit_limit) SUBPARTITION BY LIST (nls_territory) SUBPARTITION TEMPLATE (SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'), SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'), SUBPARTITION other VALUES (DEFAULT)) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2500), PARTITION p3 VALUES LESS THAN (MAXVALUE)); create table t1 (id1 number,id2 number) partition by range (id1) subpartition by list (id2) (partition p11 values less than (11) (subpartition subp1 values (1)) ); 1.1.2 分区表的维护:增加分区: ALTER TABLE sales ADD PARTITION sales2000_q1 VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’) TABLESPACE ts_sale2000q1; 如果已有maxvalue分区,不能增加分区,可以采取分裂分区的办法增加分区!删除分区: ALTER TABLE sales DROP PARTION sales1999_q1; 截短分区: alter table sales truncate partiton sales1999_q2; 合并分区: alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23; 分裂分区: ALTER TABLE sales SPLIT PARTITON sales1999_q4 AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’) INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) 交换分区: alter table x exchange partition p0 with table bsvcbusrundatald ; 访问指定分区: select * from sales partition(sales1999_q2) EXPORT指定分区: exp sales/sales_password tables=sales:sales1999_q1 file=sales1999_q1.dmp IMPORT指定分区: imp sales/sales_password FILE =sales1999_q1.dmp TABLES = (sales:sales1999_q1) IGNORE=y 查看分区信息: user_tab_partitions, user_segments 注:若分区表跨不同表空间,做导出、导入时目标数据库必须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间!这些表空间不一定是用户的默认表空间,只要存在即可。如果有一个不存在,就会报错!默认时,对分区表的许多表维护操作会使全局索引不可用,标记成UNUSABLE。那么就必须重建整个全局索引或其全部分区。如果已被分区,Oracle 允许在用于维护操作的ALTER TABLE 语句中指定UPDATE GLOBAL INDEXES 来重载这个默认特性,指定这个子句也就告诉Oracle 当它执行维护操作的DDL 语句时更新全局索引,这提供了如下好处: 1.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引; 2.因为没有被标记成UNUSABLE, 所以全局索引的可用性更高了,甚至正在执行分区的DDL 语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们;另外在指定UPDATE GLOBAL INDEXES 之前还要考虑如下性能因素: 1.因为要更新事先被标记成UNUSABLE 的索引,所以分区的DDL 语句要执行更长时间,当然这要与先不更新索引而执行DDL 然后再重建索引所花的时间做个比较,一个适用的规则是如果分区的大小小于表的大小的5% ,则更新索引更快一点; 2.DROP TRUNCATE 和EXCHANGE 操作也不那么快了,同样这必须与先执行DDL 然后再重建所有全局索引所花的时间做个比较; 3.要登记对索引的更新并产生重做记录和撤消记录,重建整个索引时可选择NOLOGGING; 4.重建整个索引产生一个更有效的索引,因为这更利于使用空间,再者重建索引时允许修改存储选项。注意分区索引结构表不支持UPDATE GLOBAL INDEXES 子句。 1.1.3 普通表变为分区表将已存在数据的普通表转变为分区表,没有办法通过修改属性的方式直接转化为分区表,必须通过重建的方式进行转变,一般可以有三种方法,视不同场景使用:用例:方法一:利用原表重建分区表。 CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); INSERT INTO T SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000; COMMIT; CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) (PARTITION P1 VALUES LESS THAN (TO_DATE('2000-1-1', 'YYYY-MM-DD')), PARTITION P2 VALUES LESS THAN (TO_DATE('2002-1-1', 'YYYY-MM-DD')), PARTITION P3 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), PARTITION P4 VALUES LESS THAN (MAXVALUE)) AS SELECT ID, TIME FROM T; RENAME T TO T_OLD; RENAME T_NEW TO T; SELECT COUNT(*) FROM T; COUNT(*) ---------- 5000 SELECT COUNT(*) FROM T PARTITION (P1); COUNT(*) ---------- 2946 SELECT COUNT(*) FROM T PARTITION (P2); COUNT(*) ---------- 731 SELECT COUNT(*) FROM T PARTITION (P3); COUNT(*) ---------- 1096 优点:方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了。不足:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对 T的访问会失败。 适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大。 方法二:使用交换分区的方法。 Drop table t; CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); INSERT INTO T SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000; COMMIT; CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-9-1', 'YYYY-MM-DD')), PARTITION P2 VALUES LESS THAN (MAXVALUE)); ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T; RENAME T TO T_OLD; RENAME T_NEW TO T; 优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。 适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。 方法三:Oracle9i以上版本,利用在线重定义功能 Drop table t; CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); INSERT INTO T SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000; COMMIT; EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T'); PL/SQL 过程已成功完成。 CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), PARTITION P4 VALUES LESS THAN (MAXVALUE)); 表已创建。 EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW'); PL/SQL 过程已成功完成。 EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_NEW'); PL/SQL 过程已成功完成。 SELECT COUNT(*) FROM T; COUNT(*) ---------- 5000 SELECT COUNT(*) FROM T PARTITION (P3); COUNT(*) ---------- 1096 优点:保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。不足:实现上比上面两种略显复杂。 适用于各种情况。 这里只给出了在线重定义表的一个最简单的例子,详细的描述和例子可以参考下面两篇文章。 Oracle的在线重定义表功能:http://blog.itpub.net/post/468/12855 Oracle的在线重定义表功能(二):http://blog.itpub.net/post/468/12962 XSB: 把一个已存在数据的大表改成分区表:第一种(表不是太大): 1.把原表改名: rename xsb1 to xsb2; 2.创建分区表: CREATE TABLE xsb1 PARTITION BY LIST (c_test) (PARTITION xsb1_p1 VALUES (1), PARTITION xsb1_p2 VALUES (2), PARTITION xsb1_p0 VALUES (default)) nologging AS SELECT * FROM xsb2; 3.将原表上的触发器、主键、索引等应用到分区表上; 4.删除原表: drop table xsb2; 第二种(表很大): 1. 创建分区表: CREATE TABLE x PARTITION BY LIST (c_test) [range ()] (PARTITION p0 VALUES [less than ](1) tablespace tbs1, PARTITION p2 VALUES (2) tablespace tbs1, PARTITION xsb1_p0 VALUES ([maxvalue]default)) AS SELECT * FROM xsb2 [where 1=2]; 2. 交换分区 alter table x exchange partition p0 with table bsvcbusrundatald ; 3. 原表改名alter table bsvcbusrundatald rename to x0; 4. 新表改名alter table x rename to bsvcbusrundatald ; 5. 删除原表drop table x0; 6. 创建新表触发器和索引create index ind_busrundata_lp on bsvcbusrundatald(。。。) local tablespace tbs_brd_ind ; 或者: 1. 规划原大表中数据分区的界限,原则上将原表中近期少量数据复制至另一表; 2. 暂停原大表中的相关触发器; 3. 删除原大表中近期数据; 4. 改名原大表名称; 5. 创建分区表; 6. 交换分区; 7. 重建相关索引及触发器(先删除之再重建). 参考脚本: select count(*) from t1 where recdate>sysdate-2 create table x2 nologging as select * from t1 where recdate>trunc(sysdate-2) alter triger trg_t1 disable delete t1 where recdate>sysdate-2 commit rename t1 to x1 create table t1 [nologging] partition by range(recdate) (partition pbefore values less than (trunc(sysdate-2)), partition pmax values less than (maxvalue)) as select * from x1 where 1=2 alter table t1 exchange partition pbefore with table x1 alter table t1 exchange partition pmax with table x2 drop table x2 [重建触发器] drop table x1 1.1.4 参考材料:如果表中预期的数据量较大,通常都需要考虑使用分区表,确定使用分区表后,还要确定什么类型的分区(range partition、hash partition、list partition等)、分区区间大小等。分区的创建最好与程序有某种默契,偶曾经创建分区表,按自然月份定义分区的,但程序却在查询时默认的开始时间与结束时间是:当前日期-30至当前日期,比如当天是9.18号,那查询条件被产生为8.18-9.18,结果分区后并不没有大幅提高性能,后来对程序的查询日期做了调整,按自然月查询,系统的负载小了很多。从Oracle8.0开始支持表分区(MSSQL2005开始支持表分区)。 Oracle9i 分区能够提高许多应用程序的可管理性、性能与可用性。分区可以将表、索引及索引编排表进一步划分,从而可以更精细地对这些数

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/235507/viewspace-559937/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-08

  • 博文量
    209
  • 访问量
    864848