RE: SQL Optimizer

From: Koppelaars, Toon <T.Koppelaars_at_centraal.boekhuis.nl>
Date: Mon, 10 Sep 2007 21:04:18 +0200
Message-ID: <1247DEDC2684644C93827EB6FDF47F9A01C1374A@SRVEVS1.boekhuis.nl>

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

Received on Mon Sep 10 2007 - 14:04:18 CDT