Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too.
For examples, will not be any reads only if it is an access predicate on index range/unique scan(it is quite obvious because of ) or full table scan on 220.127.116.11 and with gathered stats. If it is FTS on previous versions will be read only segment header. In others cases oracle will do useless scans.
So if bind variable can be null and you want to be sure that oracle will not do futile work in such cases, just add predicate ":bind_variable is not null".
This note is just aggregated info from recent question from our forum where i participated
Full test code you can download as file – test.sql.