Is where x = 0 or x is null to be replaced by where isnull(x, 0) = 0 resp. where NVL(x, 0) = 0

Question

Think of x as a column containing null, 0, 1. Only less than 1 % of the rows contain 1. There is no logical difference between 0 and Null.

Is it a good idea to insist, that isnull rather than NVL is used here (performance) ?

Answer 1

If you're looking at it purely from just what's better from a performance perspective ... I'd just test both of your methods and see what's faster for your data.

... but as you only have three states anyway, and this is going in a where clause, it might be even faster to just check that it's not the third case, and then you don't need the NVL test:

column <> 1

Or, if there really is no difference (and NULL isn't a case of 'we don't know, so we're going to treat it for all practical purposes that it's 0, but we occassionally need to know when it's unknown'), just update the table and set it to 0 where it's currently NULL. (and adjust whatever's feeding in the NULLs)

Answer 2

It depends but given the data distribution, probably not.

The performance benefit of using a single expression like NVL(x,0) in Oracle is that it becomes indexable with a function-based index. But since there are very few cases where the optimizer would use an index to return 99% of the data in the table, it seems unlikely that there would be benefits in this case. Potentially, a composite index that included NVL(x,0) and some other columns such that it would be sufficiently selective could be beneficial in some cases but it's relatively unlikely that adding an expression that only adds a tiny bit to the selectivity would be a net benefit.