RE: SQL Optimizer

Hi Bob,

If you use sysdate (-/+) <number> in the predicate side, the optimizer
assigns a default selectivity estimate (5% if I recall) to this
predicate , since it considers the "sysdate +/n <number>" as a bind
variable, and has to assing this default.  

Check the computed card on this predicates. I cannot explain what
Modified Query2 works well (maybe a trick 
of the " - 0" )??

regards,

alvaro




-----Mensaje original-----
De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
En nombre de Bob Robert
Enviado el: lunes, 10 de septiembre de 2007 17:37
Para: oracle-l@xxxxxxxxxxxxx
Asunto: SQL Optimizer

Could you please comment on the following SQL queries between Original
SQL and Modified SQL. It seems that modified SQL's are perfoming better
but I don't know how exactly Oracle optimizer works.

Original Query1:
 SELECT * 
  FROM orders  
 WHERE orders_updt_dtm BETWEEN sysdate - 7 AND sysdate
/

Modified Query1:
SELECT *
  FROM orders
WHERE 7 >= sysdate - orders_last_updt_dtm 
/

Original Query2:
SELECT *
  FROM sales
WHERE sales_updt_dtm BETWEEN sysdate - 7 AND sysdate
/

Modified Query2:
SELECT *
  FROM sales
 WHERE sales_updt_dtm >= sysdate - 7 - 0 
   AND sales_updt_dtm <= sysdate - 0
/

Thanks,
Bob


       
________________________________________________________________________
____________
Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: