Home » RDBMS Server » Performance Tuning » Hash join vs NL join
Hash join vs NL join [message #264331] Mon, 03 September 2007 01:01 Go to next message
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.
Re: Hash join vs NL join [message #264334 is a reply to message #264331] Mon, 03 September 2007 01:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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?
Re: Hash join vs NL join [message #264335 is a reply to message #264331] Mon, 03 September 2007 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 Go to previous messageGo to next message
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.
Re: Hash join vs NL join [message #264382 is a reply to message #264365] Mon, 03 September 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Hash join vs NL join [message #264389 is a reply to message #264382] Mon, 03 September 2007 03:07 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Hash join vs NL join [message #264533 is a reply to message #264389] Mon, 03 September 2007 12:00 Go to previous message
beetel
Messages: 96
Registered: April 2007
Member
Thanks, guys. I'm learning a lot from you.
Previous Topic: Database is slow.
Next Topic: shared memory realm already exists
Goto Forum:
  


Current Time: Sun Dec 04 10:55:59 CST 2016

Total time taken to generate the page: 0.07276 seconds