When I run my code through SQL Optimizer and it makes the following suggestion regarding my code.
ORIGINAL: AND xv.var = 'XYZ'
OPTIMIZED: AND xv.var = NVL('XYZ', UID)
This seems to greatly enhance performance. The explain plains are the same, except for cost. Why does that help with performance and why does SQL Optimizer even tried that?
The NVL is mainly used to affect how Oracle evaluates the literal value in the condition. Without it, Oracle can look at the value used and compared it with the histogram of your column to estimate how many records would satisfy your condition. This result of the evaluation means a lot to Oracle. Depending on the result, Oracle may determine:
- Whether to use index or not
- How to join you table (Hash, Nested Loop, Merge)
- What order to join the tables to retrieve data (start from Table A to Table B or reverse)
