154 6.3 Tuning Queries with Partitioning
Now query a nonpartitioned table as shown in Figure 6.8.
6.3.2 Partitioning and Parallel Processing
Parallel processing using partitions is very simple to understand. Partition-
ing can be used to split a table or materialized view into separate physical
chunks. Earlier in this chapter, we created a range partition table of the
SALE table called PSALE_YEAR. The partition key is the
TRANSACTION_DATE column. Figure 6.9 shows the same query state-
ment, but executed against the partitioned table PSALE_YEAR. The query
plan in Figure 6.9 is thus vastly different to that of Figure 6.8.
6.3.3 Partition Pruning
Partition pruning is a fancy name applied to a very simple process in which
a query reads a subset of partitions in a table, quite often a single partition,
as a result of a ﬁlter. In other words, the use and deﬁnition of partition
pruning is really no big deal. However, the difference in performance can be
phenomenal. Figure 6.10 shows partition pruning in action, using a ﬁlter to
reduce the query to reading a single partition.
Figure 6.11 shows the same query as in Figure 6.10, but using the paral-
lel hint once again.
A full table scan on