RE: SQL Tunning

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 3 Aug 2004 12:21:11 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKGEEEFCAA.mwf@rsiz.com>

If I knew from your query exactly which records you wanted, this would be fairly transparent.

I'm guessing you want want anything modified yesterday.

You want to avoid having to have a function-based index on last_update_date (sounds right to me).

So you want somthing like:

where last_update_date >= trunc(sysdate-1) and last_update_date < trunc(sysdate)

Right?

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wes Brooks Sent: Monday, August 02, 2004 6:13 AM
To: Oracle-L_at_freelists.org
Subject: SQL Tunning

Hello expert,

I have a table with 40 millions records and the last update date is indexed. But when we use the
following where clause, it takes forever to run the report.

   WHERE TRUNC(last_update_date) = TRUNC(SYSDATE - 1)

How to improve the performance? Do I need to create a new index field on the table with TRUNC(last_update_date)?

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Tue Aug 03 2004 - 11:17:40 CDT