理论知识
1. 概念
间隔分区（interval partitioning）是oracle database 11g Release 1及以上版本中的新增特性，它就是以一个区间分区表为“起点”，并在定义中增加了一个规则（即间隔），使数据库知道将来如何增加分区。间隔分区是范围分区的一个拓展，当插入的数据超过了现有的所有分区时，数据库会按照指定的间隔自动创建分区。Oracle支持创建单一的间隔分区表也可以创建interval-range、interval-hash和interval-list三种组合分区表。
2. 使用场景
使用间隔分区最大的好处就是不用再提前手工的创建分区，只要后续创建的分区的分区间隔是统一的，就可以考虑使用间隔分区。并且，在后续创建的分区中还能通过store in 选项以循环复用的方式来将分区存放到不同的表空间里。
如果某张表是范围分区，则很容易的将该表转换成间隔分区表。
如果某张表为间隔分区表，则无法手工的对该表手动新增分区。所以如果决定将某张表修改为间隔分区，要考虑修改相关的应用程序或者存储过程。
3. 使用限制
只能指定一个分区键，并且键值类型只能为number或date。
间隔分区不支持索引组织表。
不能在间隔分区上创建域索引（domain index）
课堂实验
创建一个用户，并创建间隔分区
SQL> create tablespace tablespacea datafile '/Oradatafile/DBData/A01.dbf' size 10m; Tablespace created. SQL> create user usera identified by usera default tablespace tablespacea; User created. SQL> grant dba to usera; Grant succeeded SQL> conn usera/usera Connected. SQL> create table audit_trail (ts timestamp,data varchar2(30)) partition by range(ts) interval (numtoyminterval(1,'month')) store in (users,tablespacea) (partition p0 values less than (to_date('2010-01-01','yyyy-mm-dd'))); Table created. |
注意：如果按月份间隔，不能写某个月28日以后的某一天，由于2月没有31日。如果填写，报错如下：
SQL> create table audit_trail1 (ts timestamp,data varchar2(30)) partition by range(ts) interval (numtoyminterval(1,'month')) store in (users,TABLESPACEA) (partition p0 values less than (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, decode(a.interval,'YES',b.interval) invertal from user_tab_partitions a,user_part_tables b where a.table_name='AUDIT_TRAIL' and a.table_name=b.table_name order by a.partition_position; ------------------ -------------------- ----------------------------------- ---------- P0 USERS TIMESTAMP' 2010-01-01 00:00:00' |
插入一条数据，并查看，发现多了一个分区
SQL> insert into audit_trail(ts,data) values (to_timestamp('2010-03-27','yyyy-mm-dd'),'aaaa'); SQL> col INVERTAL for a40 SQL> col HIGH_VALUE for a35 SQL> set lines 999 SQL> col tablespace_name for a20 SQL> col PARTITION_NAME for a15 SQL> select a.partition_name,a.tablespace_name,a.high_value, decode(a.interval,'YES',b.interval) invertal from user_tab_partitions a,user_part_tables b where a.table_name='AUDIT_TRAIL' and a.table_name=b.table_name order by a.partition_position; PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INVERTAL --------------- -------------------- ----------------------------------- ---------------------------------------- P0 USERS TIMESTAMP' 2010-01-01 00:00:00' SYS_P61 TABLESPACEA TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') |
再次插入一条数据，并查看
SQL> insert into audit_trail(ts,data) values (to_timestamp('2010-07-7','yyyy-mm-dd'),'lss'); 1 row created. SQL> SQL> col INVERTAL for a40 SQL> col HIGH_VALUE for a35 SQL> set lines 999 SQL> col tablespace_name for a20 SQL> col PARTITION_NAME for a15 SQL> select a.partition_name,a.tablespace_name,a.high_value, decode(a.interval,'YES',b.interval) invertal from user_tab_partitions a,user_part_tables b where a.table_name='AUDIT_TRAIL' and a.table_name=b.table_name order by a.partition_position; PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INVERTAL --------------- -------------------- ----------------------------------- ---------------------------------------- P0 USERS TIMESTAMP' 2010-01-01 00:00:00' SYS_P61 TABLESPACEA TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') SYS_P62 TABLESPACEA TIMESTAMP' 2010-08-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') |
我们发现，现在使用的表空间都为TABLESPACEA，再次插入两条数据，看一下
SQL> insert into audit_trail(ts,data) values (to_timestamp('2010-02-22','yyyy-mm-dd'),'lss'); 1 row created. SQL> col INVERTAL for a40 SQL> col HIGH_VALUE for a35 SQL> set lines 999 SQL> col tablespace_name for a20 SQL> col PARTITION_NAME for a15 SQL> select a.partition_name,a.tablespace_name,a.high_value, decode(a.interval,'YES',b.interval) invertal from user_tab_partitions a,user_part_tables b where a.table_name='AUDIT_TRAIL' and a.table_name=b.table_name 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 TABLESPACEA TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') SYS_P63 TABLESPACEA TIMESTAMP' 2010-06-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') SYS_P62 TABLESPACEA TIMESTAMP' 2010-08-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') |
就像我们看到的，偶数（按月份）分区存放于表空间TABLESPACEA，奇数分区存放于表空间USERS中。
查看表中数据，并执行回滚操作，再次查看表及分区信息
SQL> select * from audit_trail; TS DATA ----------------------------------- ----------- 22-FEB-10 12.00.00.000000 AM lss 27-MAR-10 12.00.00.000000 AM xysoul 12-MAY-10 12.00.00.000000 AM lss 07-JUL-10 12.00.00.000000 AM lss SQL> rollback; Rollback complete. SQL> select * from audit_trail; no rows selected SQL> col INVERTAL for a40 SQL> col HIGH_VALUE for a35 SQL> set lines 999 SQL> col tablespace_name for a20 SQL> col PARTITION_NAME for a15 SQL> select a.partition_name,a.tablespace_name,a.high_value, decode(a.interval,'YES',b.interval) invertal from user_tab_partitions a,user_part_tables b where a.table_name='AUDIT_TRAIL' and a.table_name=b.table_name 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 TABLESPACEA TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') SYS_P63 TABLESPACEA TIMESTAMP' 2010-06-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') SYS_P62 TABLESPACEA TIMESTAMP' 2010-08-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH') |
看以上信息得知，执行回滚后，表中数据消失，而分区还在。由于这些分区使用一个递归事务（recursivetransaction)来创建，递归事务是从正在执行的事务之外单独执行的事务。插入数据行时，数据库发现我们需要的分区尚不存在，就会立即开始一个新的事务，更新数据字典来反映这个新分区的存在，并提交所做工作。它必须这样做，否则存在多个插入会出现严重的竞争（串行化），因为其他事务必须等待我们提交才能看到这个新分区。因此，这个DDL是在现有事务之外完成的，所以分区会持久保留。
扫描二维码关注我的微学堂
搜索刘老师微信号：Rman-2014，备注“Oracle学习与咨询”，即可添加好友；或者扫描下面二维码，关注我的“微学堂”公众号，了解最新OCP认证动态、题库及答案解析、培训机构及讲师介绍、课堂授课内容等。每天还有一篇技术文章发布哦!
