OraFAQ Forum: Performance Tuning » Hash join vs NL join

Home » RDBMS Server » Performance Tuning » Hash join vs NL join

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

Hash join vs NL join [message #264331]

Mon, 03 September 2007 01:01

beetel
Messages: 96
Registered: April 2007

Member

When do I use Hash join and when do I use Nested loop join? Is there a criterion in terms of table size, indexes, etc? Please help.

Report message to a moderator

Re: Hash join vs NL join [message #264334 is a reply to message #264331]

Mon, 03 September 2007 01:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>When do I use Hash join and when do I use Nested loop join? Is there a criterion in terms of table size, indexes, etc?
I give up.
when do YOU use each?

Report message to a moderator

Re: Hash join vs NL join [message #264335 is a reply to message #264331]

Mon, 03 September 2007 01:07

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You don't use/choose this, Oracle chooses it for you.
If you ask when Oracle choose one or the other one.
Yes, it depends on your query (what you ask), tables size, indexes, area sizes (memory you can use for hash arrays)...

Regards
Michel

[Updated on: Mon, 03 September 2007 01:07]

Report message to a moderator

Re: Hash join vs NL join [message #264365 is a reply to message #264335]

Mon, 03 September 2007 02:22

beetel
Messages: 96
Registered: April 2007

Member

I meant when is it advisable to use USE_HASH or USE_NL hints? Or better, when does Oracle choose hash over nl or vice-versa?.. thanks.

Report message to a moderator

Re: Hash join vs NL join [message #264382 is a reply to message #264365]

Mon, 03 September 2007 02:41

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you don't well know the optimizer don't use hint.
The only ones that are safe are: all_rows, first_rows_n.

Regards
Michel

Report message to a moderator

Re: Hash join vs NL join [message #264389 is a reply to message #264382]

Mon, 03 September 2007 03:07

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Its a complex topic. I wrote down my thoughts on it here a while ago. This page is part of a greater SQL Tuning Guide that might make more sense if you started from the beginning.

Ross Leishman

Report message to a moderator

Re: Hash join vs NL join [message #264533 is a reply to message #264389]

Mon, 03 September 2007 12:00

beetel
Messages: 96
Registered: April 2007

Member

Thanks, guys. I'm learning a lot from you.

Report message to a moderator

Previous Topic:	Database is slow.
Next Topic:	shared memory realm already exists

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:39:59 CDT 2024