How the Query Optimizer Chooses Execution Plans for Joins

In order to know various types of joins search inside my blog.
In a join, one row set is called inner, and the other is called outer.
To choose an execution plan for a join statement, the optimizer must make these interrelated decisions.

1)Access Paths
2)Join Method: To join each pair of row sources, Oracle must perform a join operation. Join methods include nested loop, sort merge, cartesian, and hash joins.
3)Join Order: To execute a statement that joins more than two tables, Oracle joins two of the tables and then joins the resulting row source to the next table. This process is continued until all tables are joined into the result.

During choosing an execution plan the query optimizer considers the following:

The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.

With the query optimizer, the optimizer generates a set of execution plans, according to possible join orders, join methods, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in the following ways:


•The cost of a nested loops operation is based on the cost of reading each selected row of the outer table and each of its matching rows of the inner table into memory. The optimizer estimates these costs using the statistics in the data dictionary.

•The cost of a sort merge join is based largely on the cost of reading all the sources into memory and sorting them.

•The cost of a hash join is based largely on the cost of building a hash table on one of the input sides to the join and using the rows from the other of the join to probe it.

•A smaller sort area size(That is settings of PGA_AGGREGATE_TARGET) is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area.

•A larger multiblock read count is likely to decrease the cost for a sort merge join in relation to a nested loop join.