间隔分区

Oracle11g新特性之间隔分区运用说明
一、概述
间隔分区（interval partitioning）是oracle database 11g Release 1及以上版本中的新增特性，它就是以一个区间分区表为“起点”，并在定义中增加了一个规则（即间隔），使数据库知道将来如何增加分区。
目标：当且仅当存在一个给定分区的数据而且这些数据加入数据库时才为数据创建新的分区。简单来讲，就是不需要预先为数据创建分区，而是在插入数据时让数据自己创建分区。要使用间隔分区，首先从一个没有MAXVALUE分区的区间分区表开始，指定一个要增加至上界(upper bound)的间隔（上界是最大值，如果达到这个最大值，分区表就要创建一个新的区间分区）。这个分区表要按某一列分区，而且这一列能够增加number或者interval类型的值（例如，按varchar2字段分区的分区表就不能使用间隔分区，也就是说，可以使用alter命令将现有的区间分区表修改为间隔分区，也可以使用create创建一个间隔分区。
二、 操作说明
创建一个用户，并创建间隔分区

SQL> create tablespace xysoul datafile ‘/oracle11g/product/11.2.0/oradata/loves/xysoul01.dbf’ size 1G; Tablespace created. SQL> conn xysoul/xysoulConnected.SQL> create table audit_trail (ts timestamp,data varchar2(30))  2  partition by range(ts)  3  interval (numtoyminterval(1,‘month’))  4  store in (users,xysoul)  5  (partition p0 values less than  6  (to_date(‘2010-01-01’,‘yyyy-mm-dd’))); Table created.

注意：如果按月份间隔，不能写某个月28日以后的某一天，由于2月没有31日。如果填写，报错如下：

SQL> create table audit_trail1 (ts timestamp,data varchar2(30))  2  partition by range(ts)  3  interval (numtoyminterval(1,‘month’))  4  store in (users,xysoul)  5  (partition p0 values less than  6  (to_date(‘2010-01-29’,‘yyyy-mm-dd’)));create table audit_trail1 (ts timestamp,data varchar2(30))*ERROR at line 1:ORA-14767: Cannot specify this interval with existing high bounds

查看数据字典，查看分区信息

SQL> select a.partition_name,a.tablespace_name,a.high_value,  2  decode(a.interval,‘YES’,b.interval) invertal  3  from user_tab_partitions a,user_part_tables b  4  where a.table_name=‘AUDIT_TRAIL’  5  and a.table_name=b.table_name  6  order by a.partition_position;PARTITION_NAME     TABLESPACE_NAME     HIGH_VALUE                          INVERTAL------------------ -------------------- ----------------------------------- ----------P0                 USERS                TIMESTAMP’ 2010-01-01 00:00:00’

插入一条数据，并查看，发现多了一个分区

SQL> insert into audit_trail(ts,data) values  2  (to_timestamp(‘2010-03-27’,‘yyyy-mm-dd’),‘xysoul’);SQL> col INVERTAL for a40SQL> col HIGH_VALUE for a35SQL> set lines 999SQL> col tablespace_name for a20SQL> col PARTITION_NAME for a15SQL> select a.partition_name,a.tablespace_name,a.high_value,  2  decode(a.interval,‘YES’,b.interval) invertal  3  from user_tab_partitions a,user_part_tables b  4  where a.table_name=‘AUDIT_TRAIL’  5  and a.table_name=b.table_name  6  order by a.partition_position; PARTITION_NAME  TABLESPACE_NAME      HIGH_VALUE                          INVERTAL--------------- -------------------- ----------------------------------- ----------------------------------------P0              USERS                TIMESTAMP’ 2010-01-01 00:00:00’SYS_P61         XYSOUL               TIMESTAMP’ 2010-04-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)

再次插入一条数据，并查看
SQL> insert into audit_trail(ts,data) values  2  (to_timestamp(‘2010-07-7’,‘yyyy-mm-dd’),‘lss’); 1 row created. SQL>SQL> col INVERTAL for a40SQL> col HIGH_VALUE for a35SQL> set lines 999SQL> col tablespace_name for a20SQL> col PARTITION_NAME for a15SQL> select a.partition_name,a.tablespace_name,a.high_value,  2  decode(a.interval,‘YES’,b.interval) invertal  3  from user_tab_partitions a,user_part_tables b  4  where a.table_name=‘AUDIT_TRAIL’  5  and a.table_name=b.table_name  6  order by a.partition_position; PARTITION_NAME  TABLESPACE_NAME      HIGH_VALUE                          INVERTAL--------------- -------------------- ----------------------------------- ----------------------------------------P0              USERS                TIMESTAMP’ 2010-01-01 00:00:00’SYS_P61         XYSOUL               TIMESTAMP’ 2010-04-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)SYS_P62         XYSOUL               TIMESTAMP’ 2010-08-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)

我们发现，现在使用的表空间都为XYSOUL，再次插入两条数据，看一下

SQL> insert into audit_trail(ts,data) values  2  (to_timestamp(‘2010-02-22’,‘yyyy-mm-dd’),‘lss’); 1 row created. SQL> col INVERTAL for a40SQL> col HIGH_VALUE for a35SQL> set lines 999SQL> col tablespace_name for a20SQL> col PARTITION_NAME for a15SQL> select a.partition_name,a.tablespace_name,a.high_value,  2  decode(a.interval,‘YES’,b.interval) invertal  3  from user_tab_partitions a,user_part_tables b  4  where a.table_name=‘AUDIT_TRAIL’  5  and a.table_name=b.table_name  6  order by a.partition_position; PARTITION_NAME  TABLESPACE_NAME      HIGH_VALUE                          INVERTAL--------------- -------------------- ----------------------------------- ----------------------------------------P0              USERS                TIMESTAMP’ 2010-01-01 00:00:00’SYS_P64         USERS                TIMESTAMP’ 2010-03-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)SYS_P61         XYSOUL               TIMESTAMP’ 2010-04-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)SYS_P63         XYSOUL               TIMESTAMP’ 2010-06-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)SYS_P62         XYSOUL               TIMESTAMP’ 2010-08-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)

就像我们看到的，偶数（按月份）分区存放于表空间XYSOUL，奇数分区存放于表空间USERS中。

查看表中数据，并执行回滚操作，再次查看表及分区信息

SQL> select * from audit_trail; TS                                  DATA----------------------------------- -----------22-FEB-10 12.00.00.000000 AM        lss27-MAR-10 12.00.00.000000 AM        xysoul12-MAY-10 12.00.00.000000 AM        lss07-JUL-10 12.00.00.000000 AM        lssSQL> rollback; Rollback complete. SQL> select * from audit_trail; no rows selected SQL> col INVERTAL for a40SQL> col HIGH_VALUE for a35SQL> set lines 999SQL> col tablespace_name for a20SQL> col PARTITION_NAME for a15SQL> select a.partition_name,a.tablespace_name,a.high_value,  2  decode(a.interval,‘YES’,b.interval) invertal  3  from user_tab_partitions a,user_part_tables b  4  where a.table_name=‘AUDIT_TRAIL’  5  and a.table_name=b.table_name  6  order by a.partition_position; PARTITION_NAME  TABLESPACE_NAME      HIGH_VALUE                          INVERTAL--------------- -------------------- ----------------------------------- ----------------------------------------P0              USERS                TIMESTAMP’ 2010-01-01 00:00:00’SYS_P64         USERS                TIMESTAMP’ 2010-03-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)SYS_P61         XYSOUL               TIMESTAMP’ 2010-04-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)SYS_P63         XYSOUL               TIMESTAMP’ 2010-06-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)SYS_P62         XYSOUL               TIMESTAMP’ 2010-08-01 00:00:00’     NUMTOYMINTERVAL(1,‘MONTH’)

看以上信息得知，执行回滚后，表中数据消失，而分区还在。由于这些分区使用一个递归事务（recursive transaction)来创建，递归事务是从正在执行的事务之外单独执行的事务。插入数据行时，数据库发现我们需要的分区尚不存在，就会立即开始一个新的事务，更新数据字典来反映这个新分区的存在，并提交所做工作。它必须这样做，否则存在多个插入会出现严重的竞争（串行化），因为其他事务必须等待我们提交才能看到这个新分区。因此，这个DDL是在现有事务之外完成的，所以分区会持久保留。

顺便提一句，保存AWR信息的几个视图中，也是自动创建分区，包括oracle10g，至今没整明白到底根据什么分区的，一般一个分区表里24个快照信息，详细说明可参考其他博文，如有知道的同志请告知在下，不胜感激。
三、总结
对于数据库还是基础不是太牢固，有些浮躁吧，还不能完全沉下心来静静的读、写东西，我们总是盲目的去做实验，或者呆呆的看文档，希望两者能够结合的更好，不能只纸上谈兵，也不能就当一个武将，加油吧。参考《编程艺术》

                             Xysoul_云龙 2014年2月20日星期四 22:50
                          博客地址：http://blog.sina.com.cn/longzhimeng99