79898

Partitioned Tables and lndexes You can partition tables and indexes. Partitioning helps to

support very large tables and indexcs by enabling you to divide the tables and indexes into smaller and morę manageable pieces called partitions. SQL queries and DML statements do not have to be modified to access partitioned tables and indexes. Partitioning is transparent to the application.

After partitions are defined, certain operations become morę efficient. For cxample, for

some queries, the database can generate query results by accessing only a subset of partitions, rather than the entire tablc. This technique (called partition pruning) can provide order-of-magnitudc gains in improved performance. In addition, data managemcnt operations can takc place at the partition lcvel, rather than on the entire tablc. This results in rcduccd times for operations such as data loads: index creation and rebuilding; and backup and recovery.

Each partition can be stored in its own tablespacc, independent of other partitions. Becausc different tablcspaces can be on different disks, this provides a table structurc that can be better tuned for availability and performance. Storing partitions in different tablespaces on separate disks can also optimize available storage usage, because frequently accessed data can be placed on high-performance disks, and infrequently retrieved data can be placed on less expensive storage.

Partitioning is useful for many types of applications that manage large volumes of data. Online transaction processing (OLTP) systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from incrcased performance and manageability.

To spccify partitioning options using SQL Developcr. click the Advanced chcckbox when creating a tablc. Then click Partitioning to sec the partitioning options available.

For details about partitioning, sec Oracle Database SQL Language Reference.