Partitioning Tables  «Prev  Next»

Lesson 1

Oracle Partitioned Tables

Features of Oracle Partitioned Tables

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.

Module Objectives

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.

Distributed Databases

ANALYZE

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.

Manipulating LOBs in Partitioned Tables

You can partition tables that contain LOB columns. As a result, LOBs can take advantage of all of the benefits of partitioning including the following:
  1. LOB segments can be spread between several tablespaces to balance I/O load and to make backup and recovery more manageable.
  2. LOBs in a partitioned table become easier to maintain.
  3. LOBs can be partitioned into logical groups to speed up operations on LOBs that are accessed as a group.

This section describes some of the ways you can manipulate LOBs in partitioned tables.
[1] Partitioning: Dividing one table into several sections, or partitions, which are physically stored as if each partition were a separate table.