Oracle表分區和索引分割區匯總

Oracle表分區和索引分割區匯總

為了簡化資料庫大表的管理,例如在資料倉儲中一般都是TB級的數量級.ORACLE8以後推出了分區選項.分區將表分離在若於不同的資料表空間上,用分而治之的方法來支撐元限膨脹的大表,組大表在物理一級的可管理性.將大表分割成較小的分區可以改善表的維護、備份、恢複、事務及查詢效能。

分區的優點：

1、  增強可用性：如果表的一個分區由於系統故障而不能使用，表的其餘好的分區仍可以使用；

2、  減少關閉時間：如果系統故障隻影響表的一部份分區，那麼只有這部份分區需要修複，礦能比整個大表修複花的時間更少；

3、  維護輕鬆：如果需要得建表，獨產管理每個公區比管理單個大表要輕鬆得多；

4、  均衡I/O：可以把表的不同分區分配到不同的磁碟來平衡I/O改善效能；

5、  改善效能：對大表的查詢、增加、修改等操作可以分解到表的不同分區來並存執行，可使運行速度更快，在資料倉儲的TP查詢特別有用。

6、  分區對使用者透明，終端使用者感覺不到分區的存在。

create tablespace dw1

datafile 'D:\oracle\oradata\ora9\dw11.ora' size 50M
 

create tablespace dw2

datafile 'D:\oracle\oradata\ora9\dw21.ora' size 50M

 

一、定界分割：固名思義就是按一定range來分區，看下面的例子:

SQL> set linesize 1000

SQL> create table niegc_part

  2  (

  3  part_id integer primary key,

  4  part_date date,

  5  part_dec varchar2(100)

  6  )

  7  partition by range(part_date)

  8  (

  9  partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dw1,

 10  partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) tablespace dw2,

 11  partition part_03 values less than(maxvalue) tablespace dw1

 12  );

 

表已建立。

 

SQL>

SQL> insert into niegc_part values(1,to_date('2005-12-30','yyyy-mm-dd'),'less 20

06-01-01');

 

已建立 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> insert into niegc_part values(2,to_date('2006-01-01','yyyy-mm-dd'),'equal 2

007-01-01');

 

已建立 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> insert into niegc_part values(3,sysdate,'sysdate');

 

已建立 1 行。

 

SQL> commit;

 

提交完成。

 

SQL>

SQL>

SQL> select * from niegc_part partition(part_01);

 

   PART_ID PART_DATE  PART_DEC

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

         1 30-12月-05 less 2006-01-01

SQL>

 

相信只要對oracle 有點熟,都能知道上面的range分區的意思了.

 

兩個欄位以上的range分區大同小異,請看下面的例子:

create table niegc_part
(
part_id integer primary key,
part_date date,
part_dec varchar2(100)
)
partition by range(part_id,part_date)
(
partition part_01 values less than(1,to_date('2006-01-01','yyyy-mm-dd')) tablespace dw,
partition part_02 values less than(10,to_date('2007-01-01','yyyy-mm-dd')) tablespace dw,
partition part_03 values less than(maxvalue,maxvalue) tablespace dw
);
 

 

二、Hash分區（散列分區）。 散列分區通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使行這些分區大小一致。如將part_id的資料根據自身的情況散列地存放在指定的三個資料表空間中：

create table niegc_part

(

part_id integer primary key,

part_date date,

part_dec varchar2(100)

)

partition by hash(part_id)

(

partition part_01  tablespace dw1,

partition part_02  tablespace dw2

);

 

系統將按part_id將記錄散列地插入三個分區中，這裡也就是二個不同的資料表空間中。

 

三、複合分區。根據定界分割後，每個分區內的資料再散列地分布在幾個資料表空間中，這樣我們就要使用複合分區。複合分區是先使用定界分割，然後在每個分區同再使用散列分區的一種分區方法，如將part_date的記錄按時間分區，然後每個分區中的資料分三個子分區，將資料散列地儲存在三個指定的資料表空間中：

create table niegc_part

(

part_id integer primary key,

part_date date,

part_dec varchar2(100)

)

partition by range(part_date) subpartition by hash(part_id)

subpartitions 2 store in(dw1,dw2)

(

partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dw1,

partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) tablespace dw2,

partition part_03 values less than(maxvalue) tablespace dw1

);

先根據part_date進行定界分割，然後根據交易的ID將記錄散列地儲存在二個資料表空間中。

 

 

四、索引分割區：

注意： 對某個欄位已做了分區了，是不允許再建立索引分割區的。這一點要非常注意。
全域索引建立時global子句允許指定索引的範圍值，這個範圍值為索引欄位的範圍值：

create index idx_part_id on niegc_part(part_dec)

global partition by range(part_dec)

(

partition idx_1 values less than('1000') tablespace dw,

partition idx_2 values less than(maxvalue) tablespace dw

)

 

局部索引分割區的建立：（注意：表必須存在分區，此分區的個數必須和分區表的分區個數一樣，不然是建立不起來的）

create index idx_part_id on niegc_part(part_dec)

local

(

partition idx_1 tablespace dw1,

partition idx_2 tablespace dw2

)

 

五、分區維護：(只對定界分割)

（1）、增加一個分區：分區範圍只能往上增，不能增加一個少於原有的分區：

alter table niegc_part add partition part_03 values less than(maxvalue)

（2）、合并分區：（合并後的分區必須指下最後一個大value的分區）

alter table niegc_part merge partitions part_02,part_03 into  partition part_03

（3）、刪除一個分區：

alter table niegc_part drop partition part_01

 

分區維護：(只對定界分割)

　　（1）、增加一個分區：分區範圍只能往上增，不能增加一個少於原有的分區：

　　alter table tablename add partition new_partitionname values less than(maxvalue)

　　（2）、合并/拆分分區：（合并後的分區必須指下最後一個大value的分區）

　　alter table tablename merge partitions partitionname1,partitionname2 into partition partitionname2;

　　alter table tablename split partition partitionname1 at (xx) into (

　　partition newpartition1 ,partition newpartition2) ;

　　注意：xx為分割點

　　（3）、刪除一個分區：

　　alter table niegc_part drop partition partitionname;

　　(4)將分區改名

　　alter table table_name rename Partition partition_name to partition_name

　　(5)將分區改資料表空間

　　alter table table_name move partition_name

　　tablespace tablespace_name nologging

　　(6)查詢特定分區

　　select count(*) from table_name partition (partition_name);

　　(7)添加資料

　　insert into table_name select * from table_name partition (partition_name)

　　(8)分區表的匯出

　　userid=USER/PWD

　　buffer=102400

　　tables=table_name:partition_name,

　　file=E:exp_paraxxx.dmp

　　log=E:exp_paraxxx.log

　　(9)技巧：刪除表中一個欄位

　　alter table table_name set unused column column_name;

　　(10)加一個欄位

　　alter table table_name add column_name number(1);