Home » RDBMS Server » Performance Tuning » Tune the SQL Qury (windows, oracle 10g)
Tune the SQL Qury [message #302199] Sun, 24 February 2008 20:20 Go to next message
gentleman777us
Messages: 122
Registered: April 2005
Senior Member
Hello,

I have the following the SQl query it takes for ever to run the query because it is quering the table which has 7 million records.

I would appreciate if someone help me improve the performance of this query by rewriting it or provide any tips to improve the performance.

select weatherid,
(select DECODE (to_char(min(interval_startdt) ,'HH24MI'), '0000', (trunc(min(interval_startdt))-1)+18/24, TRUNC(min(interval_startdt))+18/24) from weather_data_ont where ind = t.ind)interval_startdt,
2035,
1,
(select DECODE (to_char(max(interval_startdt) ,'HH24MI'), '2330', (trunc(max(interval_startdt))+1)+6/24, TRUNC(max(interval_startdt))+6/24) from weather_data_ont where ind = t.ind) interval_enddt,
min(value),
5,
9998,
sysdate
from weather_data_ont t
where cui_tid = 210 and ind is not null
group by weatherid, ind;


Thanks
Re: Tune the SQL Qury [message #302201 is a reply to message #302199] Sun, 24 February 2008 21:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/

Which of the steps outlined in above URL have you tried & what were the results?
Re: Tune the SQL Qury [message #302226 is a reply to message #302201] Sun, 24 February 2008 23:44 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Read this article, especially the section on subquery expressions

Ross Leishman
Previous Topic: Procedure slow in Oracle 10g
Next Topic: Effect of size of composite primary key on IOT performance.
Goto Forum:
  


Current Time: Sat Dec 10 05:05:06 CST 2016

Total time taken to generate the page: 0.10230 seconds