[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Execution Plan Changes with a constant expression in 10gR2

Posted on 2011-10-28
10
Medium Priority
?
401 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:b_levitt
  • 6
  • 3
10 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 37045912
Oracle does short circuit evaluation, left to right

try this...


where :var=0 or col3=:var ;


also,  that particular syntax  isn't a constant  it's a "bind variable"  which means the optimizer won't know it's value until it's executing
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37045999
position should matter and with the test I've been working with it does not.

That said:
Oracle is supposed to do bind peeking for this.  I've just been unable to figure out the combo.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37046029
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37046062
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37046117
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

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37046246
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




0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37046276
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37046292
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
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1600 total points
ID: 37046775
See it this will work for you.

It's a 'trick' but no more than trying to get around dynamic SQL.

drop table tab1 purge;
create table tab1(col1 number, col2 char(1));

insert into tab1 (select rownum, 'a' from dual connect by level < 10000);
commit;

create index tab1_idx on tab1(col1);

var myvar number;

set autotrace traceonly

exec :myvar := 2;
select col2 from tab1 where col1=decode(:myvar, null, col1, :myvar);

exec :myvar := null;
select col2 from tab1 where col1=decode(:myvar, null, col1, :myvar);

set autotrace off

Open in new window

0
 
LVL 11

Author Closing Comment

by:b_levitt
ID: 37058029
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question