Partitioning Oracle Tables to Improve Database Performance and Maintenance

Partitioning tables can help you improve performance and reduce downtime and maintenance.
In this module, you learned about partitioning tables and indexes. You learned:

1. Reasons for partitioning
2. Advantages of partitioned tables
3. How to partition on a range of values
4. How to index a partitioned table
5. The differences between local and global indexes
6. How to merge, split, and drop partitions

Partitioning is available as part of the Partitioning Option under Oracle Database Enterprise Edition. It allows you to divide the information in a table or index among multiple physical storage areas, based on column values. You can partition tables based on a range of column values (often date ranges), as the result of a hash function computed on one or more columns, or based on an explicit list of values, which is particularly useful in a data warehouse environment.
Interval partitioning automatically generates a new partition of a fixed interval or range when data to be inserted does not fit into any existing partition's range. Reference partitioning defines a parent-child relationship between tables, where the child table inherits the same partitioning characteristics as the parent, including interval reference partitioning for automatically generated child partitions. Virtual column-based partitioning enables partition keys to be defined by virtual columns rather than physical ones.
You can also combine two partitioning methods into a single composite partitioning scheme, such as range-hash, range-list, list-list, list-range, list-hash, or a different range partitioning scheme layered on an outer range partition.
Oracle takes advantage of partitions to improve performance in two ways:

1. Oracle won't bother to access partitions that do not contain any data needed to satisfy the query.
2. If all the data in a partition satisfies part of the WHERE clause for the query, Oracle simply selects all the rows for the partition without evaluating the clause row by row.

Partitioned tables are especially useful in a data warehouse, where data can be partitioned based on the time period it spans.

In this module you were introduced to the following terms:

1. Partition key: The partition key is a set of one or more columns that determines the partition in which each row in a partitioned table should go. Each row is unambiguously assigned to a single partition. Oracle Database automatically directs insert, update, and delete operations to the appropriate partition by using the partition key.
2. Range partitioning: Range partitioning maps rows to partitions based on ranges of values of the partitioning key. It is the most common type of partitioning and is often used with dates. Interval partitioning extends range partitioning by automatically creating equi-sized partitions for data beyond existing range bounds.

More terms from the Extended Database Features glossary can be located at the Glossary. In the next module you will learn about clustering tables and indexes.

Before moving on, click the Quiz link below to answer some final questions about modifying partitions.
Oracle Partition - Quiz