Sreenivas -- Thanks for the question regarding "Will "AND 1=1" in SELECT improve performance of SQL at all??????", version 8.1.7

Followup   January 10, 2006 - 10am Central time zone:

search this site for 

bind variable peeking


to get an idea of what I was demonstrating.  The "where 1=1" was a 'red herring'.  I was trying to 
demonstrate that the adding of 1=1 did not make the query go "faster", it was the fact we 
accidently hard parsed it with different bind variables and the optimizer came up with an entirely 
different plan - meaning, you have a bind variable peeking issue that you erroneously believe you 
have fixed with 1=1 (until the next time the 1=1 query gets hardparsed itself!  I can "prove" (do 
not take that seriously) that adding 1=1 will decrease performance, watch 


(same setup as before):

ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec :n := 1;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly statistics
 
ops$tkyte@ORA9IR2> select * from t where id = :n;
1 row selected.
 
 
Statistics
----------------------------------------------------------
          4  consistent gets
          1  rows processed
 
 
ops$tkyte@ORA9IR2> exec :n := 99;
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t where id = :n AND 1=1;
28410 rows selected.
 
 
Statistics
----------------------------------------------------------
       2267  consistent gets
      28410  rows processed
 
ops$tkyte@ORA9IR2> exec :n := 1;
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t where id = :n;
1 row selected.
 
 
Statistics
----------------------------------------------------------
          4  consistent gets
          1  rows processed
 
ops$tkyte@ORA9IR2> select * from t where id = :n AND 1=1;
1 row selected.
 
 
Statistics
----------------------------------------------------------
        406  consistent gets
          1  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off

see, now 1=1 is "bad" - no, not really, it was bind variable peeking coming up with different 
plans depending on what got parsed first... 
 

Followup   January 10, 2006 - 1pm Central time zone:

that is very different.

adding where 1=0 is very different to - that makes queries go really fast. 

Followup   January 12, 2006 - 10am Central time zone:

You are free to disagree, but rownum is a set operation.  It is "special", it is "magic". It must 
be done deterministically.  Adding rownum to a query fundementally changes it.

In this very narrow case, it just so happens that

1=1
rownum>0

will have the same net effect, but then again:

select * 
  from t
 where dt = (select max(dt) from t t2 where t2.id = t.id);

select *
   from (select t, max(dt) over (partition by id) max_dt from t)
 where dt = max_dt;

return the same results - but are very different. 

Followup   April 18, 2006 - 2pm Central time zone:

option 0) do not have statistics that would cause bind variable peeking to do anything.  eg: in 
your transactional system - where binds are somewhat "the rule" - do you really want histograms, 
are they desirable in that system....

bind variable peeking issues would absolutely be the exception - not the rule.  I would not suggest 
your steps 1 and 2 for every query.  For the exceptions I would consider

a) do not bind that specific column, if you have a Y/N flag for example, or code number check (code 
in (1,2,3,4,5) ) - and the flag/code are skewed - you have so unique values, you need not bind that 
one necessarily.  

b) don't generate the statistics that cause the plans to change (eg: histograms, they are expensive 
to compute in the first place)

c) use your domain knownledge of the data - if the user inputs a date within the last 30 days - 
"open this query", else if the date is > 30 days (a different plan would be used) then "open this 
other_query"...

d) resort to cursor_sharing = similar for that query

e) stored outlines