Home » SQL & PL/SQL » SQL & PL/SQL » Need Query tunning Tips for performance (merged 2)
Need Query tunning Tips for performance (merged 2) [message #412066] Tue, 07 July 2009 10:50 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Table : DEF_COM
INDEX : DID (Varchar2)
AAT (Date)
GTS (Date)
PARTITION : AAT
Number of records:999999999

To Pull the hourly data from 2-3 Partition, it is taking 5-9 Secs.

Query:
SELECT * FROM DEF_COM WHERE 
AAT BETWEEN TO_DATE('15-11-2008 00:00:00', 'DD-MM-YYYY H24:MI:SS') AND TO_DATE('15-01-2009 00:00:00', 'DD-MM-YYYY H24:MI:SS')
AND GTS TO_DATE('10-01-2009 00:00:00', 'DD-MM-YYYY H24:MI:SS') AND TO_DATE('10-01-2009 01:00:00', 'DD-MM-YYYY H24:MI:SS')


Is there anyway to improve the query to get the response time below or within 1 sec?
Please suggest.
Re: Need Query tunning Tips for performance (merged 2) [message #412069 is a reply to message #412066] Tue, 07 July 2009 11:00 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Post the correct SQL - that one won't compile.
2) Post an explain plan
3) How many records does the select return?
Re: Need Query tunning Tips for performance (merged 2) [message #412070 is a reply to message #412066] Tue, 07 July 2009 11:08 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Please suggest

Post valid SQL in the future.
Place indexes on AAT & GTS

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Previous Topic: FOR UPDATE WAIT n
Next Topic: Difference between Execute and DBMS_SQL
Goto Forum:
  


Current Time: Thu Dec 08 18:44:44 CST 2016

Total time taken to generate the page: 0.19202 seconds