Oracle分区表的使用

最新推荐文章于 2021-04-02 21:57:04 发布

ceclar123

最新推荐文章于 2021-04-02 21:57:04 发布

阅读量1.3k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/ceclar123/article/details/12992303

版权

Oracle 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

常用分区技术

1、范围（Range）分区

最经典的分组算法，可以针对一个字段或多个字段进行分区。

例如该表只需要保存最近三个月的数据，其余的数据将会定期清理。则可以考虑针对时间字段进行范围分区，在删除数据的时候可以通过：

Alter table 表名 truncate 分区名;

该操作相比delete语句，速度非常快，而且产生日志很少。

同时针对分区的备份恢复、导入导出操作也是很方便

缺点：

[1]分区的数据不均匀，例如针对日期字段分区，那么每个月的数据肯定是不一样的

[2]后期维护麻烦，例如针对日期字段进行分区，那么几年之后是不是要增加新的分区呢

------------------------------------------范围分区-----------------------------------
create table test_range
(
  id      number(9),
  name    varchar2(512),
  week_no number(2)
)
tablespace USERS
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
partition by range (week_no)
(
  partition p1 values less than (13) tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p2 values less than (26) tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p3 values less than (39) tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p4 values less than (52) tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);
-----加主键--------
alter table test_range add constraint pk_test_range primary key (ID);

2、哈希（Hash）分区

采用Hash算法，将数据均匀分布到各个分区

整体性能比较好，并且针对各个分区的数据的查询性能也是差不多的，

但是该分区算法适合于不经常删除的数据，例如中国十几亿人口的人员信息，单位固定资产信息，数据一旦插入就很少删除了。

同时该分区方式不适合大批量数据操作，例如历史数据的清理，数据导入导出

------------------------------------------HASH分区-----------------------------------
create table test_hash
(
  id   number(9),
  name varchar2(512),
  code varchar2(18)
)
tablespace USERS
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
partition by hash (code)
(
  partition p1 tablespace USERS,
  partition p2 tablespace USERS,
  partition p3 tablespace USERS,
  partition p4 tablespace USERS
);
/*还可以这样写
create table test_hash
(
  id   number(9),
  name varchar2(512),
  code varchar2(18)
)
tablespace USERS
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
partition by hash (code) partitions 4 store in
(
 USERS,USERS,USERS,USERS
);
*/
-----加主键--------
alter table test_hash add constraint pk_test_hash primary key (ID);

3、列表（List）分区表

该分区方式针对分区字段的枚举值进行分区，例如方位字段，可以针对东、西、南、北；

针对七大洲可以分为7个区。

列表分区与范围分区有很多类似地方，都适合大数据操作，存在分布不均匀的问题，

才外该分区方式还有以下特点：

[1]列表分区是排序

[2]分区之前没有关联关系

[3]该分区方式只能针对一个字段

------------------------------------------列表分区-----------------------------------
create table test_list
(
  id           number(9),
  address      varchar2(512),
  country_name varchar2(512)
)
tablespace USERS
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
partition by list (country_name)
(
  partition p1 values ('China') tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p2 values ('USA') tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p3 values ('Japan') tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p4 values ('Canada') tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);
-----加主键--------
alter table test_list add constraint pk_test_list primary key (ID);

4、组合分区

常用的组合分区方式有：

[1]Range-Range

[2]Range-List

[3]Range-Hash

[4]List-List

[5]List-Hash

[6]List-Range

不过好像在11G以前只能使用Range-Hash与Range-List分区方式

------------------------------------------范围-列表分区-----------------------------------
create table TEST_RANGE_LIST
(
  ID           NUMBER(9) not null,
  NAME         VARCHAR2(512),
  SDATE        DATE,
  COUNTRY_NAME VARCHAR2(512)
)
partition by range (SDATE)
subpartition by list (COUNTRY_NAME)
(
  partition P1 values less than (TO_DATE(' 2012-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P1_SP1 values ('China') tablespace USERS,
    subpartition P1_SP2 values ('USA') tablespace USERS,
    subpartition P1_SP3 values ('Japan') tablespace USERS,
    subpartition P1_SP4 values ('Canada') tablespace USERS
  ),
  partition P2 values less than (TO_DATE(' 2013-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P2_SP1 values ('China') tablespace USERS,
    subpartition P2_SP2 values ('USA') tablespace USERS,
    subpartition P2_SP3 values ('Japan') tablespace USERS,
    subpartition P2_SP4 values ('Canada') tablespace USERS
  ),
  partition P3 values less than (TO_DATE(' 2014-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P3_SP1 values ('China') tablespace USERS,
    subpartition P3_SP2 values ('USA') tablespace USERS,
    subpartition P3_SP3 values ('Japan') tablespace USERS,
    subpartition P3_SP4 values ('Canada') tablespace USERS
  ),
  partition P4 values less than (TO_DATE(' 2015-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P4_SP1 values ('China') tablespace USERS,
    subpartition P4_SP2 values ('USA') tablespace USERS,
    subpartition P4_SP3 values ('Japan') tablespace USERS,
    subpartition P4_SP4 values ('Canada') tablespace USERS
  )
);
-----加主键--------
alter table TEST_RANGE_LIST add constraint PK_TEST_RANGE_LIST primary key (ID);
/*如果每个大分区下面的子分区是一样的，可以这样写
create table TEST_RANGE_LIST
(
  ID           NUMBER(9) not null,
  NAME         VARCHAR2(512),
  SDATE        DATE,
  COUNTRY_NAME VARCHAR2(512)
)
partition by range (SDATE)
subpartition by list (COUNTRY_NAME)
  subpartition template
  (
    subpartition SP1 values ('China') tablespace USERS,
    subpartition SP2 values ('USA') tablespace USERS,
    subpartition SP3 values ('Japan') tablespace USERS,
    subpartition SP4 values ('Canada') tablespace USERS
  )
(
  partition P1 values less than (TO_DATE(' 2012-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    ),
  partition P2 values less than (TO_DATE(' 2013-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    ),
  partition P3 values less than (TO_DATE(' 2014-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    ),
  partition P4 values less than (TO_DATE(' 2015-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
);
*/

------------------------------------------范围-HASH分区-----------------------------------
create table TEST_RANGE_HASH
(
  ID    NUMBER(9) not null,
  NAME  VARCHAR2(512),
  SDATE DATE,
  CODE  VARCHAR2(18)
)
partition by range (SDATE)
subpartition by hash (CODE)
(
  partition P1 values less than (TO_DATE(' 2012-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P1_SP1 tablespace USERS,
    subpartition P1_SP2 tablespace USERS,
    subpartition P1_SP3 tablespace USERS,
    subpartition P1_SP4 tablespace USERS
  ),
  partition P2 values less than (TO_DATE(' 2013-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P2_SP1 tablespace USERS,
    subpartition P2_SP2 tablespace USERS,
    subpartition P2_SP3 tablespace USERS,
    subpartition P2_SP4 tablespace USERS
  ),
  partition P3 values less than (TO_DATE(' 2014-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P3_SP1 tablespace USERS,
    subpartition P3_SP2 tablespace USERS,
    subpartition P3_SP3 tablespace USERS,
    subpartition P3_SP4 tablespace USERS
  ),
  partition P4 values less than (TO_DATE(' 2015-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
  (
    subpartition P4_SP1 tablespace USERS,
    subpartition P4_SP2 tablespace USERS,
    subpartition P4_SP3 tablespace USERS,
    subpartition P4_SP4 tablespace USERS
  )
);
-----加主键--------
alter table test_range_hash  add constraint pk_test_range_hash primary key (id);
/*可以这样写
create table TEST_RANGE_HASH
(
  ID    NUMBER(9) not null,
  NAME  VARCHAR2(512),
  SDATE DATE,
  CODE  VARCHAR2(18)
)
partition by range (SDATE)
subpartition by hash (CODE)
(
  partition P1 values less than (TO_DATE(' 2012-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )  
   subpartitions 4 store in(users,users,users,users),
  partition P2 values less than (TO_DATE(' 2013-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    ) 
      subpartitions 4 store in(users,users,users,users),
  partition P3 values less than (TO_DATE(' 2014-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )  
      subpartitions 4 store in(users,users,users,users),
  partition P4 values less than (TO_DATE(' 2015-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8
      minextents 1
      maxextents unlimited
    )
      subpartitions 4 store in(users,users,users,users)  
);
*/

/* 可以这样写
create table test_range_hash
(
  id    number(9),
  name  varchar2(512),
  sdate date,
  code  varchar2(18)
)
tablespace USERS
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
partition by range (sdate)
subpartition by hash(code)
 subpartition template
 (
    subpartition sp1 tablespace users,
    subpartition sp2 tablespace users,
    subpartition sp3 tablespace users,
    subpartition sp4 tablespace users
 )
(
  partition p1 values less than (TO_DATE(' 2012-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS')) tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p2 values less than (TO_DATE(' 2013-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS'))  tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p3 values less than (TO_DATE(' 2014-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS'))  tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition p4 values less than (TO_DATE(' 2015-01-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS'))  tablespace USERS
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);
*/

11G中出现新的分区技术

1、间隔（Interval）分区

在范围分区的时候，存在一个管理的不和谐的地方，就是要定期创建新的分区，或者一次性把未来几年的分区都创建；

新的间隔分区可以执行时间间隔，那么在时间到来的时候，会自动创建新的分区；

组合分区增加三种：

[1]Interval-Range

[2]Interval-Hash

[3]Interval-List

2、居于虚拟列（VirtualColumn-Based）的分区

3、引用（Reference）分区

4、系统（System）分区

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------分区表操作-------------------------------------------------------------------------------------------------------------------
--添加分区

alter table test add partition p5 values less than (90) tablespace users update global indexes;

--删除分区

alter table test drop partition p5 update global indexes;

--截断分区

alter table test truncate partition p5 update global indexes;

--移动分区

alter table test move partition p5 tablespace users storage (initial 100m next 100m) update global indexes;

--重命名分区

alter table test rename partition p5 to p5_1;

--分割分区(p5分成p6与p7)

alter table test split partition p5 at(60) into
(
    partition p6,
    partition p7
)update global indexes;

--合并分区(将p6与p7合并成p5)

alter table test merge partitions p6,p7 into partition p5 update global indexes;

--分区交换

alter table test exchange partition p1  with table test_temp;

ceclar123

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Oracle分区表的使用

常用分区技术1、范围（Range）分区最经典的分组算法，可以针对一个字段或多个字段进行分区。例如该表只需要保存最近三个月的数据，其余的数据将会定期清理。则可以考虑针对时间字段进行范围分区，在删除数据的时候可以通过：Alter table 表名 truncate 分区名;该操作相比delete语句，速度非常快，而且产生日志很少。同时针对分区的备份恢复、导入导出操作也是很方
复制链接

扫一扫