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  |
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  |
rleishman
Messages: 3728
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
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:00:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
