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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l