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@xxxxxxxxxxxxx > >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@xxxxxxxxx] >Sent: 02 August 2004 12:13 PM >To: Oracle-L@xxxxxxxxxxxxx >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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------