oracle提供了以下几种分区类型：
范围分区（range）
哈希分区（hash）
列表分区（list）
范围－哈希复合分区（range-hash）
范围－列表复合分区（range-list）
查看分区数 ：select*from user_tab_partitions where table_name=’表名’
查看分区内容：select * from 表名 partition(分区名) ;
alter table 表名 add partition 分区名 values (分区字段)
tablespace tbs_zba_czc –表空间
pctfree 10 –预留的空间大小，10%
initrans 1 –的是一个 block 上初始预分配给并行交易控制的空间
maxtrans 255 –如果initrans 不够了，自动扩展，最大这个值
分区表示例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
create table temp_fee( month_id varchar2(6), prov_id varchar2(3), total_fee number) nologging partition by range (month_id) --主分区 subpartition by list (prov_id) --子分区 ( partition part201606 values less than ('201711') tablespace tbs_zba_jm pctfree 10 initrans 1 maxtrans 255 ( subpartition sys_subp20429 values (default) tablespace tbs_zba_jm ) ); |
一、范围分区(特别要注意的是”范围”中不包含=)
1、单范围
|
1 2 3 4 5 6 7 8 9 10 11 12 |
create table temp_fee( month_id varchar2(6), prov_id varchar2(3), device_number varchar2(40)) nologging partition by range (month_id) ( partition part201606 values less than ('201711'), partition part201607 values less than ('201710'), partition part201608 values less than ('201709'), partition part201609 values less than ('201708') ); |
2、多范围分区
|
1 2 3 4 5 6 7 8 9 10 11 12 |
create table temp_fee( month_id varchar2(6), prov_id varchar2(3), device_number varchar2(40)) nologging partition by range (month_id,prov_id) ( partition part201606 values less than ('201711','011'), partition part201607 values less than ('201710','012'), partition part201608 values less than ('201709','013'), partition part201609 values less than ('201708','014') ); |
3、循环分区
1）建表
|
1 2 3 4 5 6 7 8 9 |
create table temp_fee( month_id varchar2(6), prov_id varchar2(3), device_number varchar2(40)) nologging partition by range (month_id) ( partition part201606 values less than ('201711') ); |
2）分区拓展
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare v_table varchar2(64):='temp_fee'; v_month_start varchar2(8) := '201801'; v_month_end varchar2(8) := '201803'; i varchar2(8); v_sql varchar2(5000); v_nmon varchar2(8); begin i := v_month_start; while i <= v_month_end loop v_nmon := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm'); v_sql := 'alter table '||v_table||' add partition part'||i||' values less than ('''||v_nmon||''')'; execute immediate v_sql ; i := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm'); end loop; commit; end; |
二、列表分区
1、建表
|
1 2 3 4 5 6 7 8 9 |
create table temp_qw( prov_id varchar2(3), d_number varchar2(15), user_id varchar2(15)) nologging partition by list (prov_id) ( partition part09 values ('009') ); |
2、分区拓展
|
1 2 3 4 5 6 7 8 9 10 |
declare v_table varchar2(64):='temp_qw'; v_sql varchar2(5000); begin for j in (select prov_id from prov order by prov_id) loop v_sql := 'alter table '||v_table||' add partition part'||j.prov_id||' values ('''||j.prov_id||''')'; execute immediate v_sql ; end loop; commit; end; |
需要注意的问题：
一但列表分区后，如果插入了未分区的字段会报错，为了防止出现这种情况一般我们在添加完分区后
alter table temp_qw add partition part_default values (default);
这样做的后续问题就是，在想添加分区的时候就的把这个分区删除掉
示例：
|
1 2 3 4 5 6 7 8 9 10 11 |
create table temp_qwe( prov_id varchar2(3), d_number varchar2(15), user_id varchar2(15)) nologging partition by list (prov_id) ( partition part11 values ('011'), partition part10 values ('010'), partition part09 values (default) ); |
删除分区：alter table temp_qwe drop partition part09
需要注意的是，在删除分区的时候一定要将分区内数据备份，不然删除分区的时候会将数据删除
三、哈希分区
再碰到未知怎么去分区的时候
|
1 2 3 4 5 6 7 8 9 |
create table test( transaction_id number primary key, item_id number(8) not null) partition by hash(transaction_id) ( partition part_01 tablespace tablespace01, partition part_02 tablespace tablespace02, partition part_03 tablespace tablespace03 ); |
四、组合分区
在生产中我们会常常用到这样的分区
示例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
create table temp_lfc_zcdwk_acct (month_id varchar2(6), day_id varchar2(6), prov_id varchar2(6), flag varchar2(2) ) partition by range (month_id, prov_id) subpartition by list (day_id) ( partition part201801_049 values less than ('201801', '050') ( subpartition part201801_049_subpart_01 values ('01') , subpartition part201801_049_subpart_02 values ('02') , subpartition part201801_049_subpart_03 values ('03') , subpartition part201801_049_subpart_04 values ('04') , subpartition part201801_049_subpart_05 values ('05') , subpartition part201801_049_subpart_06 values ('06') , subpartition part201801_049_subpart_07 values ('07') , subpartition part201801_049_subpart_08 values ('08') , subpartition part201801_049_subpart_09 values ('09') , subpartition part201801_049_subpart_10 values ('10') , subpartition part201801_049_subpart_11 values ('11') , subpartition part201801_049_subpart_12 values ('12') , subpartition part201801_049_subpart_13 values ('13') , subpartition part201801_049_subpart_14 values ('14') , subpartition part201801_049_subpart_15 values ('15') , subpartition part201801_049_subpart_16 values ('16') , subpartition part201801_049_subpart_17 values ('17') , subpartition part201801_049_subpart_18 values ('18') , subpartition part201801_049_subpart_19 values ('19') , subpartition part201801_049_subpart_20 values ('20') , subpartition part201801_049_subpart_21 values ('21') , subpartition part201801_049_subpart_22 values ('22') , subpartition part201801_049_subpart_23 values ('23') , subpartition part201801_049_subpart_24 values ('24') , subpartition part201801_049_subpart_25 values ('25') , subpartition part201801_049_subpart_26 values ('26') , subpartition part201801_049_subpart_27 values ('27') , subpartition part201801_049_subpart_28 values ('28') , subpartition part201801_049_subpart_29 values ('29') , subpartition part201801_049_subpart_30 values ('30') , subpartition part201801_049_subpart_31 values ('31') ), partition part201801_050 values less than ('201801', '051') ( subpartition part201801_050_subpart_01 values ('01') , subpartition part201801_050_subpart_02 values ('02') , subpartition part201801_050_subpart_03 values ('03') , subpartition part201801_050_subpart_04 values ('04') , subpartition part201801_050_subpart_05 values ('05') , subpartition part201801_050_subpart_06 values ('06') , subpartition part201801_050_subpart_07 values ('07') , subpartition part201801_050_subpart_08 values ('08') , subpartition part201801_050_subpart_09 values ('09') , subpartition part201801_050_subpart_10 values ('10') , subpartition part201801_050_subpart_11 values ('11') , subpartition part201801_050_subpart_12 values ('12') , subpartition part201801_050_subpart_13 values ('13') , subpartition part201801_050_subpart_14 values ('14') , subpartition part201801_050_subpart_15 values ('15') , subpartition part201801_050_subpart_16 values ('16') , subpartition part201801_050_subpart_17 values ('17') , subpartition part201801_050_subpart_18 values ('18') , subpartition part201801_050_subpart_19 values ('19') , subpartition part201801_050_subpart_20 values ('20') , subpartition part201801_050_subpart_21 values ('21') , subpartition part201801_050_subpart_22 values ('22') , subpartition part201801_050_subpart_23 values ('23') , subpartition part201801_050_subpart_24 values ('24') , subpartition part201801_050_subpart_25 values ('25') , subpartition part201801_050_subpart_26 values ('26') , subpartition part201801_050_subpart_27 values ('27') , subpartition part201801_050_subpart_28 values ('28') , subpartition part201801_050_subpart_29 values ('29') , subpartition part201801_050_subpart_30 values ('30') , subpartition part201801_050_subpart_31 values ('31') )); |
双分区
1、建表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create table temp_ee( month_id varchar2(6), prov_id varchar2(3), device_number varchar2(40)) nologging partition by range (month_id) subpartition by list (prov_id) ( partition part201606 values less than ('201711') ( subpartition sys_default values (default) ) ); |
2、分区拓展
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
declare v_table varchar2(64):='temp_ee'; v_month_start varchar2(8) := '201711'; v_month_end varchar2(8) := '201803'; i varchar2(8); v_sql varchar2(5000); v_nmon varchar2(8); begin i := v_month_start; while i <= v_month_end loop v_nmon := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm'); v_sql := 'alter table '||v_table||' add partition part'||i||' values less than ('''||v_nmon||''') ( '; for j in (select prov_id from prov order by prov_id) loop v_sql := v_sql || ' subpartition part'||i||'_subpart'||j.prov_id||' values ('''||j.prov_id||''') , '; end loop; v_sql := v_sql || ' subpartition part'||i||'_subpartdefault values (default) ) '; execute immediate v_sql ; i := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm'); end loop; commit; end; |