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
Messages: 122
Registered: April 2005
Senior Member

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,
(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,
from weather_data_ont t
where cui_tid = 210 and ind is not null
group by weatherid, ind;

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
Messages: 25531
Registered: January 2009
Location: SoCal
Senior Member

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
Messages: 3727
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: Mon Jul 24 08:01:54 CDT 2017

Total time taken to generate the page: 0.10691 seconds