How does partition pruning, a 11g R2 database feature, help to improve performance on an Exadata Database Machine?

Question

Options

• APartition pruning allows you to reduce a 5 TB I/O operation to a much smaller I/O operation and
• BUsing partition pruning can force the optimizer to use that method instead of Smart Scan.
• CPartition pruning is not a supported option in the 11gR2 database code that runs on an Exadata
• DPartition pruning allows you to reduce the size of the physical table by reducing fragmentation
• EPartition pruning allows you to reduce a 5 TB I/O operation to a larger I/O operation and

• A
  81% (25)
• B
  6% (2)
• D
  10% (3)
• E
  3% (1)

Explanation

Partitioning Pruning: Partitioning pruning (a.k.a. Partition elimination) is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude. For example, suppose an application contains an ORDERS table containing an historical record of orders, and that this table has been partitioned by day. A query requesting orders for a single week would only access seven partitions of the ORDERS table. If the table had 2 years of historical data, this query would access seven partitions instead of 730 partitions. This query could potentially execute 100x faster simply because of partition pruning. Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.

No community discussion yet for this question.