Lesson 1

When working with large volumes of data in a single table, the capability to divide data into distinct, logical groups can help optimize queries. Partitioning^[1] tables has been used prior to Oracle8, and this lesson focuses on the Oracle features that enhance and expand the capability to partition tables.

By the end of the module, you will be able to:

1. Partition an index-organized table
2. Partition an object table
3. Partition a table with LOBs
4. Describe how to enable row movement in a partitioned table
5. Rename, move, and coalesce partitions
6. Exchange a partition with a table
7. Describe how to create a partition-wise query or join

The next lesson looks at how to partition an index-organized table.

To avoid the recreation of any bitmap index structure, Oracle recommends creating every partitioned table with at least one compressed partition whenever you plan to partially or fully compress the partitioned table in the future. This compressed partition can stay empty or even can be dropped after the partition table creation. Having a partitioned table with compressed partitions can lead to slightly larger bitmap index structures for the uncompressed partitions. The bitmap index structures for the compressed partitions, however, are in most cases smaller than the appropriate bitmap index structure before table compression. This highly depends on the achieved compression rates.

Purpose: Use the ANALYZE statement to collect statistics, for example, to:

1. Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
2. Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
3. Identify migrated and chained rows of a table or cluster.

[1] Partitioning: Dividing one table into several sections, or partitions, which are physically stored as if each partition were a separate table.