Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Tunning

RE: SQL Tunning

From: <J.Velikanovs_at_alise.lv>
Date: Mon, 2 Aug 2004 13:20:06 +0300
Message-ID: <OF8E7C1853.3D9AEAEF-ONC2256EE4.00384FBB-C2256EE4.00398555@alise.lv>


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_at_wesbank.co.za
>
>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_at_yahoo.com]
>Sent: 02 August 2004 12:13 PM
>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
-----------------------------------------------------------------
Received on Mon Aug 02 2004 - 05:36:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US