New features for partitioned tables in Oracle
When working with large volumes of data in a single table, the capability to divide data into distinct,
logical groups can help optimize queries.
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:
Partition an index-organized table
Partition an object table
Partition a table with LOBs
Describe how to enable row movement in a partitioned table
Rename, move, and coalesce partitions
Exchange a partition with a table
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.
Use the ANALYZE statement to collect statistics, for example, to:
- Collect or delete statistics about an index or index partition, table or table
partition, index-organized table, cluster, or scalar object attribute.
- Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
- 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:
This section describes some of the ways you can manipulate LOBs in partitioned
- LOB segments can be spread between several tablespaces to balance I/O load and to make backup and recovery more manageable.
- LOBs in a partitioned table become easier to maintain.
- LOBs can be partitioned into logical groups to speed up operations on LOBs that are accessed as a group.
: Dividing one table into several sections, or partitions, which are physically stored as if each partition were a separate table.