Oracle表分區(qū)和索引分區(qū)匯總
為了簡化數(shù)據(jù)庫大表的管理,例如在數(shù)據(jù)倉庫中一般都是TB級(jí)的數(shù)量級(jí).ORACLE8以后推出了分區(qū)選項(xiàng).分區(qū)將表分離在若于不同的表空間上,用分而治之的方法來支撐元限膨脹的大表,組大表在物理一級(jí)的可管理性.將大表分割成較小的分區(qū)可以改善表的維護(hù)、備份、恢復(fù)、事務(wù)及查詢性能。
分區(qū)的優(yōu)點(diǎn)：
1、 增強(qiáng)可用性：如果表的一個(gè)分區(qū)由于系統(tǒng)故障而不能使用，表的其余好的分區(qū)仍可以使用；
2、 減少關(guān)閉時(shí)間：如果系統(tǒng)故障只影響表的一部份分區(qū)，那么只有這部份分區(qū)需要修復(fù)，礦能比整個(gè)大表修復(fù)花的時(shí)間更少；
3、 維護(hù)輕松：如果需要得建表，獨(dú)產(chǎn)管理每個(gè)公區(qū)比管理單個(gè)大表要輕松得多；
4、 均衡I/O：可以把表的不同分區(qū)分配到不同的磁盤來平衡I/O改善性能；
5、 改善性能：對(duì)大表的查詢、增加、修改等操作可以分解到表的不同分區(qū)來并行執(zhí)行，可使運(yùn)行速度更快，在數(shù)據(jù)倉庫的TP查詢特別有用。
6、 分區(qū)對(duì)用戶透明，最終用戶感覺不到分區(qū)的存在。
create tablespace dw1
datafile 'D:\oracle\oradata\ora9\dw11.ora' size 50M
create tablespace dw2
datafile 'D:\oracle\oradata\ora9\dw21.ora' size 50M
一、按范圍分區(qū)：固名思義就是按一定range來分區(qū)，看下面的例子:
SQL> set linesize 1000
SQL> create table niegc_part
(
part_id integer primary key,
part_date date,
part_dec varchar2(100)
)
partition by range(part_date)
(
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
);
表已創(chuàng)建。
SQL>
SQL> insert into niegc_part values(1,to_date('2005-12-30','yyyy-mm-dd'),'less 20
06-01-01');
已創(chuàng)建 1 行。
SQL> commit;
提交完成。
SQL> insert into niegc_part values(2,to_date('2006-01-01','yyyy-mm-dd'),'equal 2
007-01-01');
已創(chuàng)建 1 行。
SQL> commit;
提交完成。
SQL> insert into niegc_part values(3,sysdate,'sysdate');
已創(chuàng)建 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>
相信只要對(duì)oracle 有點(diǎn)熟,都能知道上面的range分區(qū)的意思了.
兩個(gè)字段以上的range分區(qū)大同小異,請(qǐng)看下面的例子:
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分區(qū)（散列分區(qū)）。 散列分區(qū)通過指定分區(qū)編號(hào)來均勻分布數(shù)據(jù)的一種分區(qū)類型，因?yàn)橥ㄟ^在I/O設(shè)備上進(jìn)行散列分區(qū)，使行這些分區(qū)大小一致。如將part_id的數(shù)據(jù)根據(jù)自身的情況散列地存放在指定的三個(gè)表空間中：
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
);
系統(tǒng)將按part_id將記錄散列地插入三個(gè)分區(qū)中，這里也就是二個(gè)不同的表空間中。
三、復(fù)合分區(qū)。根據(jù)范圍分區(qū)后，每個(gè)分區(qū)內(nèi)的數(shù)據(jù)再散列地分布在幾個(gè)表空間中，這樣我們就要使用復(fù)合分區(qū)。復(fù)合分區(qū)是先使用范圍分區(qū)，然后在每個(gè)分區(qū)同再使用散列分區(qū)的一種分區(qū)方法，如將part_date的記錄按時(shí)間分區(qū)，然后每個(gè)分區(qū)中的數(shù)據(jù)分三個(gè)子分區(qū)，將數(shù)據(jù)散列地存儲(chǔ)在三個(gè)指定的表空間中：
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
);
先根據(jù)part_date進(jìn)行范圍分區(qū)，然后根據(jù)交易的ID將記錄散列地存儲(chǔ)在二個(gè)表空間中。
四、索引分區(qū)：
注意： 對(duì)某個(gè)字段已做了分區(qū)了，是不允許再建立索引分區(qū)的。這一點(diǎn)要非常注意。
全局索引建立時(shí)global子句允許指定索引的范圍值，這個(gè)范圍值為索引字段的范圍值：
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
)
局部索引分區(qū)的建立：（注意：表必須存在分區(qū)，此分區(qū)的個(gè)數(shù)必須和分區(qū)表的分區(qū)個(gè)數(shù)一樣，不然是建立不起來的）
create index idx_part_id on niegc_part(part_dec)
local
(
partition idx_1 tablespace dw1,
partition idx_2 tablespace dw2
)
五、分區(qū)維護(hù)：(只對(duì)范圍分區(qū))
（1）、增加一個(gè)分區(qū)：分區(qū)范圍只能往上增，不能增加一個(gè)少于原有的分區(qū)：
alter table niegc_part add partition part_03 values less than(maxvalue)
（2）、合并分區(qū)：（合并后的分區(qū)必須指下最后一個(gè)大value的分區(qū)）
alter table niegc_part merge partitions part_02,part_03 into partition part_03
（3）、刪除一個(gè)分區(qū)：
alter table niegc_part drop partition part_01
分區(qū)維護(hù)：(只對(duì)范圍分區(qū))
?。?）、增加一個(gè)分區(qū)：分區(qū)范圍只能往上增，不能增加一個(gè)少于原有的分區(qū)：
alter table tablename add partition new_partitionname values less than(maxvalue)
（2）、合并/拆分分區(qū)：（合并后的分區(qū)必須指下最后一個(gè)大value的分區(qū)）
alter table tablename merge partitions partitionname1,partitionname2 into partition partitionname2;
alter table tablename split partition partitionname1 at (xx) into (
partition newpartition1 ,partition newpartition2) ;
注意：xx為分割點(diǎn)
?。?）、刪除一個(gè)分區(qū)：
alter table niegc_part drop partition partitionname;
(4)將分區(qū)改名
alter table table_name rename Partition partition_name to partition_name
(5)將分區(qū)改表空間
alter table table_name move partition_name
tablespace tablespace_name nologging
(6)查詢特定分區(qū)
select count(*) from table_name partition (partition_name);
(7)添加數(shù)據(jù)
insert into table_name select * from table_name partition (partition_name)
(8)分區(qū)表的導(dǎo)出
userid=USER/PWD
buffer=102400
tables=table_name:partition_name,
file=E:exp_paraxxx.dmp
log=E:exp_paraxxx.log
(9)技巧：刪除表中一個(gè)字段
alter table table_name set unused column column_name;
(10)加一個(gè)字段
alter table table_name add column_name number(1);
http://www.cnblogs.com/rootq/archive/2008/12/24/1361631.html