Home » SQL & PL/SQL » SQL & PL/SQL » costs of joins
costs of joins [message #288725] Tue, 18 December 2007 07:46 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
Hi all,

I just wrote two simple query, one using an inner join and the other using a subquery to retrieve the same results.

Here is my example queries:

Query #1:
select   column_1,
         count(1)
from     tableA
         inner join tableB on tableB.id = tableA.id
group by column_1


Query #2:
select   column_1,
         count(1)
from     tableA
where    tableA.id in (select tableB.id from tableB)
group by column_1


I ran the explain plan and found that Query #2 costs considerably less... so my question is this: are subqueries in these type of scenarios always run faster because there is no joins?

I'm just confused because I always thought that subqueries are more expensive than using inner joins?

Any thoughts or opinions on this?

Thanks,
John.
Re: costs of joins [message #288731 is a reply to message #288725] Tue, 18 December 2007 07:55 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Factors that could change the cost would be how many rows are in each of the tables and whether an index exists on id column.
Re: costs of joins [message #288738 is a reply to message #288725] Tue, 18 December 2007 08:05 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, if you have up to date statistics, optimizer will know which form is cheaper and will transform your query.
As a rule of thumb, if you don't need values from a table, put it in a subquery.

Regards
Michel
Previous Topic: renaming column at run time
Next Topic: PL/SQL Performance
Goto Forum:
  


Current Time: Wed Dec 07 10:59:23 CST 2016

Total time taken to generate the page: 0.12664 seconds