Looking back at some of my previous posts I was reminded how easy it is to overlook one important feature when trying to comment on another. In particular, in this case, a short posting on indexed access paths omitted any mention of parallel execution until a comment from David Aldridge jogged my memory. So here’s an important thought about parallel execution.
Whatever you thought you knew about parallel execution, you need to review that knowledge in the light of partitioning. For example:
Parallel operations always do tablescans – No. Not if you have partitioned indexes. A parallel operation can run with each PX (parallel execution) session doing an index range scan of a single partition of the index.
Parallel operations do direct path reads – No. Consider the previous comment. A parallel operation can do index range scans – these are not done with direct path reads. (In fact a parallel nested loop join with an index on the second table will do cached reads on the index and second table – without a shred of partitioning in sight. The direct path reads will be restricted to the tablescan of the first table).
Finally, tablescans are not the only thing that do direct path reads. You can get them from parallel index fast full scans as well.
By the way, if you’re thinking that paralel queries “obviously” ought to do tablescans and hash joins, think again. Sometimes the performance problem with parallel execution comes from the huge volume of messaging that can take place – typified by wait event “PX Deq Credit: Send Blkd” on a parallel_to_parallel step; you may find that forcing a nested loop join makes a massive difference to the messaging, and makes the query run faster.