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.
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.