Oracle中的分区

一、作用

        Oracle数据库的分区把表中的数据行按照分区划成几个区域表中数据可以按照分区进行插入、查询和删除提高大数据量下表的性能。

二、应用场景

        常应用于数据量大的表一般类似于流水表和存储大文本数据的表。

三、具体使用

Oracle中有范围分区散列分区列表分区和复合分区四种。

3.1 范围分区(range)

        根据数据库表中某一字段的值的范围来划分分区。

create table user
  (
  id varchar2(20),
  u_name varchar2(30),
  u_sex varchar2(2),
  u_age int
)
partition by range(u_age)
(
    partition ptt_age_younger values less than(20),
    partition ptt_age_midder values less than(50), 
    partition ptt_age_older values less than(150)
 
);
--将数据的u_age值按照<20,20-50,50-150插入到分区ptt_age_younger、ptt_age_midder 、ptt_age_older 中

3.2 散列分区(Hash)

        将表中的数据平均分配到指定的几个分区中列数据具体在哪个分区是依据分区列的hash值自动分配无法预知所以不太方便根据数据分类进行清理等。

create table user
(
  u_id varchar2(20),
  u_name varchar2(20),
  u_sex varchar2(2),
  u_age int
)
partition by hash(u_name)
(
  partition ptt_name_h1,
  partition ptt_name_h2,
  partition ptt_name_h3
);
--将数据的u_name值按照Hash算法后几乎均衡分区到ptt_name_h1-3中

3.3 列表分区(list)

        列表分区需要指定分区的值具有唯一性。只能指定一个列不能像range或者hash分区那样同时指定多个列做为分区依赖列但它的单个分区对应值可以有多个。

　　插入/更新数据时如果分区列的值和分区中定义的不同则会报错导致插入/更新失败。可以创建一个default分区存储不在指定范围内的记录。

create table user
(
  u_id varchar2(20),
  u_name varchar2(20),
  u_sex varchar2(2),
  u_age int
)
partition by list(u_sex)
(
  partition ptt_sex_m values('M'),
  partition ptt_sex_f values('F')
);
--将数据的u_sex值,如果是M插入/更新分区ptt_sex_m如果是F插入/更新分区ptt_sex_fu_sex为其他数值时会报错因为这里没定义default分区

3.4 复合分区

        如果某张表按照某列进行分区后数据量仍然很大或者需要进行更细粒度的划分可以通过分区内再建子分区的方式对分区再进行分区就是复合分区。主要有范围-哈希分区(range-hash)、范围-列表分区(range-hash)

        复合分区只能是对Range分区进行再分区子分区只能是Hash分区或者List分区。

3.4.1 范围-哈希分区(range-hash)

        先对某列数据进行range分区再对分区数据进行Hash分区划分子分区。

create table user
  (
  id varchar2(20),
  u_name varchar2(30),
  u_sex varchar2(2),
  u_age int
)
partition by range(u_age)
subpartition by hash(u_name)
(
    partition ptt_age_younger values less than(20)
        (
            subpartition sptt_yg_name_h1,subpartition sptt_yg_name_h2
        ),
    partition ptt_age_midder values less than(50),
        (
            subpartition sptt_md_name_h1,subpartition sptt_md_name_h2,
        ),
    partition ptt_age_older values less than(150)
        (
            subpartition sptt_od_name_h1,subpartition sptt_od_name_h2
        )
);
--将数据的u_age值按照<20,20-50,50-150插入到分区ptt_age_younger、ptt_age_midder 、ptt_age_older 中,每个分区中的数据还会根据name进行Hash计算再划分为subpartition sptt_yg_name_h1,subpartition sptt_yg_name_h2,subpartition sptt_md_name_h1,subpartition sptt_md_name_h2,subpartition sptt_od_name_h1,subpartition sptt_od_name_h2子分区

3.4.2 范围-列表分区(range-list)

        对表数据按照某列进行range分区不同的分区中数据再按照指定list进行列表分区。相关规则按照range和list相同。

create table user
  (
  id varchar2(20),
  u_name varchar2(30),
  u_sex varchar2(2),
  u_age int
)
partition by range(u_age)
(
    partition ptt_age_younger values less than(20)
    (
        subpartition sptt_yg_sex_m values('M'),
        subpartition sptt_yg_sex_f values('F')
    ),
    partition ptt_age_midder values less than(50),
    (
        subpartition sptt_md_sex_m values('M'),
        subpartition sptt_md_sex_f values('F')
    ),
    partition ptt_age_older values less than(150)
    (
        subpartition sptt_od_sex_m values('M'),
        subpartition sptt_od_sex_f values('F')
    )
 
);
--将数据的u_age值按照<20,20-50,50-150插入到分区ptt_age_younger、ptt_age_midder 、ptt_age_older 中,再根据各个分区中数据的u_sex的值为M或者F将数据划分到sptt_yg_sex_m、sptt_yg_sex_f、sptt_md_sex_m、sptt_md_sex_f、sptt_od_sex_m、sptt_od_sex_f中