Oracle multicolumn partitioning vs using a subpartition

Question

Apart from the obvious, can anyone explain the what is different between multicolumn partitioning and using a subpartition? And which one is better for a OLTP scenario? For details, see Managing Partitioned Tables and Indexes in the Oracle Database Administrator's Guide.

A (dumb) example of a table partitioned on multiple columns is:

CREATE TABLE demo1
(
   year          NUMBER, 
   month         NUMBER,
   day           NUMBER,
   instance      NUMBER, /* assuming this can only be 1 or 2 */
   other1        VARCHAR2(50),
   other2        VARCHAR2(50),
   other3        VARCHAR2(50)
) 
PARTITION BY RANGE (year,instance) 
(
   PARTITION data_2009_inst1 VALUES less than (2009,2) TABLESPACE data_2009,
   PARTITION data_2009_inst2 VALUES less than (2009,3) TABLESPACE data_2009,
   PARTITION data_2010_inst1 VALUES less than (2010,2) TABLESPACE data_2010,
   PARTITION data_2010_inst2 VALUES less than (2010,3) TABLESPACE data_2010,
   PARTITION data_2011_inst1 VALUES less than (2011,2) TABLESPACE data_2011,
   PARTITION data_2011_inst2 VALUES less than (2011,3) TABLESPACE data_2011
);

Similarly, example of a subpartitioned table is:

CREATE TABLE demo2
(
   year          NUMBER, 
   month         NUMBER,
   day           NUMBER,
   instance      NUMBER, /* assuming this can only be 1 or 2 */
   other1        VARCHAR2(50),
   other2        VARCHAR2(50),
   other3        VARCHAR2(50)
) 
PARTITION BY RANGE (year) 
SUBPARTITION BY LIST (instance) /* Cannot subpartition by range in 10gR2 */
   SUBPARTITION template 
   (
      SUBPARTITION i1 VALUES (1),
      SUBPARTITION i2 VALUES (2),
      SUBPARTITION ix VALUES (DEFAULT)
   )
(
   PARTITION data_2009 VALUES less than (2010) TABLESPACE data_2009,
   PARTITION data_2010 VALUES less than (2011) TABLESPACE data_2010,
   PARTITION data_2011 VALUES less than (2012) TABLESPACE data_2011
);

Now what is the difference between these tables? Are they not "logically" the same? I know its far easier to add partitions to demo2 as you need to split partitions on demo1 to get more partitions as time passes by. Which on is better in an OLTP scenario?

On a side note, the reason I am partitioning on the INSTANCE number has to do with Oracle RAC. I am trying to create an "instance affinity" to stop "hot block" from slowing down the database as these need be sent across the interconnect between the RAC nodes. (We have empirically proved that this does make a difference in our testing).

Solution

There probably isn't any difference in your case, but in general sub-partitioning allows you to partition in 2 different ways, such as range-hash, range-list. Your sub-partition example is range-list, but equivalent to the single-level range partitioning. However, you could not use a single-level if your sub-partitioning was like this example from the doc you linked:

ALTER TABLE quarterly_regional_sales 
   ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
         (
          SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
         );