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
How to create the Partition Table
Depending on types of Partition , here is the way to create the Partition table
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)
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
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') );
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 . . . . . .; );
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