How does partition pruning, a 11gR2 database feature, help to improve performance on an
Exadata Database Machine?
A.
Partition pruning allows you to reduce a 5 TB I/O operation to a much smaller I/O operation and
therefore, enables a much faster return of information.
B.
Using partition pruning can force the optimizer to use that method instead of Smart Scan.
Partitioning in Exadatashould be used wisely
C.
Partition pruning is not a supported option in the11gR2database codethatruns on an Exadata
Machine, due to issues with Cost Based Optimizer plans.
D.
Partition pruning allows you to reduce the size of the physical table by reducing fragmentation
within the individual tables.
E.
Partition pruning allows you to reduce a 5 TB I/O operation to a larger I/O operation and
therefore, forces the use of the Smart Scan technology.
Answer is A. Pruning reduces I/O into smaller operations.
Hi Matt
Can you please share any document reference related to partition in exadata ?
Thanks
Jai
Hi Jai,
This is pretty old, but answers this particular question specifically.
https://blogs.oracle.com/datawarehousing/entry/partitioning_andorwith_exadata
“In essence, partition pruning allows you to reduce a 5TB I/O operation to a much smaller I/O operation and therefore much faster return of the information.”
Thanks Matt. Really appreciate !
The answer is A:
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.
A
A