Data challenges and solutions

Oracle Table partitioning

This article presents all available methods of partitioning in Oracle databases

 

What is partitioning?

Partitioning allow us to manage big tables, indexes and index-organized tables with greater flexibility, scalability, manageability and performance. In short we can divide these tables into smaller pieces (these pieces are called partitions). Every such partition has its own name and we can independently define different storage characteristics for each of them. Thanks to this, we can manage partitioned objects either by individual partition or as a group.
From the perspective of applications, there is no difference when it comes to accessing or modifying the data. Accessing a partitioned table is the same as accessing a non-partitioned table.

 

Different types of partitioning
  • list partitioning
  • auto-list partitioning
  • range partitioning
  • hash partitioning
  • interval partitioning
  • system partitioning
  • virtual column-based partitioning
  • reference partitioning
  • composite partitioning

List partitioning

We can split tables into partitions on base of the list of values. Additionally we can specify a default partition into which rows that do not map to any other partition will be mapped.

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20),
                            CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                           )
       TABLESPACE tbs_data1 
       PARTITION BY LIST (area) 
       (
        PARTITION england VALUES ('England') TABLESPACE tbs_data2,
        PARTITION ireland VALUES ('Northern Ireland'),
        PARTITION scotland VALUES ('Scotland'),
        PARTITION wales VALUES ('Wales'),
        PARTITION area_null VALUES (NULL),
        PARTITION area_unknown VALUES (DEFAULT)
        );

Auto-list partitioning

An extension of list partitioning. It allows us to automate the creation of list partitions, by creating a new partition as soon as we attempt to add a new piece of data for which is no existing partition. The new partition is created only for distinct new value if the value is not yet included in defined partitions (the table must not have DEFAULT partition defined)

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20)
                           )
       TABLESPACE tbs_data1 
       PARTITION BY LIST (area) AUTOMATIC
       (
        PARTITION england VALUES ('England')
        );

Range partitioning

We can split tables into partitions on based on ranges of data. To prevent failures with inserting data outside of any defined ranges, we add a partition with MAXVALUE, which will catch data that exceed all specified ranges. It is the most popular type of data partitioning used mainly for data related to dates, but it could be related also to numeric or string values.

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20),
                            CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales')) 
                            )
       PARTITION BY RANGE (area) 
       ( PARTITION sale_region_1 VALUES LESS THAN ('S'),
         PARTITION sale_region_2 VALUES LESS THAN (MAXVALUE)
        );

CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20),
                            CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                           )
       TABLESPACE tbs_data1 
       PARTITION BY RANGE (store_id) 
       (
        PARTITION store_1 VALUES LESS THAN (1000) TABLESPACE tbs_data1,
        PARTITION store_2 VALUES LESS THAN (2000) TABLESPACE tbs_data2,
        PARTITION store_3 VALUES LESS THAN (3000),
        PARTITION store_max VALUES LESS THAN (MAXVALUE)
        );

CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20),
                            CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                           )
       PARTITION BY RANGE (sale_date) 
       ( PARTITION sale_2014 VALUES LESS THAN (TO_DATE('2015/01/01','YYYY/MM/DD')),
         PARTITION sale_2015 VALUES LESS THAN (TO_DATE('2016/01/01','YYYY/MM/DD')),
         PARTITION sale_2016 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')),
         PARTITION sale_2017 VALUES LESS THAN (TO_DATE('2018/01/01','YYYY/MM/DD')),
         PARTITION sale_max VALUES LESS THAN (MAXVALUE) );

 

Hash partitioning

When we don’t see some business or logical way to split the data and we want to split them equally, we can use hash partitioning. Thanks to hash partitioning we can partition the data on base of the output of hashing algorithm.

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE
                           )
       PARTITION BY HASH (id) 
       ( PARTITION sale_p1 TABLESPACE tbs_data1,
         PARTITION sale_p2 TABLESPACE tbs_data2,
         PARTITION sale_p3 TABLESPACE tbs_data3,
         PARTITION sale_p4 TABLESPACE tbs_data4
        );

CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE
                           ) 
       PARTITION BY HASH (id) PARTITIONS 4 STORE IN (tbs_data1, tbs_data2, tbs_data3, tbs_data4);

Interval partitioning

An extension of range partitioning. It allows us to automate the creation of range partitions, by creating a new partition as soon as we attempt to add a new piece of data for which is no existing partition. The new partition is created on the basis of pre-defined intervals. Possible intervals: ‘YEAR’, ‘MONTH’, ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’.

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20),
                            CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                           )
       PARTITION BY RANGE (sale_date)
       INTERVAL(NUMTOYMINTERVAL(1,'YEAR')) STORE IN (tbs_data1,tbs_data2,tbs_data3,tbs_data4)
       ( PARTITION sale_2014 VALUES LESS THAN (TO_DATE('2015/01/01','YYYY/MM/DD')),
         PARTITION sale_2015 VALUES LESS THAN (TO_DATE('2016/01/01','YYYY/MM/DD')),
         PARTITION sale_2016 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD'))
        );

CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE
                           ) PARTITION BY RANGE (sale_date)
       INTERVAL(NUMTODSINTERVAL(1,'DAY')) STORE IN (tbs_data1,tbs_data2,tbs_data3,tbs_data4)
       ( PARTITION sale_before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD'))
        );

System partitioning

We can create partitioning which will be controlled by the application. In this type of partitioning we don’t specify partitioning keys. It means that we don’t control partitioning ranges. It is done by the application. The insert statement in the application’s code must specify into which partition the data will be placed.

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE
                           )
       PARTITION BY SYSTEM
       ( PARTITION sale_p1 TABLESPACE tbs_data1,
         PARTITION sale_p2 TABLESPACE tbs_data2,
         PARTITION sale_p3 TABLESPACE tbs_data3,
         PARTITION sale_p4 TABLESPACE tbs_data4
        );
INSERT INTO sales_report partition (sale_p1) (id, product_id, qty, store_id,       sale_date                   ) 
                                       VALUES (1,         10,1000,        1, TO_DATE('2017/10/29','YYYY/MM/DD'));

Virtual column-based partitioning

It is partitioning on basis of the column value, which is not inserted into the table. The value of this virtual column is calculated on the fly by a function or expression. Once we have created a virtual column, then we can start to query it like any other column. We can use all available methods of partitioning on the basis of virtual columns even combined partitioning. The table should have enabled row movement to let the database migrate rows between partitions when value of the virtual column is changed.

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            amnt NUMBER(10,2),
                            store_id INTEGER,
                            sale_date DATE,
                            v_total_amount AS (qty * amnt)
                           )
       PARTITION BY RANGE (sale_date)
       INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
       SUBPARTITION by range (v_total_amount)
       SUBPARTITION template
          ( SUBPARTITION p_small   VALUES LESS THAN (10000),
            SUBPARTITION p_medium  VALUES LESS THAN (50000),
            SUBPARTITION p_large   VALUES LESS THAN (100000),
            SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
           )
       ( PARTITION sale_before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')) )
ENABLE ROW MOVEMENT;

Reference partitioning

This type of partitioning allows us to partition tables based on partitioning on another table. Partitioning is based on parent-child relations between tables that are restricted with primary or foreign key constraints (in the latter case, the foreign key column has to be NOT NULL). Using this type of partitions means that we don’t have to duplicate the key partitioned columns. Since version 12c it is also possible to perform reference partitioning using a defined interval (parameter COMPATIBLE has to be set to at least 12.0.0.0).

Example:
CREATE TABLE order_form ( id INTEGER,
                          order_date DATE,
                          status_id NUMBER(2),
                          order_total NUMBER(10,2),
                          CONSTRAINT order_form_pk PRIMARY KEY (id)
                         )
      PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL (12,'MONTH'))
      ( PARTITION order_form_2016 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')),
        PARTITION order_form_2017 VALUES LESS THAN (TO_DATE('2018/01/01','YYYY/MM/DD')),
        PARTITION order_form_2018 VALUES LESS THAN (TO_DATE('2019/01/01','YYYY/MM/DD'))
       );

CREATE TABLE oi ( id INTEGER,
                  order_form_id INTEGER NOT NULL,
                  oi_name VARCHAR2(255),
                  start_date DATE,
                  end_date DATE,
                  CONSTRAINT oi_fk1 FOREIGN KEY (order_form_id) REFERENCES order_form (id)
                 )
       PARTITION BY REFERENCE (oi_fk1);

Composite partitioning

Composite partitioning allow us to combine different types of basic partitioning. It means that we can partition the table once, and then divide these partitions into sub-partitions using another (potentially different) partitioning method. Examples include:

  • range – list

    The table is first partitioned using range partitioning and every partition will then be subdivided using list partitioning

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20),
                            CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                           )
       PARTITION BY RANGE (sale_date)
       INTERVAL (NUMTOYMINTERVAL (3,'MONTH'))
       SUBPARTITION BY LIST (area)
       SUBPARTITION TEMPLATE
       ( SUBPARTITION sale_england  VALUES ('England'),
         SUBPARTITION sale_ireland  VALUES ('Northern Ireland'),
         SUBPARTITION sale_scotland VALUES ('Scotland'),
         SUBPARTITION sale_wales    VALUES ('Wales')
        )
       ( PARTITION before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')) )
PARALLEL;
  • range – hash

    The table is first partitioned using range partitioning and every partition will then be subdivided using hash partitioning.

Example:
CREATE TABLE sales_report ( id INTEGER,
                            product_id INTEGER,
                            qty INTEGER,
                            store_id INTEGER,
                            sale_date DATE,
                            area VARCHAR2(20),
                            CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                           )
       PARTITION BY RANGE (sale_date)
       INTERVAL (NUMTOYMINTERVAL (12,'MONTH'))
       SUBPARTITION BY HASH (id)
       SUBPARTITION TEMPLATE
       ( SUBPARTITION sale_p1,
         SUBPARTITION sale_p2,
         SUBPARTITION sale_p3,
         SUBPARTITION sale_p4
        )
       ( PARTITION before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')) )
PARALLEL;
  • range – range

    The table is first partitioned using range partitioning and every partition will then be subdivided using range partitioning again.

Example:
CREATE TABLE orders ( id INTEGER,
                      order_date DATE,
                      ship_date DATE
                     )
        PARTITION BY RANGE (order_date)
        SUBPARTITION BY RANGE (ship_date)
        ( PARTITION orders_2016_jan VALUES LESS THAN (TO_DATE('2017/02/01','YYYY/MM/DD'))
          ( SUBPARTITION sp_2016_jan_early VALUES LESS THAN (TO_DATE('2017/01/16','YYYY/MM/DD')),
            SUBPARTITION sp_2016_jan_agreed VALUES LESS THAN (TO_DATE('2017/02/01','YYYY/MM/DD')),
            SUBPARTITION sp_2016_jan_late VALUES LESS THAN (MAXVALUE)
           ),
          PARTITION orders_2016_feb VALUES LESS THAN (TO_DATE('2017/03/01','YYYY/MM/DD'))
          ( SUBPARTITION sp_2016_feb_early VALUES LESS THAN (TO_DATE('2017/02/16','YYYY/MM/DD')),
            SUBPARTITION sp_2016_feb_agreed VALUES LESS THAN (TO_DATE('2017/03/01','YYYY/MM/DD')),
            SUBPARTITION sp_2016_feb_late VALUES LESS THAN (MAXVALUE)
           )
         )
PARALLEL;
  • list – list

    The table is first partitioned using list partitioning and every partition will then be subdivided using list partitioning again.

Example:
CREATE TABLE users ( id INTEGER,
                     username VARCHAR2(255),
                     area VARCHAR2(20),
                     CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                    )
       PARTITION BY LIST (area)
       SUBPARTITION BY LIST (id)
       SUBPARTITION TEMPLATE (
           SUBPARTITION even VALUES (2,4,6,8,10),
           SUBPARTITION odd  VALUES (1,3,5,7,9) ) 
       ( PARTITION p_north VALUES ('Northern Ireland','Scotland'),
         PARTITION p_south VALUES ('England','Wales')
        );

  • list – hash

    The table is first partitioned using list partitioning and every partition will then be subdivided using hash partitioning.

Example:
CREATE TABLE users ( id INTEGER,
                     username VARCHAR2(255),
                     area VARCHAR2(20),
                     CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                    )
       PARTITION BY LIST (area)
       SUBPARTITION BY HASH (id)
       SUBPARTITIONS 4 
       ( PARTITION p_north VALUES ('Northern Ireland','Scotland'),
         PARTITION p_south VALUES ('England','Wales')
        );
  • list – range

    The table is first partitioned using list partitioning and every partition will then be subdivided using range partitioning.

Example:
CREATE TABLE orders ( id INTEGER,
                      order_date DATE,
                      ship_date DATE,
                      area VARCHAR2(20),
                      CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                    )
       PARTITION BY LIST (area)
       SUBPARTITION BY RANGE (ship_date)
       ( PARTITION orders_england VALUES ('England')
         ( SUBPARTITION sp_en_2016_jan_early VALUES LESS THAN (TO_DATE('2017/01/16','YYYY/MM/DD')),
           SUBPARTITION sp_en_2016_jan_agreed VALUES LESS THAN (TO_DATE('2017/02/01','YYYY/MM/DD')),
           SUBPARTITION sp_en_2016_jan_late VALUES LESS THAN (MAXVALUE) ),
         PARTITION orders_ireland VALUES ('Northern Ireland')
         ( SUBPARTITION sp_ie_2016_feb_early VALUES LESS THAN (TO_DATE('2017/02/16','YYYY/MM/DD')),
           SUBPARTITION sp_ie_2016_feb_agreed VALUES LESS THAN (TO_DATE('2017/03/01','YYYY/MM/DD')),
           SUBPARTITION sp_ie_2016_feb_late VALUES LESS THAN (MAXVALUE) ),
         PARTITION orders_scotland VALUES ('Scotland')
         ( SUBPARTITION sp_sc_2016_feb_early VALUES LESS THAN (TO_DATE('2017/02/16','YYYY/MM/DD')),
           SUBPARTITION sp_sc_2016_feb_agreed VALUES LESS THAN (TO_DATE('2017/03/01','YYYY/MM/DD')),
           SUBPARTITION sp_sc_2016_feb_late VALUES LESS THAN (MAXVALUE) ),
         PARTITION orders_wales VALUES ('Wales')
        ( SUBPARTITION sp_wl_2016_feb_early VALUES LESS THAN (TO_DATE('2017/02/16','YYYY/MM/DD')),
          SUBPARTITION sp_wl_2016_feb_agreed VALUES LESS THAN (TO_DATE('2017/03/01','YYYY/MM/DD')),
          SUBPARTITION sp_wl_2016_feb_late VALUES LESS THAN (MAXVALUE) )
       );
  • interval – range

    The table is first partitioned using interval partitioning and every partition will then be subdivided using range partitioning.

Example:
CREATE TABLE orders ( id INTEGER,
                      order_date DATE,
                      ship_date DATE,
                      qty INTEGER
                    )
       PARTITION BY RANGE (ship_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
       SUBPARTITION BY RANGE (qty)
       SUBPARTITION TEMPLATE
       ( SUBPARTITION p_low VALUES LESS THAN (1000),
         SUBPARTITION p_medium VALUES LESS THAN (10000),
         SUBPARTITION p_high VALUES LESS THAN (maxvalue)
        )
       (PARTITION before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')) )
PARALLEL;
  • interval – list

    The table is first partitioned using interval partitioning and every partition will then be subdivided using list partitioning.

Example:
CREATE TABLE orders ( id INTEGER,
                      order_date DATE,
                      ship_date DATE,
                      qty INTEGER,
                      area VARCHAR2(20),
                      CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                    )
       PARTITION BY RANGE (ship_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
       SUBPARTITION BY LIST (area)
       SUBPARTITION TEMPLATE
       ( SUBPARTITION p_north VALUES ('Northern Ireland','Scotland'),
         SUBPARTITION p_south VALUES ('England','Wales')
       )
       ( PARTITION before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')) )
PARALLEL;
  • interval – hash

    The table is first partitioned using interval partitioning and every partition will then be subdivided using hash partitioning.

Example:
CREATE TABLE orders ( id INTEGER,
                      order_date DATE,
                      ship_date DATE
                    )
       PARTITION BY RANGE (ship_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
       SUBPARTITION BY HASH (id)
       SUBPARTITIONS 4
       ( SUBPARTITION p_north VALUES ('Northern Ireland','Scotland'),
         SUBPARTITION p_south VALUES ('England','Wales')
       )
       ( PARTITION before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')) )
PARALLEL;

 

  • Hash-Hash

    The table is first partitioned using hash partitioning and every partition will then be subdivided using hash partitioning again.

Example:
CREATE TABLE users ( id INTEGER,
                     username VARCHAR2(255),
                     area VARCHAR2(20),
                     CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                    )
       PARTITION BY HASH (id)
       SUBPARTITION BY HASH (area)
       SUBPARTITIONS 4 PARTITIONS 8;

 

  • Hash-List

    The table is first partitioned using hash partitioning and every partition will then be subdivided using list partitioning.

  • Example:
    CREATE TABLE users ( id INTEGER,
                         username VARCHAR2(255),
                         area VARCHAR2(20),
                         CONSTRAINT sales_report_ck CHECK (area in ('England','Northern Ireland','Scotland','Wales'))
                        )
           PARTITION BY HASH (id)
           SUBPARTITION BY LIST (area)
           SUBPARTITION TEMPLATE
           ( SUBPARTITION p_north VALUES ('Northern Ireland','Scotland'),
             SUBPARTITION p_south VALUES ('England','Wales')
           )
           PARTITIONS 8;

     

  • Hash-Range

    The table is first partitioned using hash partitioning and every partition will then be subdivided using range partitioning.

  • Example:
    CREATE TABLE orders ( id INTEGER,
                          order_date DATE,
                          ship_date DATE
                        )
           PARTITION BY HASH (id)
           SUBPARTITION BY RANGE (ship_date) 
           SUBPARTITION TEMPLATE
           (
             SUBPARTITION sp_before_2017 VALUES LESS THAN (TO_DATE('2017/01/01','YYYY/MM/DD')),
             SUBPARTITION sp_2017 VALUES LESS THAN (TO_DATE('2018/01/01','YYYY/MM/DD')),
             SUBPARTITION sp_after_2017 VALUES LESS THAN (MAXVALUE)
           ) PARTITIONS 4 ;

     

    Leave a Reply to Anonymous Cancel reply