b_levitt
asked on
Execution Plan Changes with a constant expression in 10gR2
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
from table
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?
select col1, col2
from table
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
for boolean expressions position does matter because of short circuit evaluation.
however, I will concede that the optimizer should "try" to put the lightweight evaluations first in order to create its own short circuit.
is that what you mean?
however, I will concede that the optimizer should "try" to put the lightweight evaluations first in order to create its own short circuit.
is that what you mean?
I mean I've not been able to remove the FTS using a dummy in the where clause to get around the optional parameter/dynamic SQL.
maybe I should rephrase....
the "trick" to the "trick" is you want your dynamic sql to generate a constant expressions that allows short circuit evaluation.
the code above uses bind variables NOT constants.
so, you want something like this....
if var is not null then
v_sql := v_sql || ' and (1=1 or :var is null)';
else
v_sql := v_sql || ' and my_column = :var';
end if;
execute immediate v_sql using var;
this will cause the optimizer to eliminate your :var is null clause because the 1=1 makes it unnecessary
but it will use your column index when var is not null
the "trick" to the "trick" is you want your dynamic sql to generate a constant expressions that allows short circuit evaluation.
the code above uses bind variables NOT constants.
so, you want something like this....
if var is not null then
v_sql := v_sql || ' and (1=1 or :var is null)';
else
v_sql := v_sql || ' and my_column = :var';
end if;
execute immediate v_sql using var;
this will cause the optimizer to eliminate your :var is null clause because the 1=1 makes it unnecessary
but it will use your column index when var is not null
I might have misinterpreted your intent.
if your goal was to avoid using dynamic sql then you are sort of stuck. Bind peeking in 10g could lead to plans using an index based on what it peeked. In 11g the optimizer will peek multiple times, this is to help with skewed data, as a side effect it's going to be less helpful in your particular query.
for Oracle "OR" means ignore the index.
try using union
if your goal was to avoid using dynamic sql then you are sort of stuck. Bind peeking in 10g could lead to plans using an index based on what it peeked. In 11g the optimizer will peek multiple times, this is to help with skewed data, as a side effect it's going to be less helpful in your particular query.
for Oracle "OR" means ignore the index.
try using union
or more accurately, union all
select col1, col2
from table
where col3=:var
union all
select col1, col2
from table
where :var=0;
this will produce a plan that says it's doing an index scan AND a full table scan. But... I think you'll find the optimizer at run time will do the right thing.
it did in my test anyway
select col1, col2
from table
where col3=:var
union all
select col1, col2
from table
where :var=0;
this will produce a plan that says it's doing an index scan AND a full table scan. But... I think you'll find the optimizer at run time will do the right thing.
it did in my test anyway
even better would be if the second query was
:var is NULL
instead of :var = 0
why? because even if col3 is nullable, col3 = :var will always be false if :var is NULL and the optimizer can act on that, effectively eliminating the entire first query, which is even faster than doing an index scan
:var is NULL
instead of :var = 0
why? because even if col3 is nullable, col3 = :var will always be false if :var is NULL and the optimizer can act on that, effectively eliminating the entire first query, which is even faster than doing an index scan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The decode trick worked for me. However, I also appreciate sdstuber's original post as it made me realize that this is a bind variable problem and not an issue with constant expressions.
That said:
Oracle is supposed to do bind peeking for this. I've just been unable to figure out the combo.