Home » RDBMS Server » Performance Tuning » how can i execute this query faster (Oracle 10g R1)
icon5.gif  how can i execute this query faster [message #313692] Mon, 14 April 2008 03:54 Go to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
select /*+ FIRST_ROWS(100)*/
file_no,master_bl,container,
vesl_name,load_code,disch_code,eta_date
from imp_file_cont
where eta_date between to_date(sysdate-360)
and to_date(sysdate) AND STATUS = 'O'
and substr(direction,2,1)='A'.


I m having this sql query is it is
taking about 2 mins to execute.
It has nearly about 10,000 records.

Can anybody please help me out to
reduce the execution time.
Or suggest any other method to optimize
this query.Please.

Thanks & Regards.

[Updated on: Mon, 14 April 2008 03:57]

Report message to a moderator

Re: how can i execute this query faster [message #313698 is a reply to message #313692] Mon, 14 April 2008 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Oracle SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: how can i execute this query faster [message #313852 is a reply to message #313698] Mon, 14 April 2008 22:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
CREATE INDEX random_advice_from_internet 
ON imp_file_cont (status, substr(direction,2,1), eta_date);


Given the depth of insight you have provided on the requirement, I doubt you'd appreciate a long drawn-out explanation of this tuning approach.

Maybe it's faster, maybe it's not.
Maybe it'll break your system, maybe it won't.

I suppose it depends on how confident you are about taking random advice from the internet.

Ross Leishman
Re: how can i execute this query faster [message #313860 is a reply to message #313852] Mon, 14 April 2008 23:24 Go to previous messageGo to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
Can u Please explain to me in brief about random_advice_from_internet?
This is simply ou have given a name to the
index or does it mean something else?
I have already created an index on
status,direction,file_no.
Is this ok?
Or should i make index as U have said?

Thanks & Regards.
Re: how can i execute this query faster [message #314825 is a reply to message #313860] Thu, 17 April 2008 23:39 Go to previous message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

random_advice_from_internet is an index name he simply given....

follow the way he guided you.it will help uou

Gopu
Previous Topic: Enabling Trace file generation.
Next Topic: Rosco's Performance Tuning Guide
Goto Forum:
  


Current Time: Thu Dec 08 20:34:27 CST 2016

Total time taken to generate the page: 0.05773 seconds