Such intelligent pruning can dramatically reduce the data volume, resulting in substantial improvements in query performance. For example, if a query only involves Q1 sales data, there is no need to retrieve data for the remaining three quarters. This knowledge is exploited in optimizing SQL statements to mark the partitions that need to be accessed, eliminating ("pruning") unnecessary partitions from access by those SQL statements.įor each SQL statement, depending on the selection criteria specified, unneeded partitions can be eliminated. The Oracle server incorporates the intelligence to explicitly recognize partitions. PARTITION sales13 VALUES LESS THAN ( 52 ) TABLESPACE ts12 ) įigure 9-1 SALES Table Partitioned by Weekįor more examples of partitioned tables, see the Oracle8 Administrator's Guide. PARTITION sales2 VALUES LESS THAN ( 8 ) TABLESPACE ts1, (PARTITION sales1 VALUES LESS THAN ( 4 ) TABLESPACE ts0,
This SQL statement creates the partitioned table: In Figure 9-1, the sales table contains historical data divided by week number into 13 four-week partitions. The section "Basic Partitioning Model" on page 9-11 provides more information about partitioning concepts. You can balance I/O load by mapping partitions to disk drives.You can back up and recover each partition independently.You can contain the impact of damaged data.Optionally, you can store each partition in a separate tablespace, which has the following advantages: However, storage specifications and other physical attributes such as PCTFREE, PCTUSED, INITRANS, and MAXTRANS can vary for different partitions of the same table or index.Įach partition is stored in a separate segment. For example, all partitions in a table share the same column and constraint definitions and all partitions in an index share the same index columns. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.Īll partitions of a table or index have the same logical attributes, although their physical attributes can be different. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes.
Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Oracle only supports partitioning for tables and indexes it does not support partitioning of clustered tables and their indexes, nor of snapshots.