Home » RDBMS Server » Performance Tuning » Whats the difference between these two queries ? - tunning purspective (oracle 9.0.2)
Whats the difference between these two queries ? - tunning purspective [message #302483] Mon, 25 February 2008 20:53 Go to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Whats the difference between these two queries ?
I have huge amount of data for each table.
Its takeing such a long time (>5-6hrs). here whice one is fast / do we have any other option there apart from listed here....

and this is i am going to insert into table....

QUERY 1:

SELECT --<< USING INDEX >>
field1, field2, field3, sum( case when field4 in (1,2) then 1 when field4 in (3,4) then -1 else 0 end)
FROM
tab1 inner join tab2 on condition1 inner join tab3 on condition2 inner join tab4 on conditon3
WHERE
condition4..10 and
GROUP BY
field1, field2,field3
HAVING
sum( case when field4 in (1,2) then 1 when field4 in (3,4) then -1 else 0 end) <> 0;


QUERY 2:

SELECT --<< USING INDEX >>
field1, field2, field3, sum( decode(field4, 1, 1, 2, 1, 3, -1, 4, -1 ,0))
FROM
tab1, tab2, tab3, tab4
WHERE
condition1 and
condition2 and
condition3 and
condition4..10
GROUP BY
field1, field2,field3
HAVING
sum( decode(field4, 1, 1, 2, 1, 3, -1, 4, -1 ,0)) <> 0;
Re: Whats the difference between these two queries ? - tunning purspective [message #302492 is a reply to message #302483] Mon, 25 February 2008 22:59 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you are asking whether DECODE is faster than CASE - or vice versa - then you need not worry. By far the greatest cost in such a query is the IO required to retrieve the raw data, closely followed by the SORT required to satisfy the GROUP BY.

The cost of a scalar function like CASE or GROUP BY is way, way, way down the list. Over several hours you will notice NO difference.

If you want to tune the statement itself, post the full SQL Trace / Tkprof output.

Ross Leishman
Previous Topic: Oracle Slows down.
Next Topic: Index
Goto Forum:
  


Current Time: Sat Dec 03 12:04:13 CST 2016

Total time taken to generate the page: 0.23264 seconds