oracle列分区,Oracle数据库分区--实例

分区表通过对分区列进行判断，把满足不同条件的分区列对应的记录保存在不同的分区中。

一、何为分区表

什么情况下会使用分区表？表中已有大量数据，或预计到表中将会保存大量的数据

可以按照预期(月份、区域、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);

省略：添加、收缩、合并、拆分、阶段、重命名、删除表分区；添加、重新编译、拆分、重命名、删除索引分区。