What are two ways in which query performance can be improved with partitioning?
A.
Partition pruning
B.
Partition optimization
C.
Partition compression
D.
Partition-wise joins
Explanation:
A: Even when you don’t name a specific partition in a SQL statement, the fact that a
table is partitioned might still influence the manner in which the statement accesses the table.
When a SQL statement accesses one or more partitioned tables, the Oracle optimizer attempts to
use the information in the WHERE clause to eliminate some of the partitions from consideration
during statement execution. This process, called partition pruning, speeds statement execution by
ignoring any partitions that cannot satisfy the statement’s WHERE clause. To do so, the optimizer
uses information from the table definition combined with information from the statement’s WHERE
clause.
D: A partition wise join is a join between (for simplicity) two tables that are partitioned on the same
column with the same partitioning scheme. In shared nothing this is effectively hard partitioning
locating data on a specific node / storage combo. In Oracle is is logical partitioning.
If you now join the two tables on that partitioned column you can break up the join in smaller joins
exactly along the partitions in the data. Since they are partitioned (grouped) into the same buckets,
all values required to do the join live in the equivalent bucket on either sides. No need to talk to
anyone else, no need to redistribute data to anyone else… in short, the optimal join method for
parallel processing of two large data sets.