Discussions
Categories
- 5.4K All Categories
- 15 Introduce Yourself!
- 455 Community Feedback - NEW! (No Product Questions)
- 107 General Community Platform Concerns/Kudos/Feedback
- 83 Community Platform Bug Reports
- 86 How Do I Use the Community?
- 65 Where is the...? (Community Platform Locations)
- 18 Ideas and Suggestions for the Community Platform
- 71 Personal Document & Blog Archive
- 5 Community Programs
- 5 Get-Togethers
- 4.8K Certification Community
- 4.7K Certification Community Discussions
- 22 Oracle Certified Master Profiles
- 31 Oracle Database 12c Administrator Certified Master Profiles
- 83 Visual Builder Cloud Service
The where (:B1 is null or t.col1 = :B1) execution plan problem

Joerg.Sobottka
Senior Consultant and Oracle Ace Member Posts: 598 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