Lesson 11

Partitioning tables can help you to 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

With the Enterprise Editions of Oracle8 and beyond, you can purchase the Partitioning Option. As the name implies, this option allows you to partition tables and indexes. Partitioning a data structure means that you can divide the information in the structure among multiple physical storage areas. A partitioned data structure is divided based on column values in the table. You can partition tables based on the range of column values in the table (often date ranges), or as the result of a hash function (which returns a value based on a calculation performed on the values in one or more columns). As of Oracle9i you can also use a list of values to define a partition, which can be particularly useful in a data warehouse environment.
Oracle Database 11g added several partitioning types over its releases. Interval partitioning provides the ability to automatically generate a new partition of a fixed interval or range when data to be inserted does not fit into existing partition ranges. Reference partitioning is used where a parent-child relationship can be defined between tables and the child table inherits the same partitioning characteristics as the parent. Virtual column-based partitioning enables partition keys to be defined by virtual columns.
You can have two levels of partitions, called composite partitions, using a combination of partition methods. Prior to Oracle Database 11g, you could partition using a composite of range and hash partitioning. Oracle Database 11g added the ability to combine list partitioning with list, range, or hash partitioning, or range partitioning with a different range partitioning scheme. Oracle Database 12c adds interval reference partitioning.
Oracle is smart enough to take advantage of partitions to improve performance in two ways:

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

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

In this module you were introduced to the following terms:

1. Dump file: The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.
2. RAID disk arrays: RAID (redundant array of independent disks) is a data storage virtualization technology that combines multiple physical disk drive components into a single logical unit for the purposes of data redundancy, performance improvement, or both

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