oracle按group_id和子分区每月分区 [英] oracle partition by group_id and subpartition monthly

问题描述

我想创建一个这样的表.

I want to create a table like this.

create table some_data ( 
  id number(19,0),
  group_id number(19,0),
  value float,
  timestamp timestamp
);

对于此表，我希望将数据存储为

For this table i would like to have the data stored like

 group_id=1
    jan-2015
    feb-2015
    ...
group_id=2
    jan-2015
    feb-2015
    ...    

等等.所以我假设我必须为 group_id 创建一个按范围划分的分区，然后还要按范围创建一个带有时间戳列的子分区，对吗?

and so on. So I assume i have to create a partition by range for the group_id and then a subpartition also by range with the timestamp column, right?

所以它应该是这样的:

create table some_data ( 
  id number(19,0),
  group_id number(19,0),
  value float,
  timestamp timestamp
)
PARTITION BY RANGE (group_id)
SUBPARTITION BY RANGE ("TIMESTAMP") 
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
 PARTITION part_1 values LESS THAN (TO_DATE('01.02.2015','DD.MM.YYYY'))
 );

这样对吗?还有一个问题:有了这个分区，如果添加了一个新的group_id，oracle会自动为新的group_id创建一个新的分区，并为新月的新数据创建新的suppartitions?

Is this right? And also the question: With this partition, if a new group_id is added, will oracle create automatically a new partition for the new group_id and the new suppartitions for new data with new months?

推荐答案

子分区级别不支持间隔分区:

Interval partitioning is not supported on subpartition level:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF54559

你可以这样定义:

create table some_data ( 
  id number(19,0),
  group_id number(19,0),
  value float,
  timestamp timestamp  -- not good naming
)
PARTITION BY RANGE ("TIMESTAMP")
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE (group_id) -- it could be hash or list as well
   subpartition template(
     ...
   )
(
 PARTITION part_1 values LESS THAN (TO_DATE('01.02.2015','DD.MM.YYYY'))
);

这篇关于oracle按group_id和子分区每月分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！