Oracle Partitioning

         Partitioning addresses key issues in supporting very large tables and indexes by splitting them into smaller and more manageable pieces called partitions.

 LOB  datatype can be partitioned
 LONG/LONG RAW datatype cannot be partitioned

When to Partition a table Table Size &  Historical data table 

Types of Partitioning

• Range Partitioning
• Hash Partitioning
• List Partitioning
• Interval Partitioning ( Only in 11G )
• REF Partitioning ( Only in 11G )
• Composite Partitioning

Range Partitioning

    Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key.

List Partitioning allows unordered and unrelated sets of data to be grouped and organized together

Interval partitions are automatically allocated based upon the value/interval that we set

   Restrictions on Interval Partitioning
•Cannot be used for index organized tables
•Must use only one partitioning key column and it must be a DATE or NUMBER
•Cannot create domain indexes on interval partitioned tables
•Are not supported at the sub-partition level

Benefits of Interval Partitioning
Range partitioned table can easily be converted to use interval partitioning by using the following command:

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Since Oracle will automatically create the new partition based on interval if a partition does not exist in range partition .So that the following error can be solved

ORA-14400: inserted partition key does not map to any partition


ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions.


Oracle uses some sort of fixed algorithm for defining partitions. Choose partition interval correctly.

REF partitioning child table inherits the partitioning strategy of parent table through PK-FK relationship


Different Types of REF  partitioning

---------