Home » SQL & PL/SQL » SQL & PL/SQL » Which query is better and Why ?
Which query is better and Why ? [message #183778] Sun, 23 July 2006 14:42 Go to next message
Messages: 38
Registered: June 2006
Hi Frnds,

I am having two queries which gives same output.They are as follows.

--Query 1
(select sum(amt) from table a,table b where a.code= b.code
and a.date = c.date
and a.id = c.id) as col1,
(select sum(amt) from table d,table e where d.idno = e.idno
and d.date= c.date
and d.id = c.id ) as col2
from table c
where c.date = '20-feb-06'

This resulted me 1 row but took more time to execute.

Col1 Col2
100.00 2000.00

I have written the same query in the below way using union clause

--Query 2
select sum(col1),sum(col2)
select a.id as col0, sum(amt) as col1,0 as col2 from
table a,table b
where a.code = b.code
and a.date = c.date
group by a.id
union all
select d.id as col0,0 as col1 , sum(amt) as col2 from
table d,table e
where d.idno = e.idno
and a.date = c.date
group by d.id
)x,table c
where x.id =c.id
and c.date = '20-feb-06'

This too gave me the same output and the execution time was very less

col1 col2
1000.00 2000.00

I am using the second query as the execution time is very less
and my question is "Is there any way to tune the first query ? If yes please explain me ."

Hope my question is not so confusing..

Immediate help would be appreciated.

Thanks in Advance,

Re: Which query is better and Why ? [message #183791 is a reply to message #183778] Sun, 23 July 2006 21:06 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In terms of what Oracle has to do to execute these two queries, there is not a lot of difference at face value assuming that the query is driving off table c.

The first query would be FORCED to drive off table c, and then perform nested lookups on the other two sub-queries.

The second query COULD work the same way, but does not need to. The CBO could unnest the UNION ALL, after which it would have a few different execution options. This is probably whats happening, but I can't tell unless you include EXPLAIN PLANs for both queries.

If unnesting is making the second query faster (no guarantees)then there is no way (that I know of) to make the first query just as fast - nested sub-queries in the SELECT clause are forced to nest.

Ross Leishman
Previous Topic: tnsnames.ora
Next Topic: Tracing PL/SQL Code
Goto Forum:

Current Time: Sun May 28 09:33:20 CDT 2017

Total time taken to generate the page: 0.17290 seconds