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) ?

  • 1
    What is "resp" an abbreviation for? I can't quite figure out what word you're intending to use there. – Justin Cave Feb 25 '11 at 11:00
  • Sorry it's German resp. is abrivation of respective. A further German word would be beziehungsweise .Perhaphs I better use just or. My online dictionaries like pons.eu here do not really help. – bernd_k Feb 25 '11 at 11:08
  • I use dict.tu-chemnitz.de – gbn Feb 26 '11 at 10:51

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)

  • good idea, but you never know, if a former access programmer uses -1 instead of 1. Best thing would be redesign the column as bit. – bernd_k Feb 25 '11 at 15:10

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.