Partitioning in Oracle

As table size grows due to more data, the performance of insert, update, delete and select SQL operations, as well as maintenance operations tasks such as taking backups, index maintenance and update statistics may take longer to perform. In order to improve the performance of these operations it is therefore advisable to partition tables greater than 2GB in separate chunks…. Continue Reading →

As table size grows due to more data, the performance of insert, update, delete and select SQL operations, as well as maintenance operations tasks such as taking backups, index maintenance and update statistics may take longer to perform. In order to improve the performance of these operations it is therefore advisable to partition tables greater than 2GB in separate chunks. The data and indexes using certain criteria are partitioned into separate file groups in a database.

Types of Partitioning

The selection criterion used is in the form of a partition key, which helps in dividing data in a large table into distinct sets of smaller chunks. Below are the commonly-used partitions that are supported by Oracle:

  1. List Partitioning: – This type of partitioning separates the total set of values in the table into a distinct set of values which each partition can have. For example, the sales partitions can be a list of regions such as North, East, and South West with each region being in a separate table as shown in the example below:
    1. Let’s first create the table with list partition as shown in the sample DDL below:
    2. CREATE TABLE SALES
         (
          SALES_NO NUMBER, 
          DEPT_NAME VARCHAR2 (20),
          SALE_AMOUNT NUMBER (10, 2),
          SALE_DATE DATE,
          REGION VARCHAR2 (10)
         )
         PARTITION BY LIST (REGION)
            (
             PARTITION P1 VALUES ('NORTH'),
             PARTITION P2 VALUES ('SOUTH'),
             PARTITION P3 VALUES ('EAST'),
             PARTITION P4 VALUES ('WEST')
           );
    3. Let’s insert the data in the table:
    4. Insert into SALES values (1,'SALES', 2000.25,'01-OCT-16','NORTH');
      Insert into SALES values (2,'SALES', 6000.25,'01-OCT-16','SOUTH');
      Insert into SALES values (3,'SALES', 8000.25,'01-OCT-16','EAST');
      Insert into SALES values (4,'SALES', 12000.25,'01-OCT-16','WEST');
      COMMIT;
    5. Run the sample query which will give data in partitions P1 and P2:
    6. SELECT * FROM SALES PARTITION (P1);
      SELECT * FROM SALES PARTITION (P2);
  2. Range Partitioning: – is based on a range of values, for example dates from less than 01-JAN-2014 can be in one partition, with dates from 2015 onwards in another partition, as shown below.
    1. Below is a sample DDL for our Range Partition table:
    2. CREATE TABLE SALE_ORDER
      (
      ORDER_NO NUMBER(10),
      ORDER_TYPE VARCHAR2(5),
      ORDER_DATE DATE,
      FREIGHT_DATE DATE,
      FREIGHT_AMOUNT NUMBER (10, 2),
      ORDER_AMOUNT NUMBER (10, 2)
      ) 
      PARTITION BY RANGE (ORDER_DATE) 
      (Partition P1 values less than (TO_DATE ('01-JAN-2014','dd-MON-yyyy')), 
      Partition P2 values less than (TO_DATE ('01-JAN-2015','dd-MON-yyyy')), 
      Partition P3 values less than (TO_DATE ('01-JAN-2016','dd-MON-yyyy')), 
      Partition P4 values less than (TO_DATE ('01-JAN-2017','dd-MON-yyyy'))
      );
    3. Below is the script to insert data into the table:
    4. INSERT INTO SALE_ORDER VALUES (1,'SALE','01-JAN-2013','01-JAN-2013', 20,200);
      INSERT INTO SALE_ORDER VALUES (1,'SALE','01-JAN-2015','01-JAN-2015', 60,600);
      INSERT INTO SALE_ORDER VALUES (1,'SALE','01-JAN-2016','01-JAN-2016', 80,800);
      INSERT INTO SALE_ORDER VALUES (1,'SALE','01-JAN-2017','01-JAN-2017', 90,900);
    5. Below is the select script to see the data in the table:
    6. SELECT * FROM SALE_ORDER PARTITION (P1);
      SELECT * FROM SALE_ORDER PARTITION (P2);
  3. Hash Partitioning: – Oracle uses the hash algorithm to evenly distribute the data among the new partitions. It’s mostly useful to evenly distribute the data among the nodes of Oracle Real Application Cluster (RAC).
    1. Below is a sample DDL for a Hash Partition table:
    2. CREATE TABLE PURCHASE_ORDER
      (
      PURCHASE_ORDER_NO NUMBER (10),
      PURCHASE_ORDER_TYPE VARCHAR2 (5),
      PURCHASE_ORDER_DATE DATE,
      FREIGHT_DATE DATE,
      FREIGHT_AMOUNT NUMBER (10, 2),
      PURCHASE_ORDER_AMOUNT NUMBER (10, 2)
      ) 
      PARTITION BY HASH (PURCHASE_ORDER_NO)
      PARTITIONS 4;
    3. Below is the script used to insert data into the table:
    4. INSERT INTO PURCHASE_ORDER VALUES (1,'PO','01-JAN-2014','01-JAN-2014', 10,100);
      INSERT INTO PURCHASE_ORDER VALUES (2,'PO','01-JAN-2015','01-JAN-2015', 20,200);
      INSERT INTO PURCHASE_ORDER VALUES (3,'PO','01-JAN-2016','01-JAN-2016', 50,500);
      INSERT INTO PURCHASE_ORDER VALUES (4,'PO','01-JAN-2017','01-JAN-2017', 60,600);
      COMMIT;
    5. Below is the select script to see the data in the table:
    6. SELECT * FROM PURCHASE_ORDER PARTITION (SYS_P1360);
      SELECT * FROM PURCHASE_ORDER PARTITION (SYS_P1361);

      The partition name created in database can be found using this script:

      SELECT partition_name, tablespace_ name, high_value
      FROM user_tab_partitions;
  4. Interval Partitioning: – Introduced in Oracle 11g, interval partitioning is an extension of range partitioning. In this, Oracle automatically creates a partition based on the partition key when a new dataset comes in.
    1. Below is the sample script for Interval Partition table:
    2. CREATE TABLE RECEIPT_ORDER
          (
          RECEIPT_NO NUMBER, 
          DEPT_NAME VARCHAR2 (20),
          RECEIPT_AMOUNT NUMBER (10, 2),
          RECEIPT_DATE DATE,
          REGION VARCHAR2 (10)
          ) 
         PARTITION BY RANGE (RECEIPT_DATE) 
          INTERVAL (NUMTOYMINTERVAL (1, 'MONTH'))
          (PARTITION p1 VALUES LESS THAN (TO_DATE ('1-1-2014', 'DD-MM-YYYY')),
            PARTITION p2 VALUES LESS THAN (TO_DATE ('1-1-2015', 'DD-MM-YYYY')),
            PARTITION p3 VALUES LESS THAN (TO_DATE ('1-7-2016', 'DD-MM-YYYY')),
            PARTITION p4 VALUES LESS THAN (TO_DATE ('1-1-2017', 'DD-MM-YYYY')));
      
    3. Below is the insert script for the table:
    4. INSERT INTO RECEIPT_ORDER VALUES (1,'RO', 200,'01-JAN-2013','NORTH');
      INSERT INTO RECEIPT_ORDER VALUES (2,'RO', 600,'01-JAN-2015','NORTH');
      INSERT INTO RECEIPT_ORDER VALUES (3,'RO', 800,'01-JAN-2016','NORTH');
      INSERT INTO RECEIPT_ORDER VALUES (4,'RO', 900,'01-JAN-2017','NORTH');
      INSERT INTO RECEIPT_ORDER VALUES (4,'RO', 900,'01-MAR-2017','NORTH');
      COMMIT;
    5. Below is the select script to see the data for ’01-MAR-2017′:
    6. SELECT * FROM RECEIPT_ORDER PARTITION (SYS_P1363);
      SYS_P163 is the partition oracle automatically created on running the insert script for '01-MAR-2017'.

      The partition name created in the database can be found using this script:

      SELECT partition_name, tablespace_ name, high_value
      FROM user_tab_partitions;
  5. Composite Partitioning: is a combination of two partitions on a single table. In a composite partition, a table is partitioned on one key and the partition is further sub-partitioned on the other key. There can be a range of composite partitions in Oracle such as List-Range, Hash-Range, etc.
    1. Below is the example DDL for List Range Partition:
    2. CREATE TABLE STORE_SALE
        (   
       STORE_NO NUMBER, 
          DEPT_NAME VARCHAR2 (20),
         STORE_SALE_AMOUNT NUMBER (10, 2),
          STORE_SALE_DATE DATE,
          REGION VARCHAR2 (10)
        )
       PARTITION BY RANGE (STORE_SALE_DATE) SUBPARTITION BY LIST (REGION)
         SUBPARTITION TEMPLATE
         (
             SUBPARTITION P1 VALUES ('NORTH'),
             SUBPARTITION P2 VALUES ('SOUTH'),
             SUBPARTITION P3 VALUES ('EAST'),
             SUBPARTITION P4 VALUES ('WEST')
         )
       (Partition P1 values less than (TO_DATE ('01-JAN-2014','dd-MON-yyyy')), 
      Partition P2 values less than (TO_DATE ('01-JAN-2015','dd-MON-yyyy')), 
      Partition P3 values less than (TO_DATE ('01-JAN-2016','dd-MON-yyyy')), 
      Partition P4 values less than (TO_DATE ('01-JAN-2017','dd-MON-yyyy'))
      );
    3. Below is the script to insert the data into the table:
    4. INSERT INTO STORE_SALE VALUES (1,'RO', 200,'01-JAN-2013','NORTH');
      INSERT INTO STORE_SALE VALUES (1,'RO', 600,'01-JAN-2015','SOUTH');
      INSERT INTO STORE_SALE VALUES (1,'RO', 800,'01-JAN-2016','EAST');
      INSERT INTO STORE_SALE VALUES (1,'RO', 800,'01-MAR-2016','WEST');
      COMMIT;
    5. Below is the script to see the data in the partition:
    6. SELECT * FROM STORE_SALE PARTITION (P4)

Partition Pruning:

One of the techniques often used to improve performance is Partition Pruning. Partition pruning is the method of eliminating the data not needed as output in a query, thereby increasing query performance. Using partition pruning, the query can only access the partition frequently used instead of scanning the entire table. For example, if the query filters based on the partition key it will read only the data that it needs and you can get performance improvement by using partition eliminations.

For example, we have a partitioned table called Sales. The partition key for Sales is sales date. Let’s assume that Sales has one year of data, January to December, with a partition for each month of data. If the following query is run:

SELECT * 
FROM SALES 
WHERE sales date BETWEEN '1-MAR-16' AND '31-MAR-16';

Partition pruning is achieved by partition elimination of all data partitions except for month of March. Partitioning helps to scale data warehouse environment by divide large tables into smaller pieces. Using partitions queries can be run on smaller data reducing the I/O requirements.

Types of Indexes on Partitions

There are two types of indexes that can be created on the partitions:

  1. Local Indexes: – These are indexes that are local to each partition and maintain one to one relationships between the index partition and partitioned tables. All keys stored in the index refer to one underlying table.

For example: the below statement creates the index IDX_SALES, which is a local index on the SALES table, with partitions based on regions.

CREATE INDEX IDX_SALES ON SALES (REGION) LOCAL;
  1. Global Indexes: – These are indexes that span partitions. These can be partitioned or non-partitioned. In case of global partitioned indexes, each partition in the index can be mapped to multiple underlying partitions.

For example, the below statement creates the global partitioned index IDX_SALE_ORDER, which is a global index on SALE_ORDER table:

CREATE INDEX IDX_SALE_ORDER
ON SALE_ORDER (FREIGHT_DATE)
GLOBAL
   PARTITION BY RANGE (FREIGHT_DATE)
      (Partition IDX1 values less than (TO_DATE ('01-JAN-2016','dd-MON-yyyy')), 
       Partition IDX2 values less than (MAXVALUE));

Try to use local indexes wherever possible as they are easier to maintain and are useful for the partition pruning technique, which can help in performance improvement.

Summary

In this article, I have explained the concepts of partitioning tables and indexes in Oracle and how partitioning can be useful for large tables to improve performance and maintenance. I have also given real life examples of how these partitions and indexes can be created and used.