02 December, 2018

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.



2 comments:

Unknown said...

Hi Hemant,
I'm suppose, we should not call Oracle blocks physical. They're actually logical representation of the database level, and we aren't certain about how the data is stored on the physical drive.

Hemant K Chitale said...

Unknown : I take your point. The mapping between an Oracle Data Block and the FileSystem BlockSize or Disk SectorSize is not 1:1 so an Oracle Data Block is a logical overlay over the physical drive.
I've updated the first paragraph.

Featured Post :

An "Awesome" List of Resources

Here's an "Awesome" List of Resources  https://github.com/sindresorhus/awesome . . .  

Preview Popular Posts