Oracle Interval

aaie_ 2018-02-14

展开全文

转载自：http://www./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_t01valuesless
 than(to_date('2008-01-01','yyyy-mm-dd'))
 ,

partitionpart_t02valuesless
 than(to_date('2009-01-01','yyyy-mm-dd'))
 ,

partitionpart_t03valuesless
 than(to_date('2010-01-01','yyyy-mm-dd'))
 ,

partitionpart_t04valuesless
 than(to_date('2011-01-01','yyyy-mm-dd'))
 ,

partitionpart_t05valuesless
 than(to_date('2012-01-01','yyyy-mm-dd'))
 ,

partitionpart_t06valuesless
 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