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 不够了，自动扩展，最大这个值
分区表示例：
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、单范围
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、多范围分区
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）建表
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）分区拓展
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、建表
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、分区拓展
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);
这样做的后续问题就是，在想添加分区的时候就的把这个分区删除掉
示例：
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
需要注意的是，在删除分区的时候一定要将分区内数据备份，不然删除分区的时候会将数据删除
三、哈希分区
再碰到未知怎么去分区的时候
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
);
四、组合分区
在生产中我们会常常用到这样的分区
示例：
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、建表
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、分区拓展
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;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
