Merge Joins and Nested Join
In a large join operation, a merge join requires less I/O and CPU time than a nested join. A merge join usually reads each block of the inner table only once, unless a large number of hash collisions occur.
A nested join performs a block read on the inner table for each outer row being evaluated. If the number of rows selected from the outer table is large, this can cause each block of the inner table to be read multiple times.
Merge Join with Covering NUSI
When large outer tables are being joined, a merge join of a table with a covering index of another table can realize a significant performance improvement.
The Optimizer considers a merge join of a base table with a covering NUSI, which gives the Optimizer an additional join method and costing estimate to choose from.
Logging Problematic Queries
You can log problematic queries in several ways. See Logging Problematic Queries.