Oracle Interval-Partition解决Range分区大难题

最新推荐文章于 2024-05-24 10:54:19 发布

不断学习的ITer

最新推荐文章于 2024-05-24 10:54:19 发布

阅读量3.1k

点赞数

分类专栏： Sqlserver/Oracle/Hadoop

Sqlserver/Oracle/Hadoop 专栏收录该内容

41 篇文章 1 订阅

订阅专栏

转载自：http://www.2cto.com/database/201309/244285.html

Oracle Interval-Partition解决Range分区大难题

《oracle分区》中讲了oracle的几种分区，并且对于oracle的典型分区如Range分区和List分区给了示例。

在实际运用Range分区时，遇到了这样的难题：

 
         createtableTMP_LXQ_1 
        
         ( 
        
         PROPOSALNO             VARCHAR2(22), 
        
         STARTDATE              
         DATE 
        
         ) 
        
         partitionbyrange(STARTDATE) ( 
        
         partitionpart_t01values less than(to_date( 
         '2008-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
         partitionpart_t02values less than(to_date( 
         '2009-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
         partitionpart_t03values less than(to_date( 
         '2010-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
         partitionpart_t04values less than(to_date( 
         '2011-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
         partitionpart_t05values less than(to_date( 
         '2012-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
         partitionpart_t06values less than(to_date( 
         '2013-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
         partitionpart_t07values less than(maxvalue) 
        
         );

本例中的时间只是到了2013年，然后2013年之后的时间完全放入到了maxvalue中，这确实也是一种解决办法，即有除去2008-2013年的数据时，数据库不会报错。但是这样就改变了我们分区的初衷，分区是想让各个部分的数据均衡，以加快查询。

在oracle11g出现之前，实际工作中经常会遇到这种情况，而处理这种情况，通常是由DBA或者开发人员手动进行分区或者直接定义maxvalue。

Oracle 11g的新增特性Interval分区可以解决这个问题，下面介绍一下Interval分区。

一、interval分区

INTERVAL分区是Oracle11g新增的特性，它是针对Range类型分区的一种功能拓展。对连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，Interval-Partition特性可以实现自动的分区创建。

示例：

 
         createtable TMP_LXQ_1 
        
 
         ( 
        
 
         proposalnovarchar2(22), 
        
 
         startdatedate 
        
 
         ) 
        
 
         partitionby range(STARTDATE) 
        
 
         interval(numtoyminterval(1, 
         'year' 
         ))( 
        
 
         partitionpart_t01 
         values 
         less than(to_date( 
         '2008-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
 
         partitionpart_t02 
         values 
         less than(to_date( 
         '2009-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
 
         partitionpart_t03 
         values 
         less than(to_date( 
         '2010-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
 
         partitionpart_t04 
         values 
         less than(to_date( 
         '2011-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
 
         partitionpart_t05 
         values 
         less than(to_date( 
         '2012-01-01' 
         , 
         'yyyy-mm-dd' 
         )) , 
        
 
         partitionpart_t06 
         values 
         less than(to_date( 
         '2013-01-01' 
         , 
         'yyyy-mm-dd' 
         )) 
        
 
         ); 
        

如果插入2014年的值，系统会自动的添加一个分区，分区范围为2014-01-01到2014-12-31日。

--如果对分区名不太爽，则可以自己修改一下：

alter table t_range rename partition SYS_P21 to p_2014_02_01

alter table t_range set STORE IN (tablespace1, tablespace2, tablespace3);
这3个表空间，分区会循环分配到这3个表空间。

二、interval分区和range分区的转换

ALTERTABLE TMP_LXQ_1 SET INTERVAL (numtoyminterval(1,'year'));

对于INTERVAL分区表，新增的超过分区上限的数据会自动导致对应的INTERVAL分区被建立。

同样INTERVAL分区表可以方便的转化为RANGE分区表，只需要不输入INTERVAL的值即可：

ALTER TABLETMP_LXQ_1 SET INTERVAL ();

三、interval分区的特点

1.由range分区派生而来

2.以定长宽度创建分区（比如年、月、具体的数字（比如100、500等））

3.分区字段必须是number或date类型

4.必须至少指定一个range分区（永久分区）

5.当有记录插入时，系统根据需要自动创建新的分区和本地索引

6.已有的范围分区可被转换成间隔分区（通过ALTER TABLE SET INTERVAL选项完成）

7.IntervalPartitioning不支持支持索引组织表

8.在Interval Partitioning表上不能创建domain index

四、interval分区问题

在oracle自动创建分区的时候，系统会默认指定一个分区名，系统默认创建的分区名字与我们的分区命名规范会有一定的差距，这个问题暂时还不知道有什么解决方法。

备注：

显示分区表信息
　　显示数据库所有分区表的信息：DBA_PART_TABLES
　　显示当前用户可访问的所有分区表信息：ALL_PART_TABLES
　　显示当前用户所有分区表的信息：USER_PART_TABLES
　　显示表分区信息 显示数据库所有分区表的详细分区信息：DBA_TAB_PARTITIONS
　　显示当前用户可访问的所有分区表的详细分区信息：ALL_TAB_PARTITIONS
　　显示当前用户所有分区表的详细分区信息：USER_TAB_PARTITIONS
　　显示子分区信息 显示数据库所有组合分区表的子分区信息：DBA_TAB_SUBPARTITIONS
　　显示当前用户可访问的所有组合分区表的子分区信息：ALL_TAB_SUBPARTITIONS
　　显示当前用户所有组合分区表的子分区信息：USER_TAB_SUBPARTITIONS
　　显示分区列 显示数据库所有分区表的分区列信息：DBA_PART_KEY_COLUMNS
　　显示当前用户可访问的所有分区表的分区列信息：ALL_PART_KEY_COLUMNS
　　显示当前用户所有分区表的分区列信息：USER_PART_KEY_COLUMNS
　　显示子分区列 显示数据库所有分区表的子分区列信息：DBA_SUBPART_KEY_COLUMNS
　　显示当前用户可访问的所有分区表的子分区列信息：ALL_SUBPART_KEY_COLUMNS
　　显示当前用户所有分区表的子分区列信息：USER_SUBPART_KEY_COLUMNS

        查看数据库中所有数据文件的名称位置,所属表空间,以及大小等等  dba_data_files

　　---------------------------------------------------------------------------------------------------
　　select * from user_tables a where a.partitioned='YES'

如果不想使用以上的方法动态增加表的分区，就需要用下面的方式手工增加分区：

1：增加tablespace

create tablespace 空间_2019
datafile '/oradata/空间_2019.dbf' size 1500M
DEFAULT STORAGE (
initial 64K
next 1M
minextents 1
maxextents unlimited
);

如果不知道如何设置tablespace存放地址，可以查看select * from dba_data_files; 参照写个地址

2：增加表分区

alter table POSITION_GPSONE_YEAR add partition PART_2013 values less than ('2014') tablespace TBSERVER;

表名：POSITION_GPSONE_YEAR

分区名：PART_2013

分区字段的值要小于该值：2014

这个表所在的表空间：TBSERVER

不断学习的ITer

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Oracle Interval-Partition解决Range分区大难题

转载自：http://www.2cto.com/database/201309/244285.htmlOracle Interval-Partition解决Range分区大难题《oracle分区》中讲了oracle的几种分区，并且对于oracle的典型分区如Range分区和List分区给了示例。在实际运用Range分区时，遇到了这样的难题：
复制链接

扫一扫