oracle 分區表方法詳解介紹

分區表的概念，簡單來說就是：原來一個使用者下表的資料是放在一個資料表空間裡，使用了分區表後，表的資料是放在多個資料表空間中。

PS：資料表空間是包括一個使用者下所有檔案的邏輯概念。

如果一個資料庫中，某幾個表（特別是曆史表）佔了資料庫50%以上的空間，或達到幾百G的磁碟容量。這時就要考慮使用分區表了。

Oracle表分區分為四種：定界分割（Range分區），散列分區（Hash分區），列表分區（List分區）和複合分區（範圍-散列分區range-hash，範圍-列表分區range-list）

分區的好處，主要用到以下兩點：
改善查詢效能
維護備份資料方便

一、定界分割
定界分割就是根據表中某一欄位的值的範圍來分區，特別適用於時間日期、ID號來分區

1、建立測試使用者

create user wangyi identified by wangyi;
grant dba to wangyi;
conn wangyi/wangyi;

2、建立測試表和資料
//策略是按月建立分區，保留一年資料，年末備份前年資料。

例子：

create table range_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by range(inst_date)
(
partition p1 values less than(to_date('01/02/2016','DD/MM/YYYY')),
partition p2 values less than(to_date('01/03/2016','DD/MM/YYYY')),
partition p3 values less than(to_date('01/04/2016','DD/MM/YYYY')),
partition p4 values less than(to_date('01/05/2016','DD/MM/YYYY')),
partition p5 values less than(to_date('01/06/2016','DD/MM/YYYY'))
);

插入測試資料：

insert into range_table values(to_date('01/02/2016','DD/MM/YYYY'),1,'0.1');
insert into range_table values(to_date('01/03/2016','DD/MM/YYYY'),2,'0.2');
insert into range_table values(to_date('01/04/2016','DD/MM/YYYY'),3,'0.3');

PS：分區表不一定要放到不同的資料表空間中，如要放入不同的資料表空間，需先建立資料表空間檔案，然後在partition語句後面加入tablespace space_nameXX。

3、查詢分區情況
1）

select * from dba_part_tables where owner = 'WANGYI';

2）

select count(*) from range_table;

  COUNT(*)
----------
  3

3）

select count(*) from range_table partition (p2);

  COUNT(*)
----------
  1

可以看到3條記錄，落到了3個分區中。

4、11g可以用interval函數自動分區

create table range_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by range(inst_date)
interval(numtoyminterval(1, 'month'))
(
partition p1 values less than(to_date('2016-01-01','YYYY-MM-DD'))
);

二、散列分區
散列分區是根據欄位的hash值進行均勻分布，儘可能的實現各分區所散列的資料相等。

例子：

create table hash_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by hash(inst_date)
(
partition p1,
partition p2,
partition p3
);

insert into hash_table values(to_date('01/02/2016','DD/MM/YYYY'),1,'0.1');
insert into hash_table values(to_date('01/03/2016','DD/MM/YYYY'),2,'0.2');
insert into hash_table values(to_date('01/04/2016','DD/MM/YYYY'),3,'0.3');

2、查詢分區情況
1）

select count(*) from hash_table;

  COUNT(*)
----------
  3

2）

select count(*) from hash_table partition (p1);

  COUNT(*)
----------
  1

3）

select count(*) from hash_table partition (p2);

  COUNT(*)
----------
  2

記錄具體落到哪個分區是由oracle計算hash值後決定的。

三、列表分區
列表分區明確指定了根據某欄位的某個具體值進行分區，而不是像定界分割那樣根據欄位的值範圍來劃分的。

列表分區不支援多列，但是定界分割和雜湊分割支援多列。

例子：

create table list_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by list(produce_id)
(
partition p1 values(1),
partition p2 values(2),
partition p3 values(3)
);

insert into list_table values(to_date('01/02/2016','DD/MM/YYYY'),1,'0.1');
insert into list_table values(to_date('01/03/2016','DD/MM/YYYY'),2,'0.2');
insert into list_table values(to_date('01/04/2016','DD/MM/YYYY'),3,'0.3');

3、查詢分區情況
1）

select count(*) from list_table partition (p1);

  COUNT(*)
----------
  1

2）

select count(*) from list_table partition (p2);

  COUNT(*)
----------
  1

3）

select count(*) from list_table partition (p3);

  COUNT(*)
----------
  1

四、複合分區（範圍-散列分區，範圍-列表分區）
複合分區就是可以在分區裡再建子分區。

比較複雜，見參考資料第一個連結。。。

五、對分區的維護

1、清空表分區資料

alter table 表名 truncate partition 分區名;

2、直接刪除分區

alter table 表名 drop partition 分區名;