Thanks for the question, hu.
Asked: April 21, 2016 - 4:33 pm UTC
Last updated: April 22, 2016 - 10:19 am UTC
Version: 11R2
Viewed 1000+ times
You Asked
hi Tom!
i got a problem,how to optimize a SQL which use many conditons with "or" , like this:
select n1,n2,n3,n4.. from a where n1=1 or n2=2 or n3=4,or ...;
Thank you!
best wishes!
and Chris said...
When optimizing queries, Oracle can do an OR expansion. This effectively converts your query into separate statements UNIONed together:
https://blogs.oracle.com/optimizer/entry/or_expansion_transformation There is a limit to how many times it will do this though:
https://blogs.oracle.com/sql/entry/avoid_or_bind_variable_predicates So OR conditions on multiple columns are often tough to optimize.
Ask yourself - do you really need all those ORs? Is there another way you could write your query? Does splitting it query blocks you union all together help?
If you have a specific piece of SQL you need help with, please post the query along with:
- It's execution plan
- DDL statements for the tables, including any constraints and indexes on these