2

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
    ...

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?

So it should look like this:

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'))
 );

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?

Fulley
  • 73
  • 6
  • Why do you care how the data is stored? – PlanItMichael Feb 17 '15 at 15:21
  • @PlanItMichael well, stored might be the wrong wording here, i want to create the partitions in a way to get my query results faster and i think to get faster query results i need to create the partitions as i described above – Fulley Feb 17 '15 at 15:29
  • Not necessarily. If the data you want to query on is of low cardinality (i.e., across many rows only a few different values exist) you can use bitmap indexes. They are lightning fast and work well when querying against multiple columns. However, they aren't good for tables that are frequently updated. – PlanItMichael Feb 17 '15 at 15:42
  • well, this table has a lot of rows (200.000.000+) and every day we have like 2million new inserts, and i don't know how many rows we delete per day – Fulley Feb 17 '15 at 15:48

1 Answers1

2

Interval partitioning is not supported on subpartition level:

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

You can define it like this:

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'))
);
Rusty
  • 1,988
  • 10
  • 12
  • yes! this one works, thanks. and for the timestamp: it was just an example, the real amount of the columns and their name is of course not the same as here. :) – Fulley Feb 17 '15 at 16:47