Knowledge on Nested loops , Hash joins and Merge joins

Hi ,

I need to know the situations when oracle uses Nested Joins , Merge Joins and Hash Joins  and also is it possible to make oracle use hash join instead of an nested join.
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can't answer the first part but the second part, you can tell Oracle to use a hash with the USE_HASH hint.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF51108

Note:
That is only a 'hint'.  Oracle can still choose to not follow the hint.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
actually  hints, despite their name, are directives.

however - the rules for when a hint will be illegal are not well documented,  so it might look like you're being ignored; but really you told the optimizer to do something it couldn't.


When oracle uses one method over the other is determined by the estimated cost of doing them (rbo only uses nested loops) .

If you're up to it, Jonathan Lewis' book Cost-Based Oracle Fundamentals digs into it with a LOT of depth.   Don't let the term "Fundamentals" confuse you.  It's not light reading of intro material.

It's "fundamentals" like quantum particles are "fundamental" to physics.
0
Mark GeerlingsDatabase AdministratorCommented:
All of these join options are good sometimes.  None of these are always bad.  It depends on the numbers of records involved in the tables, and it usually also depends on what bind variables are provided in the query, and on which columns are indexed.  It also depends a bit on your: server memory,  your SGA size and your other SGA settings.

Rather than focusing on a general discussion of the pros and cons of one of these options compared to another, it may be more helpful for you to focus on particular problem queries or programs *AFTER* you are confident that your Oracle init parameters are appropriate for your: server and storage hardware; your data base size, your application, your number of users, etc. and that your table statistics are up-to-date and getting recalculated at regular intervals.

If your init values are not optimum, or if your table statistics are not up-to-date, don't waste time trying to force one particular join option over another.
0
sam_2012Author Commented:
Thanks for all the suggestions. I have brought a copy of cost based optimiser fundamentals from Jonathan lewis , going thru it . Also , modified the join condition on the tables , now it is not using the nested loops but hash joins.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.