RE: SQL Optimizer

From: Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com>
Date: Mon, 10 Sep 2007 18:06:19 +0200
Message-ID: <607D6181D9919041BE792D70EF2AEC48242BCD@LIMENS.sivsa.int>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Bob Robert
Enviado el: lunes, 10 de septiembre de 2007 17:37 Para: oracle-l_at_freelists.org
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

Received on Mon Sep 10 2007 - 11:06:19 CDT