OraFAQ Forum: Performance Tuning » When to use sub-query and when to use join

Home » RDBMS Server » Performance Tuning » When to use sub-query and when to use join (11g R1, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

When to use sub-query and when to use join [message #486201]

Tue, 14 December 2010 04:53

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

Hi all,

In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.

So when should I use sub-query and when should I go for join?

This is I think subjective, I want all those different thoughts.

regards,
Delna

Report message to a moderator

Re: When to use sub-query and when to use join [message #486209 is a reply to message #486201]

Tue, 14 December 2010 05:06

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Don't care about this, write the query as well and maintainable as possible and let optimizer converts it in the best way.

Regards
Michel

Report message to a moderator

Re: When to use sub-query and when to use join [message #486210 is a reply to message #486209]

Tue, 14 December 2010 05:11

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

Right. Optimizer will select the best way.
But this is not the answer of my question.

regards,
Delna

Report message to a moderator

Re: When to use sub-query and when to use join [message #486211 is a reply to message #486210]

Tue, 14 December 2010 05:17

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The answer is:
Don't care about this, write the query as well and maintainable as possible

Regards
Michel

Report message to a moderator

Re: When to use sub-query and when to use join [message #486216 is a reply to message #486211]

Tue, 14 December 2010 05:45

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

Anyone, who can give better answer?

regards,
Delna

Report message to a moderator

Re: When to use sub-query and when to use join [message #486219 is a reply to message #486216]

Tue, 14 December 2010 05:50

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is bad in my answer?

Regards
Michel

Report message to a moderator

Re: When to use sub-query and when to use join [message #486221 is a reply to message #486216]

Tue, 14 December 2010 05:52

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

You're assuming there is a better answer.
Write it in whichever form is easiest to follow. Change it if it causes a major performance issue.

Report message to a moderator

Re: When to use sub-query and when to use join [message #486264 is a reply to message #486221]

Tue, 14 December 2010 09:45

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Once upon a time I attended a SQL statement tuning class at Oracle's Costa Mesa facility. The course and material were intended for Oracle 8.1.5. The instructor stressed that sub-queries (especially correlated sub-queries) were inherently slow and strongly recommended using joins instead whenever possible. The execution plans for sub-queries, whether in IN clauses or correlated sub-queries showed nested loops and did not use indexes. That was a few versions ago and the optimizer has gotten better. I still tend to prefer join conditions and find such queries easier to read. The following section of the current documentation describes how the optimizer unnests the sub-query to form a join condition to get the better execution plan. I figure why not just use the join condition to begin with?

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF10104

[Updated on: Tue, 14 December 2010 09:54]

Report message to a moderator

Re: When to use sub-query and when to use join [message #486335 is a reply to message #486264]

Wed, 15 December 2010 00:02

delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City

Senior Member

Thanks Barbara mam for your reply.

As you said, in Oracle 8i, "joins were giving better performance than sub-query". Fine.
From the link you provided, Oracle has said that optimizer transforms sub-query into join whenever possible. That means above statement remains true in current version also, because Oracle has to do some extra work for that transformation.

I think, I got the answer.
Then also if anybody wants to add content to this thread then it is most welcome.

regards,
Delna

Report message to a moderator

Re: When to use sub-query and when to use join [message #486346 is a reply to message #486335]

Wed, 15 December 2010 01:31

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

because Oracle has to do some extra work for that transformation.

No, Oracle does the work anyway and then compare the cost of both to know which one is cheaper (roughly describing).

Regards
Michel

Report message to a moderator

Previous Topic:	dbms_lob.append
Next Topic:	dbms_job

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 20 17:05:48 CST 2025