Forum Stats

  • 3,769,603 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

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

Joerg.Sobottka
Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 596 Bronze Trophy

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