Partitioning Overview

Partitioning is powerful functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a comprehensive range of partitioning schemes to address every business requirement. Moreover, since it is entirely transparent in SQL statements, partitioning can be used with any application, from packaged OLTP applications to data warehouses.

Key Benefits

Partitioning:

• Increases performance by only working on the data that is relevant.
• Improves availability through individual partition manageability.
• Decreases costs by storing data in the most appropriate manner.
• Is easy as to implement as it requires no changes to applications and queries.
• Is a mature, well proven feature used by thousands of Oracle customers.
   

Partitioning Methods

Oracle supports a wide array of partitioning methods:

• Range Partitioning - the data is distributed based on a range of values.
• List Partitioning The data distribution is defined by a discrete list of values. One or multiple columns can be used as partition key.
• Auto-List Partitioning - extends the capabilities of the list method by automatically defining new partitions for any new partition key values.
• Hash Partitioning - an internal hash algorithm is applied to the partitioning key to determine the partition.
• Composite Partitioning - combinations of two data distribution methods are used. First, the table is partitioned by data distribution method one and then each partition is further subdivided into subpartitions using the second data distribution method.
• Multi-Column Range Partitioning - an option for when the partitioning key is composed of several columns and subsequent columns define a higher level of granularity than the preceding ones.
• Interval Partitioning - extends the capabilities of the range method by automatically defining equi-partitioned ranges for any future partitions using an interval definition as part of the table metadata.
• Reference Partitioning Partitions - a table by leveraging an existing parent-child relationship. The primary key relationship is used to inherit the partitioning strategy of the parent table to its child table.
• Virtual Column Based Partitioning - allows the partitioning key to be an expression, using one or more existing columns of a table, and storing the expression as metadata only.
• Interval Reference Partitioning - an extension to reference partitioning that allows the use of interval partitioned tables as parent tables for reference partitioning.

Summary

Partitioned tables can be created as internal heap or index organized tables, as external tables, and beginning with Oracle Database 19c as hybrid partitioned tables. The availability of individual partitioning methods varies with the type of the table.