Partitioning -- 11 : Composite Partitioning

Oracle allows Composite Partitioning where a Partition can, itself, be Sub-Partitioned.  Each SubPartition is a distinct segment (allocation of physical Oracle Data blocks) while the Partition itself remains a logical definition without a segment.

Composite Partitioning can comprise of :

• Range-Hash  
• Range-List  
• Range-Range
• List-Range
• List-Hash
• List-List
• Interval-Hash
• Interval-List
• Interval-Range

Here is one example of Range-List Partitioning :

SQL> drop table my_sales_table; Table dropped. SQL>

SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> 
SQL> l
  1  create table my_sales_table
  2  (invoice_id  number(16) primary key,
  3   invoice_date date,
  4   region_code varchar2(5),
  5   invoice_amount number)
  6  partition by range (invoice_date)
  7  subpartition by list (region_code)
  8  subpartition template
  9  (
 10   subpartition US values ('US') tablespace tbs_US,
 11   subpartition EMEA values ('EMEA') tablespace tbs_EMEA,
 12   subpartition ASIA values ('ASIA') tablespace tbs_ASIA,
 13   subpartition OTHERS values (DEFAULT) tablespace tbs_OTHERS)
 14  (
 15   partition p_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
 16   partition p_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
 17* )
SQL> /

Table created.

SQL> 
SQL> select table_name, partition_name, subpartition_name
  2  from user_tab_subpartitions
  3  where table_name = 'MY_SALES_TABLE'
  4  /

TABLE_NAME                     PARTITION_NAME  SUBPARTITION_NAME
------------------------------ --------------- ------------------------------
MY_SALES_TABLE                 P_2018          P_2018_US
MY_SALES_TABLE                 P_2018          P_2018_EMEA
MY_SALES_TABLE                 P_2018          P_2018_ASIA
MY_SALES_TABLE                 P_2018          P_2018_OTHERS
MY_SALES_TABLE                 P_2019          P_2019_US
MY_SALES_TABLE                 P_2019          P_2019_EMEA
MY_SALES_TABLE                 P_2019          P_2019_ASIA
MY_SALES_TABLE                 P_2019          P_2019_OTHERS

8 rows selected.

SQL> 
SQL> l
  1  select segment_name, segment_type, partition_name, tablespace_name
  2  from user_segments
  3  where segment_name = 'MY_SALES_TABLE'
  4* order by 1,2,3
SQL> /

SEGMENT_NAME    SEGMENT_TYPE       PARTITION_NAME  TABLESPACE_N
--------------- ------------------ --------------- ------------
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_ASIA     TBS_ASIA
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_EMEA     TBS_EMEA
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_OTHERS   TBS_OTHERS
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_US       TBS_US
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_ASIA     TBS_ASIA
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_EMEA     TBS_EMEA
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_OTHERS   TBS_OTHERS
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_US       TBS_US

8 rows selected.

SQL> 

Note how the actual SubPartition Names are auto-created by Oracle using the composite of the Partition Name  (P_2018, P_2019) and the SubPartition Name (from the SubPartition Template).

In this case, the names that are SubPartition in USER_TAB_SUBPARTITIONS appear as PARTITION_NAME in USER_SEGMENTS because each of the two logical Partitions (P_2018, P_2019) don't actually have their own Segments.


Note :  I set "deferred_segment_creation" to FALSE so that all the Segments would be created upfront even if they are not populated. "deferred_segment_creation" is an 11g feature.