oracle间隔分区表空间,时间间隔分区，及其默认表空间的几个使用场景

最新推荐文章于 2025-02-03 15:21:27 发布

转载最新推荐文章于 2025-02-03 15:21:27 发布 · 628 阅读

文章标签：

本文介绍了Oracle 11g中的时间间隔分区表特性，详细展示了如何通过INTERVAL关键字创建自动分区，并讨论了分区键的限制，如只能为NUMBER或DATE类型。同时，通过三个场景探讨了预定义分区表空间和STOREIN子句的使用，说明了不同配置下数据如何分配到不同表空间。实验结果显示，预定义分区的数据会根据设定进入指定表空间，而后续分区则遵循STOREIN指定的顺序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这次上线，会将一张普通表，变为分区表，准确地说，是时间间隔分区，这是11g的新特性，可以避免人工创建分区，当有新数据进来，他会创建新分区，以及相应的local分区索引，语法如下，原始语法基础上，增加关键字INTERVAL，里面可以用一些函数，例如NUMTOYMINTERVAL(1, 'MONTH')，指定使用一个月的间隔，

CREATE TABLE tbl_interval0

( id NUMBER(6),

time_id DATE

)

PARTITION BY RANGE (time_id)

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

(PARTITION p0 VALUES LESS THAN (TO_DATE('2017-01-01', 'YYYY-MM-DD')));

这是一些interval分区表的要求，例如分区键类型只可以选择NUMBER，或者DATE，但允许预定义一些分区，并指定不同的表空间，不支持参考分区，

using a date where the high or low bound of the partition would be out of the range set for storage causes an error.

the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE type. 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.

You cannot use interval partitioning with reference partitioned tables.

Serializable transactions do not work with interval partitioning. Inserting data into a partition of an interval partitioned table that does not have a segment yet causes an error.

我们看下，若指定了不同的表空间，新建分区，如何使用这些表空间，如下是几个场景，

场景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;

查看预定义分区和扩展分区的表空间分配情况。

select table_name, partition_name, tablespace_name, high_value

from user_tab_partitions where table_name like 'INTERVAL%';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME HIGH_VALUE

----------------- --------------- ---------------- --------------------------------------------------

INTERVAL_SALES1 P0 USERS TO_DATE(' 2003-01-01 00:00:00'...)

INTERVAL_SALES1 P1 USERS TO_DATE(' 2004-01-01 00:00:00'...)

INTERVAL_SALES1 P2 USERS TO_DATE(' 2005-01-01 00:00:00'...)

INTERVAL_SALES1 P3 USERS TO_DATE(' 2006-01-01 00:00:00'...)

INTERVAL_SALES1 SYS_P64 SALES_TBS2 TO_DATE(' 2008-01-01 00:00:00'...)

INTERVAL_SALES2 P0 SALES_TBS1 TO_DATE(' 2003-01-01 00:00:00'...)

INTERVAL_SALES2 P1 SALES_TBS2 TO_DATE(' 2004-01-01 00:00:00'...)

INTERVAL_SALES2 P2 SALES_TBS3 TO_DATE(' 2005-01-01 00:00:00'...)

INTERVAL_SALES2 P3 SALES_TBS4 TO_DATE(' 2006-01-01 00:00:00'...)

INTERVAL_SALES2 SYS_P65 USERS TO_DATE(' 2008-01-01 00:00:00'...)

INTERVAL_SALES3 P0 SALES_TBS1 TO_DATE(' 2003-01-01 00:00:00'...)

INTERVAL_SALES3 P1 SALES_TBS2 TO_DATE(' 2004-01-01 00:00:00'...)

INTERVAL_SALES3 P2 SALES_TBS3 TO_DATE(' 2005-01-01 00:00:00'...)

INTERVAL_SALES3 P3 SALES_TBS4 TO_DATE(' 2006-01-01 00:00:00'...)

INTERVAL_SALES3 SYS_P66 SALES_TBS2 TO_DATE(' 2008-01-01 00:00:00'...)

15 rows selected.

从实验中，可以得出如下的结论，

场景1. 设置store in，未设置预定义分区表空间。

属于预定义分区的数据，由于未指定默认表空间，因此进入用户默认表空间(USERS)，其他分区数据，会参考(SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)的顺序，依次按序进入分区中。

场景2. 未设置store in, 设置预定义分区表空间。

属于预定义分区的数据，会进入指定的表空间，例如2003、2004、2005、2006年，其他分区数据会进入，此用户默认的表空间USERS。

场景3. 设置store in, 设置预定义分区表空间。

其实是场景1和2的结合，属于预定义分区的数据，会进入指定的表空间，其他分区数据，会参考(SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)的顺序，依次按序进入分区中。

当然，如果比较粗放的，无论是预定义，还是非预定义，可以不指定默认的表空间，此时使用的就是此用户，默认的表空间。

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，

oracle间隔分区 表空间,时间间隔分区，及其默认表空间的几个使用场景

oracle间隔分区表空间,时间间隔分区，及其默认表空间的几个使用场景