RE: SQL Optimizer

Bob,

Your modified Query1 is not the same (semantically) as your original Query1: 
that is, it poses a different question to the DBMS. It also retrieves orders 
whose ...dtm is in the future. Now if you have some integrity constraint 
dictating that there never are such orders, then they would (semantically) be 
the same again... :-). 

And as far as I know (at least in 9i and upwards), the optimizer will rewrite 
your Query2 into the modified Query2. There is no need for you to do that 
(apart from the "-0", what is that for?). There should be no performance 
difference.

Toon

PS. I just noticed your email id. We're talking Oracle here, right?

=======================
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
/

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


Other related posts: