分区表通过对分区列进行判断，把满足不同条件的分区列对应的记录保存在不同的分区中。
一、何为分区表
什么情况下会使用分区表？
表中已有大量数据，或预计到表中将会保存大量的数据
可以按照预期（月份、区域、dml）对表中的数据执行查询和更新
什么是分区键？
创建分区表，需要分区键；分区表的每一列都明确的归属一个分区，划分的依据就是分区键。
有如下特点：
由1~~16个列顺序组成
不能包含Level、RowId、MisLabel伪列
不能包含为空的列
Oracle支持哪些类型的分区方式？
范围分区——将分区表中的记录映射到基于分区键列值范围的分区，例如：按照月份划分
散列分区——基于分区键的散列值进行映射到分区中，也就是字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。
列表分区——根据分区键的值明确定义其归属的分区，例如：华北、东北等区块
组合范围-散列分区——范围和散列的组合，例如：按月份对业绩进行分区，并用散列分区
组合范围-列表分区——范围和列表的组合，例如：按月份对业绩进行分区，并用地域分区
什么情况下使用分区表，在多大的数据量时？
记录数在1000w以上
表中数据有百万，但每条记录的数量比较大
二、应用场景
需求：信用卡消费流程
--创建用户Credit
create user credit identified by pwd default tablespace users temporary tablespace temp;
--向Credit授权
grant create session to credit;
grant create table to credit;
grant create sequence to credit;
grant create user,alter user,drop user to credit;
--向用户credit授予表空间users的配额
alter user credit quota 100M on users;
确定分区方案
首先需要确定分区的依据和分区的数量，针对信用卡可以按照consume_date进行分区
create tablespace creditTab1
datafile 'E:\app\Administrator\admin\orcl\creditDataFile01.dbf' size 50M;
create tablespace creditTab2
datafile 'E:\app\Administrator\admin\orcl\creditDataFile02.dbf' size 50M;
create tablespace creditTab3
datafile 'E:\app\Administrator\admin\orcl\creditDataFile03.dbf' size 50M;
create tablespace creditTab4
datafile 'E:\app\Administrator\admin\orcl\creditDataFile04.dbf' size 50M;
create tablespace creditTab5
datafile 'E:\app\Administrator\admin\orcl\creditDataFile05.dbf' size 50M;
create tablespace creditTab6
datafile 'E:\app\Administrator\admin\orcl\creditDataFile06.dbf' size 50M;
create tablespace creditTab7
datafile 'E:\app\Administrator\admin\orcl\creditDataFile07.dbf' size 50M;
create tablespace creditTab8
datafile 'E:\app\Administrator\admin\orcl\creditDataFile08.dbf' size 50M;
create tablespace creditTab9
datafile 'E:\app\Administrator\admin\orcl\creditDataFile09.dbf' size 50M;
create tablespace creditTab10
datafile 'E:\app\Administrator\admin\orcl\creditDataFile10.dbf' size 50M;
create tablespace creditTab11
datafile 'E:\app\Administrator\admin\orcl\creditDataFile11.dbf' size 50M;
create tablespace creditTab12
datafile 'E:\app\Administrator\admin\orcl\creditDataFile12.dbf' size 50M;--向用户credit授予表空间配额
alter user credit quota 50M on creditTab1;
alter user credit quota 50M on creditTab2;
alter user credit quota 50M on creditTab3;
alter user credit quota 50M on creditTab4;
alter user credit quota 50M on creditTab5;
alter user credit quota 50M on creditTab6;
alter user credit quota 50M on creditTab7;
alter user credit quota 50M on creditTab8;
alter user credit quota 50M on creditTab9;
alter user credit quota 50M on creditTab10;
alter user credit quota 50M on creditTab11;
alter user credit quota 50M on creditTab12;创建分区，按照consume_date划分，共12个分区
drop table credit.credit2018;
create table credit.credit2018
(consume_id number,
card_no varchar2(50),
shop varchar2(50),
goods varchar2(50),
amount number(10,2),
consume_date date
)
partition by range(consume_date)
(
partition partition1 values less than(to_date('2018-02-01','yyyy-mm-dd')) tablespace creditTab1,
partition partition2 values less than(to_date('2018-03-01','yyyy-mm-dd')) tablespace creditTab2,
partition partition3 values less than(to_date('2018-04-01','yyyy-mm-dd')) tablespace creditTab3,
partition partition4 values less than(to_date('2018-05-01','yyyy-mm-dd')) tablespace creditTab4,
partition partition5 values less than(to_date('2018-06-01','yyyy-mm-dd')) tablespace creditTab5,
partition partition6 values less than(to_date('2018-07-01','yyyy-mm-dd')) tablespace creditTab6,
partition partition7 values less than(to_date('2018-08-01','yyyy-mm-dd')) tablespace creditTab7,
partition partition8 values less than(to_date('2018-09-01','yyyy-mm-dd')) tablespace creditTab8,
partition partition9 values less than(to_date('2018-10-01','yyyy-mm-dd')) tablespace creditTab9,
partition partition10 values less than(to_date('2018-11-01','yyyy-mm-dd')) tablespace creditTab10,
partition partition11 values less than(to_date('2018-12-01','yyyy-mm-dd')) tablespace creditTab11,
partition partition12 values less than(maxvalue) tablespace creditTab12
);例如：插入了2018-01-01的消费记录则会保存在partition1分区中，大于2018-12-01的记录会被记录到partition12分区中。
--查看分区表信息
select * from dba_part_tables t where t.owner='CREDIT';partitioning_type:分区方法——包括range,hash,system,list
subpartitioning_type:组合分区方法——包括none,hash,system,list
partition_count:表中分区的数量
def_subpartition_count:在组合分区中，子分区数量
partitioning_key_count:在组合分区中，子分区中键中包含的列数量
--查看分区表中分区信息
select * from dba_tab_partitions t where t.table_owner='CREDIT';composite:是否为组合分区表
subpartition_count:如果为组合分区时，包含的子分区数
high_value:分区上限
high_value_length:分区上限值的长度
partition_position:分区在表中的位置
--创建全局范围分区索引
create index credit.idx_sonsume2018
on credit.credit2018(consume_date)
global partition by range(consume_date)
(
partition partition1 values less than(to_date('2018-02-01','yyyy-mm-dd')) tablespace creditTab1,
partition partition2 values less than(to_date('2018-03-01','yyyy-mm-dd')) tablespace creditTab2,
partition partition3 values less than(to_date('2018-04-01','yyyy-mm-dd')) tablespace creditTab3,
partition partition4 values less than(to_date('2018-05-01','yyyy-mm-dd')) tablespace creditTab4,
partition partition5 values less than(to_date('2018-06-01','yyyy-mm-dd')) tablespace creditTab5,
partition partition6 values less than(to_date('2018-07-01','yyyy-mm-dd')) tablespace creditTab6,
partition partition7 values less than(to_date('2018-08-01','yyyy-mm-dd')) tablespace creditTab7,
partition partition8 values less than(to_date('2018-09-01','yyyy-mm-dd')) tablespace creditTab8,
partition partition9 values less than(to_date('2018-10-01','yyyy-mm-dd')) tablespace creditTab9,
partition partition10 values less than(to_date('2018-11-01','yyyy-mm-dd')) tablespace creditTab10,
partition partition11 values less than(to_date('2018-12-01','yyyy-mm-dd')) tablespace creditTab11,
partition partition12 values less than(maxvalue) tablespace creditTab12
);
--查看分区索引
select * from dba_part_indexes t where t.owner='CREDIT';
--查看分区索引中分区的信息
select * from dba_ind_partitions t where t.index_owner='CREDIT';locality:区别本地、全局索引
--创建本地分区索引
create index idx_consume_date on credit.credit2018(consume_date) local;查看详细信息同上。
创建散列分区表：有些情况下，用户只希望对拥有大型数据表进行分区，但并不要求把记录放置在指定的分区中，此时可以采用散列分区的方式，由系统分区键上的值分配到不同的分区中。
日志表：
--创建3个分区
create tablespace tabLog1
datafile 'E:\app\Administrator\admin\orcl\logDataFile01.dbf' size 50M;
create tablespace tabLog2
datafile 'E:\app\Administrator\admin\orcl\logDataFile02.dbf' size 50M;
create tablespace tabLog3
datafile 'E:\app\Administrator\admin\orcl\logDataFile03.dbf' size 50M;
--创建散列分区表
create table hashPartitionLog
(
log_id number,
log_text varchar2(4000),
log_date date
)
partition by hash(log_id)
(
partition partition1 tablespace tabLog1,
partition partition2 tablespace tabLog2,
partition partition3 tablespace tabLog3
);查看分区信息同上。
--创建全局散列分区索引
create index idx_log_id
on hashPartitionLog(Log_Id)
global partition by hash(log_id)
(
partition partition1 tablespace tabLog1,
partition partition2 tablespace tabLog2,
partition partition3 tablespace tabLog3
);创建列表分区表：将销售市场按区域划分，黑龙江、吉林和辽宁为东北大区part_db，北京、天津、河北为华北大区part_hb等。
--创建4个分区
create tablespace tabMarket1
datafile 'E:\app\Administrator\admin\orcl\marketDataFile01.dbf' size 50M;
create tablespace tabMarket2
datafile 'E:\app\Administrator\admin\orcl\marketDataFile02.dbf' size 50M;
create tablespace tabMarket3
datafile 'E:\app\Administrator\admin\orcl\marketDataFile03.dbf' size 50M;
create tablespace tabMarket4
datafile 'E:\app\Administrator\admin\orcl\marketDataFile04.dbf' size 50M;
--创建列表分区表
create table saleMarket
(
area_id number,
area_name varchar2(100),
description varchar2(4000)
)partition by list(area_name)
(
partition part_db values('黑龙江','吉林','辽宁') tablespace tabMarket1,
partition part_hb values('北京','天津','河北') tablespace tabMarket2,
partition part_hn values('广东','广西','海南') tablespace tabMarket3,
partition part_qt values(default) tablespace tabMarket4
);查看分区信息同上。
创建组合范围-散列分区表
组合分区就是在分区中再创建子分区。
--1.首先为每个散列子分区创建各自的表空间
create tablespace hashTab1
datafile 'E:\app\Administrator\admin\orcl\hashDataFile01.dbf' size 50M;
create tablespace hashTab2
datafile 'E:\app\Administrator\admin\orcl\hashDataFile02.dbf' size 50M;
create tablespace hashTab3
datafile 'E:\app\Administrator\admin\orcl\hashDataFile03.dbf' size 50M;
--2.然后向用户credit授予表空间配额
alter user credit quota 50M on hashTab1;
alter user credit quota 50M on hashTab2;
alter user credit quota 50M on hashTab3;
--3.创建表consume2018
drop table credit.credit2018;
create table credit.credit2018
(
consume_id number,
card_no varchar2(50),
shop varchar2(50),
goods varchar2(50),
amount number(10,2),
consume_date date
)
partition by range(consume_date)
subpartition by hash(consume_id)
subpartitions 3 store in (hashTab1,hashTab2,hashTab3)
(
partition partition1 values less than(to_date('2018-02-01','yyyy-mm-dd')) tablespace creditTab1,
partition partition2 values less than(to_date('2018-03-01','yyyy-mm-dd')) tablespace creditTab2,
partition partition3 values less than(to_date('2018-04-01','yyyy-mm-dd')) tablespace creditTab3,
partition partition4 values less than(to_date('2018-05-01','yyyy-mm-dd')) tablespace creditTab4,
partition partition5 values less than(to_date('2018-06-01','yyyy-mm-dd')) tablespace creditTab5,
partition partition6 values less than(to_date('2018-07-01','yyyy-mm-dd')) tablespace creditTab6,
partition partition7 values less than(to_date('2018-08-01','yyyy-mm-dd')) tablespace creditTab7,
partition partition8 values less than(to_date('2018-09-01','yyyy-mm-dd')) tablespace creditTab8,
partition partition9 values less than(to_date('2018-10-01','yyyy-mm-dd')) tablespace creditTab9,
partition partition10 values less than(to_date('2018-11-01','yyyy-mm-dd')) tablespace creditTab10,
partition partition11 values less than(to_date('2018-12-01','yyyy-mm-dd')) tablespace creditTab11,
partition partition12 values less than(maxvalue) tablespace creditTab12
);查看分区信息同上。查看分区信息时可以看出，分区数量为12，子分区数量为3
还有一种是为所有分区创建相同的子分区。
--为所有分区创建相同的子分区
drop table credit.credit2018;
create table credit.credit2018
(
consume_id number,
card_no varchar2(50),
shop varchar2(50),
goods varchar2(50),
amount number(10,2),
consume_date date
)
partition by range(consume_date)
subpartition by hash(consume_id)
(
partition partition1 values less than(to_date('2018-02-01','yyyy-mm-dd')) tablespace creditTab1,
partition partition2 values less than(to_date('2018-03-01','yyyy-mm-dd')) tablespace creditTab2
(
subpartition sub_part_1 tablespace hashTab1,
subpartition sub_part_2 tablespace hashTab2,
subpartition sub_part_3 tablespace hashTab3
),
partition partition3 values less than(to_date('2018-04-01','yyyy-mm-dd')) tablespace creditTab3,
partition partition4 values less than(to_date('2018-05-01','yyyy-mm-dd')) tablespace creditTab4,
partition partition5 values less than(to_date('2018-06-01','yyyy-mm-dd')) tablespace creditTab5,
partition partition6 values less than(to_date('2018-07-01','yyyy-mm-dd')) tablespace creditTab6,
partition partition7 values less than(to_date('2018-08-01','yyyy-mm-dd')) tablespace creditTab7,
partition partition8 values less than(to_date('2018-09-01','yyyy-mm-dd')) tablespace creditTab8,
partition partition9 values less than(to_date('2018-10-01','yyyy-mm-dd')) tablespace creditTab9,
partition partition10 values less than(to_date('2018-11-01','yyyy-mm-dd')) tablespace creditTab10,
partition partition11 values less than(to_date('2018-12-01','yyyy-mm-dd')) tablespace creditTab11,
partition partition12 values less than(maxvalue) tablespace creditTab12
);查看分区信息得知，只有分区partition2中包含了3个子分区，其他分区中都是没有子分区的。
组合范围-列表分区
--为每个散列子分区创建各自的表空间
create tablespace listTab1
datafile 'E:\app\Administrator\admin\orcl\listDataFile01.dbf' size 50M;
create tablespace listTab2
datafile 'E:\app\Administrator\admin\orcl\listDataFile02.dbf' size 50M;
create tablespace listTab3
datafile 'E:\app\Administrator\admin\orcl\listDataFile03.dbf' size 50M;
create tablespace listTab4
datafile 'E:\app\Administrator\admin\orcl\listDataFile04.dbf' size 50M;
--向用户credit授予表空间配额
alter user credit quota 50M on listTab1;
alter user credit quota 50M on listTab2;
alter user credit quota 50M on listTab3;
alter user credit quota 50M on listTab4;
--创建表
create table credit.rangeListPartTable
(
id number,
name varchar2(4000)
)
partition by range(id)
subpartition by list(name)
subpartition template
(
subpartition part_a values('a') tablespace listTab1,
subpartition part_b values('b') tablespace listTab2,
subpartition part_c values('c') tablespace listTab3,
subpartition part_d values(default) tablespace listTab4
)
(
partition partition1 values less than(100) tablespace listTab1,
partition partition2 values less than(200) tablespace listTab2,
partition partition3 values less than(300) tablespace listTab3,
partition partition4 values less than(maxvalue) tablespace listTab4
);查看分区信息同上。
查询分区中的记录
--日志表，查询使用
insert into hashpartitionLog values(1,'logText1',sysdate);
insert into hashpartitionLog values(2,'logText2',sysdate);
insert into hashpartitionLog values(3,'logText3',sysdate);
insert into hashpartitionLog values(4,'logText4',sysdate);
insert into hashpartitionLog values(5,'logText5',sysdate);
insert into hashpartitionLog values(6,'logText6',sysdate);
insert into hashpartitionLog values(7,'logText7',sysdate);
insert into hashpartitionLog values(8,'logText8',sysdate);
insert into hashpartitionLog values(9,'logText9',sysdate);
insert into hashpartitionLog values(10,'logText10',sysdate);
commit;
--查看分区表中的partition1的数据
select * from hashpartitionLog partition(partition1);
省略：添加、收缩、合并、拆分、阶段、重命名、删除表分区；添加、重新编译、拆分、重命名、删除索引分区。
转载于:https://blog.51cto.com/mazongfei/2130130
939
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
