Partitioned Tables

Any table can be partitioned up to a million separate partitions except those tables containing columns with LONG or LONG RAW data types. You can, however, use tables containing columns with CLOB or BLOB data types.

This sections contains the following topics:

Note:

To reduce disk and memory usage (specifically, the buffer cache), you can store tables and partitions of a partitioned table in a compressed format inside the database. This often improves scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead.

See Also:

Oracle Database Administrator's Guide for information about guidelines for managing tables

When to Partition a Table

Here are some suggestions for situations when you should consider partitioning a table:

  • Tables that are greater than 2 GB.

    These tables should always be considered as candidates for partitioning.

  • Tables that contain historical data, in which new data is added into the newest partition.

    A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

  • Tables whose contents must be distributed across different types of storage devices.

When to Partition an Index

Here are some suggestions for when to consider partitioning an index:

  • Avoid index maintenance when data is removed.

  • Perform maintenance on parts of the data without invalidating the entire index.

  • Reduce the effect of index skew caused by an index on a column with a monotonically increasing value.