Execution Plan Changes with a constant expression in 10gR2
Posted on 2011-10-28
I'm not wild about dynamic sql. Consequently, when a query parameter is optional, often write the query with an 'or' and a constant expression:
select col1, col2
where col3=:var or :var=0;
Sql Server correctly adjusts the excecution plan based on the constant expression (as I would hope). In Oracle, unfortunately, this causes a full table scan regardless of the constant expression. Is there a way around this? Is it better in 11g?