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.

Partitioning -- 10 : Virtual Column Based Partitioning

Oracle 11g supports specifying a Virtual Column as the Partition Key.

A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions.  The actual value is not stored in the block holding the row but is computed when the row is retrieved.

For example :

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_id, invoice_date, sale_year
 from my_sales_table
/

INVOICE_ID  INVOICE_DATE  SALE_YEAR
         1  28-NOV-18          2018

The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.

drop table my_sales_table;

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
 partition by list(sale_year)
 (partition p_2018 values (2018),
  partition p_2019 values (2019),
  partition p_2020 values (2020)
  )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_date, sale_year from my_sales_table partition (p_2018)
/

INVOICE_DATE  SALE_YEAR
28-NOV-18          2018

Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.

SQL Slowdown ? A short list of potential reasons

Jonathan Lewis has published a short list of potential reasons why you might see a slowdown in SQL execution.  With newer releases 12.2, 18c and 19c, the list may have to be expanded.