Partitioned tables and parallel query processing

Parallel query processing on partitioned tables can potentially produce dramatic improvements in query performance. Partitions increase simultaneous access by worker processes. When enough worker processes are available, and the value for the max parallel degree configuration parameter is set equal to or greater than the number of partitions, one worker process scans each of the table’s partitions.

When the partitions are distributed across physical disks, the reduced I/O contention further speeds parallel query processing and achieves a high level of parallelism.

The optimizer can choose to use parallel query processing for a query against a partitioned table when parallel query processing is enabled. The optimizer considers a parallel partition scan for a query when the base table for the query is partitioned, and it considers a parallel index scan for a useful index.

See Chapter 25, “Parallel Query Optimization,” for more information on how parallel queries are optimized.