The where (:B1 is null or t.col1 = :B1) execution plan problem

Joerg.Sobottka

Very often I discuss with customers or other DBAs tuning issues. A typical situation is regarding "good" vs. "bad" execution plans, evil full table scans and how to fix that. One of the problems we have discussed in the last weeks was - how to tune the following select to not do full table scans all the time???

select * from t1_test t where (:B1 is null or t.col1 = :B1);

The main issue here is that Oracle doesn't know anything about the bind variables value at the time the execution plan is generated. Read more about that and why user experience counts more than an execution plan on my latest blog entry: https://a-different-view-by-js.blogspot.com/2018/01/theORNullProblem.html