Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

RE: SQL Tunning

From: J.Velikanovs@xxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Date: Mon, 2 Aug 2004 13:20:06 +0300

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

References:

RE: SQL Tunning
From: Leonard, George

RE: SQL Tunning

Other related posts: