Oracle partitioned table

What is Oracle Partitioned Table?

Partitioning divides a table, index, or index-organized table into smaller components. Each component is called a partition (or subpartition for composite partitioned objects). Every partition has a unique name, and may optionally have individual storage characteristics: examples include compression, or being stored in different tablespaces. A well-designed partitioning strategy can improve query access and updates by limiting the operation to a single or sub-set of partitions.

Partitioning for Performance – Partition Pruning

Oracle Partitioned table feature enables the query optimizer to skip partitions that are not required by a particular SQL statement. Depending upon the SQL statement, the optimizer can identify partitions and sub-partitions that need to be accessed, as well as ones that do not. This can result in substantial improvements in query performance, because the optimizer focuses on a specific subset of data that can be refined further if additional predicates exist.

The optimizer does this by using the partition information stored in the data dictionary to identify the content of a partition without querying the data it contains. Partitioning is an extra layer of the data dictionary between Tables/Indexes and Tablespaces

Limitation of Partitioned Table

In Oracle Database 11g, a table can have a maximum of 1048575 (1024K – 1) partitions. With the sole exception of tables containing columns of LONG or LONG RAW datatypes, all tables can be partitioned (including columns of type CLOB or BLOB).

Type of Table Partitions

Partitioning is available in Oracle database from 8.0 version and oracle is continuously adding more and more features with every release.The following table shows a summary of the main changes

The main partition strategy given by Oracle are
(1)Range
(2)List
(3) Hash
(4)Composite

How to create the Partition Table

Depending on types of Partition , here is the way to create the Partition table

Range Partitioning

Oracle Partition the data based on consecutive ranges of values of the Partition key.
Each partition’s end point is specified using the following syntax:

VALUES LESS THAN (value-list)

Example

CREATE TABLE EXP_RANGE
 (ID NUMBER(15) NOT NULL,
 CODE_ID NUMBER(15) NOT NULL,
 PERIOD_NAME VARCHAR2(15) NOT NULL,
 ACTUAL_FLAG VARCHAR2(1) NOT NULL,
 VERSION_ID NUMBER(15),
 LAST_UPDATE_DATE DATE NOT NULL,
 . . . . . .
 )
 PARTITION BY RANGE (PERIOD_NAME)
 (
 PARTITION PR1 VALUES LESS THAN ('JAN-2019'),
 PARTITION PR2 VALUES LESS THAN ('FEB-2019')
 . . .
 . . .
 );

Range Partitioning is useful for partitioning historical and transaction data, as the boundaries of range partition define the order of partition in tables and indexes

List Partitioning

In this method, oracle maps specific rows to partitions, based on a static list of literal values. The partition key for list partitioning can only be based on a single column.

CREATE TABLE EXP_LIST
 (ID NUMBER NOT NULL,
 ORG_ID NUMBER,
 OPEN_FLAG VARCHAR2(4) NOT NULL,
 . . . . . .
 ) PARTITION BY LIST (open_flag)
 (
 PARTITION PR1 VALUES ('YES'),
 PARTITION PR2 VALUES ('NO')
 );

Hash Partitioning

In this oracle used the hashing algorithm to decide the physical placement of data. Hash partitioning will distribute data evenly across a fixed number of partitions.

CREATE TABLE EXP_HASH
 (ID NUMBER NOT NULL,
 ORG_ID NUMBER,
 ORDERED_ITEM VARCHAR2(2000),
 OPEN_FLAG VARCHAR2(1) NOT NULL,
 . . . . . .
 )
 PARTITION BY HASH (ID)
 PARTITIONS 10
 . . .
 . . .;
 );

Composite Partitioning

In this oracle used the combination of range, list and hash partitioning.composite partitioning methods are range-hash or range-list.

CREATE TABLE sales_details
   ( prod_id       NUMBER(6)
   , cust_id       NUMBER
   , time_id       DATE
   , channel_id    VARCHAR2(1)
   , promo_id      NUMBER(6)
   , quantity_sold NUMBER(3)
   , amount_sold   NUMBER(10,2)
   )
  PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
   SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
  ( PARTITION sales_q1_2019 VALUES LESS THAN (TO_DATE('01-APR-2019','dd-MON-yyyy'))
  , PARTITION sales_q2_2019 VALUES LESS THAN (TO_DATE('01-JUL-2019','dd-MON-yyyy'))
  , PARTITION sales_q3_2019 VALUES LESS THAN (TO_DATE('01-OCT-2019','dd-MON-yyyy'))
  , PARTITION sales_q4_2019 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy'))
  );

How to move the existing object to Partitioned object

There are many ways to achieve this feat. I am explaining here the simplistic method
1.Create an empty partitioned table using the partitioned clause and with the parallel option. The table name must have a different name from the non-partitioned table.
2.Populate data for the required partition from the non-partitioned table.
3. Consider using the APPEND oracle hint with an INSERT statement as an easy code change that provides good performance. If logging is enabled and indexes are present, the INSERT /*+ APPEND */ hint may not be effective.To minimize the overhead of index maintenance, drop indexes prior to migration and recreate them once the partitioned table has been populated.
4.Rename the partitioned table to the same as the original table, or change the synonym.
5. Build table indexes for the partitioned table

Also Reads
Oracle Partition Index
Oracle Parallel Query
https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm