I am repeatedly seeing queries similar to:
select count(*)
from some_table
where nvl(some_column,'N') = 'Y';
Where some_column takes values in ('Y', 'N', null).
Is there a reason this is being used rather than the following?
select count(*)
from some_table
where some_column = 'Y';
From the few examples I've tried, there doesn't seem to be any performance advantage, and in a few cases the first query being slower.
Thanks in advance.
I see this a lot too. Most likely the developer has got into the habit of wrapping nullable columns in an nvl expression, probably after being burned in the past by an unexpected null value, and now adds it everywhere without bothering to think it through, or perhaps without having really understood the issue in the first place.
This will prevent use of any index, partition pruning or histograms on the underlying column (although the optimiser's adaptive features may generate dynamic histograms to correct for this).
You may need this kind of construction in the case where you are checking that an expression does not have some particular value, if you want to treat null as not 'Y' for example. Perhaps the developer is thinking of this.