NVL v/s Check IS NULL We have queries in procedures in oracle like,

    SELECT  ......
    WHERE   .....
    AND (SomeColumn IS NULL OR SomeColumn = SomeThing)


But our DBA is saying this is not good, and saying use NVL instead. We are using this for long time and haven't found any issue. Should we change IS NULL with NVL? from NVL I mean NVL(SomeColumn, '') = SomeThing
Many databases including Oracle have a COALESCE function that behaves like NVL, but takes two or more values.  This is useful to return a value when there are multiple columns that could have the desired value and/or to provide a default value. 

If all columns in an index are NULL, Oracle does not index the row, so it will require a table scan to retrieve rows when you have a NULL value in the match condition.  In other cases, you have values that are not NULL, to match on and only one column that could be NULL.  This is common in OUTER JOIN when you want to select only one value in the outer table.  

In any case, unless you have in index where the NULL value is indexed, a search of all candidate records will be required.  If you have an index that can be used to reduce the number of possible matches to less than 4% of the the records, then the candidate set may consist of those rows.  Oracle will generally use a table scan for small tables, or if there is no index that can limit the candidate rows sufficiently.  Above 4% (possibly lower) the preference will be for a table scan.

You have three options for the conditional:

NVL(column, value) = value
COALESCE(column, value) = value
column = value OR column IS NULL


In my experience, all three are equivalent.  You can verify for yourself, by running an explain plan on the query.

It is possible to influence the plan with hints.  However, this can easily result in sub-optimal plans.