What is Partitioning in Oracle ?

Partitioning is the concept of physically breaking down a considerably large table or index into multiple smaller, manageable pieces called partitions.

Logically the partitioned object remains as a single entity for the consumers, where physically the object is split into a number of relatively smaller partitions. Partitioning feature provides the flexibility to tune the structures for better performance and availablity. All the partitions of a partitioned object must have the same logical attributes like colunm names, data types, constraints etc, but each partition can have separate physical attributes like tablespace etc. Partition tables are created using the PARTITION BY clauase in the CREATE TABLE / CREATE INDEX commands.

A table can be partitioned upto 1024K – 1 partitions

A table containing LONG or LONG RAW data types cannot be partitioned

Partitioning of a table or index is driven by a combination of one or more columns known as the Partition Key

A partition key -

Determines a unique partition of the object for each row
Consists of a list of 1 to 16 columns
Cannot contain a column of type ROWID
Can contain columns that are NULLable

Oracle offers the following partitioning methods

Range Partitioning – Each partition stores data belonging to a range of values of the partition key.

                               For example a table partitioned by ranges of reporting date.

List Partitioning  – Each partition is defined to store rows with partition key values belonging to a finite list. 

                            For example table list partitioned on a “country” column.

Hash Partitioning  – Oracle applies a hashing algorithm on the partition key to decide the partition of a row.

                              Only the number of partitions are defined at the table creation time. 

                             There is no explicit control to direct the rows to a particular partition in this case.

Composite Partitioning – Composite partitioning combines two partitioning methods to further divide the partitions of an object into sub partitions.

The methods available for composite partitioning are(11g) Range – Range, Range – List, Range – Hash; List – Range, List – List, List – Hash.

Oracle 11g offers the following partitioning methods as well – Interval partitioning, Reference Partitioning, System Partitioning.