Lesson 1

Partitioned tables are an Oracle feature that can dramatically improve a variety of database operations, from 1) query execution to 2) backup and recovery to 3) higher availability. This module introduces:

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 addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions, which are entirely transparent to an application. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects.
Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as compression enabled or disabled, physical storage settings, and tablespaces. Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

If you use partitioned tables as part of your application, it is likely, if not imperative, that you use partitioned indexes to complement the advantages gained by using table partitioning. Usually, having a partitioned table and indexes go hand in hand. When there is one, there is usually both. This is common, but not essential. It is possible to have partitioned tables without partitioned indexes, and it is possible to have a non-partitioned table with partitioned indexes. There are several factors that affect the design of the database tables and indexes, including:

1. Application data loading requirements (DML) a) Is it an OLTP system? b) Is it a data warehouse?
2. Client query requirements
3. Data volume
4. Data purging requirements

Deciding on whether your indexes should be partitioned will largely be based on the answers to the aforementioned factors. All of the above factors are important, but often it boils down to data volume. The volume of your data affects 1) load speed, 2) query speed, and 3) data purging speed. Obviously, as volume increases, design considerations must include factors to improve the speed of all these factors. All this said, some key reasons to have partitioned indexes include:

1. You can perform maintenance on only a portion of the index based on activity in the table.
2. You can rebuild only portions of an index.
3. You can spread an index out evenly, that is, you can always have a balanced index.

Within this chapter are examples of how to create partitioned indexes. In addition, there are some examples of partitioned index usage, along with examples of operations that are performed on the database that can affect partitioned indexes.