I would try:
1. to rewrite
WHERE last_update_date TRUNC(SYSDATE - 2) and TRUNC(SYSDATE - 1)
2. to get execution plan
3. to learn a litle bit more, how Oracle RDBMS work ;)
.
I definitely wouldn�t create function-based index for this purpose.
>Create inondex xxx on xxxx ( trunc(last_update_date)) tablespace yyyyyy;
.
Form first look I would suggest to Wes RTFM.
And URL below is good point to start:
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10755/toc.htm
Best regards,
On 02.08.2004 13:19:13 oracle-l-bounce wrote:
>Build a functional index on the last_update_date column, it is probably
>ignoring it, doing a tablescan
>
>Create index xxx on xxxx ( trunc(last_update_date)) tablespace yyyyyy;
>
>
>George
>__ ____ ____ ____ ____ ____ ____ ____ ____ _____
>George Leonard
>Oracle Database Administrator
>New Dawn Technologies @ Wesbank
>E-mail:gleonard@(protected)
>
>You Have The Obligation to Inform One Honestly of the risk, And As a
Person
>You Are Committed to Educate Yourself to the Total Risk In Any Activity!
>Once Informed & Totally Aware of the Risk,
>Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
>
>
>-- --Original Message-- --
>From: Wes Brooks [mailto:wes_brooks@(protected)]
>Sent: 02 August 2004 12:13 PM
>To: Oracle-L@(protected)
>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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
