How to store range/hash composite partitions in separate datafiles by range?

Question

I'm creating a database which will utilize composite partitioning. I will partition one table using range partitioning (by date) and then further subpartition it by hash (by client id). So far so good, no problem, but I also need to have those partitions stored in separate data files each dbf holding data for a single month. I'm reading on composite partitions and what I found is that primary range partitioning will be only a logical one and data will be stored in subpartitions instead which seems to make my goal impossible. Am I right and should look for a different solution?

Thanks in advance.

My databases are Oracle 11g and Oracle 12

Are you sure you need to manage data files? If you're using ASM it will take care of that for you. — Jon Heller, Sep 13 '16 at 01:43

score 1 · Answer 1 · answered Sep 11 '16 at 17:09

On existing table you can move partitions or subpartitions to a different tablespace, i.e. different datafile, examples:

ALTER TABLE scuba_gear MOVE SUBPARTITION bcd_types TABLESPACE tbs23; 
ALTER TABLE parts MOVE PARTITION depot2 TABLESPACE ts094;

see Moving Subpartitions and Moving Table Partitions

For new tables typically you would be create them like this:

CREATE TABLE sales
  ( 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,'MONTH'))
STORE IN (ts_1, ts_2, ts_3, ts_4, ts_5, ts_6 ,ts_7 ,ts_8, ts_9, ts_10, ts_11, ts_12)
 SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4
 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')));

Oracle then will put the monthly partitions by "round-robin" method to these 12 tablespaces. STORE IN clause is also possible for subpartitions, see Creating a composite range-hash partitioned table

How to store range/hash composite partitions in separate datafiles by range?

1 Answers1