Home » RDBMS Server » Performance Tuning » How Oracle optimizer choose joins (hash, Merge & nested loop join)
How Oracle optimizer choose joins (hash, Merge & nested loop join) [message #568987] Thu, 18 October 2012 07:03 Go to next message
Messages: 6
Registered: May 2007
Location: Mumbai
Junior Member
Could you please someone help me to understand the Oracle optimizer joins concept? I want to know how the Oracle optimizer choose joins and apply them while executing the query. So that I will insure about optimizer join before writting any query.
Re: How Oracle optimizer choose joins (hash, Merge & nested loop join) [message #568993 is a reply to message #568987] Thu, 18 October 2012 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Senior Member
Account Moderator
Just take care of writing your queries in good SQL following the relational theory and let the optimizer does its work, 99.99% of the time you have nothing more to do.

If you want to know more then you can read:
Database Concepts
Database Performance Tuning Guide

Re: How Oracle optimizer choose joins (hash, Merge & nested loop join) [message #569577 is a reply to message #568993] Mon, 29 October 2012 22:56 Go to previous message
Kevin Meade
Messages: 1970
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel is right on the money. Your first priority should be to write a correct and easy to understand query. The optimizer will do the rest for you.

But since you asked, here is a little information about it. I assume you know what a HASH JOIN is and a NESTED LOOPS JOIN etc. This is to say you know the basics of query construction for Oracle? Michel provided reading for if you don't.

In short, Oracle applies some basic rules of thumb against the query text you supply in order to rewrite it into a different variation that can be better optimized.
Once it has this rewrite, it costs out different plans and selects the plan with the lowest cost.
Cost is an Oracle mashup number based on estimated individual costs added together of I/O, CPU, (and depending upon database version NETWORK / MEMORY CONSUMED / SYSTEM RESOURCE SATURATIONS).

Here is the important part...
For Oracle to cost correctly, you need to provide the optimizer with the right information. You do this by:

1) using a good relational design (3rd normal form data model, constraint definitions, proper data types and nullability)
2) doing the right pre-maintenance on your system (smart statistics collection, use of cpu costing, well reasoned database parameter settings)
3) writing a query that correctly defines the result you want

If you do these three things then only one query in one thousand will be a problem and most of them will be a problem only because of some optimizer limitation or special situation and there will almost always be a database feature you can exploit to fix their performance.

As for joins, here is a very simplified explanation:

In Oracle we mostly concern ourselves with two types of joins: 1) NESTED LOOPS JOIN and 2) HASH JOIN. HASH JOIN comes in two parts: 1) setup and 2) join. NESTED LOOPS JOIN has only one part, 1) join. HASH JOIN join part is almost always more efficient then NESTED LOOPS JOIN join part. But the setup and overhead of HASH JOIN means there is extra cost. As a result, Oracle will usually do a NESTED LOOPS JOIN for a small set of driving rows because this generally makes the NESTED LOOPS JOIN less expensive than a HASH JOIN because of the setup overhead of the HASH JOIN. But after a certain number of rows, HASH JOIN becomes way more efficient inspite of it setup overhead.

NESTED LOOPS JOIN requires very little memory.
HASH JOIN requires lots of memory.

This is another clue that NESTED LOOPS JOIN is much better suited for small joins whereas HASH JOIN is better at large joins.

Remember, I said the above was a vey simple explanation. It leaves out many many details and I am trying to avoid controversy where not everyone agrees on specific opinions.


[Updated on: Mon, 29 October 2012 23:01]

Report message to a moderator

Previous Topic: Proper index is not used ...
Next Topic: How tune the following query?
Goto Forum:

Current Time: Fri Dec 26 20:24:21 CST 2014

Total time taken to generate the page: 0.10242 seconds