Oracle 11g推出了interval间隔分区，以往的分区是需要手工或半自动化脚本实现分区扩展，但这种间隔分区的出现，将分区扩展的工作彻底解放出来，这里不讨论何为间隔分区，主要说一下创建间隔分区有一个STORE IN参数，官方文旦对其的介绍是：
The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.
STORE IN参数可以明确间隔分区使用的一个或多个表空间，他使用的是循环算法来创建间隔分区。
接下来，分别有三种方法来指定间隔分区的表空间，我们看下各自的不同。
方法1：设置store in，未设置预定义分区表空间。
CREATE TABLE interval_sales1
( prod_id NUMBER(6)
, cust_id NUMBER , time_id DATE , channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) store in (SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')) ); insert into interval_sales1 values(908001,101,to_date('2002-8-10','yyyy-mm-dd'),'a',88001,100,200); insert into interval_sales1 values(908002,102,to_date('2003-7-10','yyyy-mm-dd'),'a',88002,100,800); insert into interval_sales1 values(908003,103,to_date('2004-5-30','yyyy-mm-dd'),'a',88003,100,700); insert into interval_sales1 values(908004,104,to_date('2005-12-10','yyyy-mm-dd'),'a',88004,100,600); insert into interval_sales1 values(908005,105,to_date('2007-11-14','yyyy-mm-dd'),'a',88005,100,500); commit;
方法2：未设置store in,
设置预定义分区表空间。
CREATE TABLE interval_sales2
( prod_id NUMBER(6)
, cust_id NUMBER , time_id DATE , channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')) tablespace SALES_TBS1,
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')) tablespace SALES_TBS2,
PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')) tablespace SALES_TBS3,
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')) tablespace SALES_TBS4); insert into interval_sales2 values(908001,101,to_date('2002-8-10','yyyy-mm-dd'),'a',88001,100,200); insert into interval_sales2 values(908002,102,to_date('2003-7-10','yyyy-mm-dd'),'a',88002,100,800); insert into interval_sales2 values(908003,103,to_date('2004-5-30','yyyy-mm-dd'),'a',88003,100,700); insert into interval_sales2 values(908004,104,to_date('2005-12-10','yyyy-mm-dd'),'a',88004,100,600); insert into interval_sales2 values(908005,105,to_date('2007-11-14','yyyy-mm-dd'),'a',88005,100,500); commit;
方法3：设置store in, 设置预定义分区表空间。
CREATE TABLE interval_sales3
( prod_id NUMBER(6)
, cust_id NUMBER , time_id DATE , channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) store in (SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')) tablespace SALES_TBS1,
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')) tablespace SALES_TBS2,
PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')) tablespace SALES_TBS3,
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')) tablespace SALES_TBS4); insert into interval_sales3 values(908001,101,to_date('2002-8-10','yyyy-mm-dd'),'a',88001,100,200); insert into interval_sales3 values(908002,102,to_date('2003-7-10','yyyy-mm-dd'),'a',88002,100,800); insert into interval_sales3 values(908003,103,to_date('2004-5-30','yyyy-mm-dd'),'a',88003,100,700); insert into interval_sales3 values(908004,104,to_date('2005-12-10','yyyy-mm-dd'),'a',88004,100,600); insert into interval_sales3 values(908005,105,to_date('2007-11-14','yyyy-mm-dd'),'a',88005,100,500); commit;
我们看见三种方法对STORE IN和预定义分区进行了穷举，我们看看不同方法对于间隔分区表空间的使用有何区别。