Tag Archives: hash

Join Method in explain plan of SQL Query

Join Method in explain plan of SQL Query

Optimizer is using the wrong join type, need to change the join method for increase the speed of the SQL Queries.
You can override the join type by placing the appropriate hint in the query. You can test the SQL Query performance by changing its Join Method like Nested loop to hash join etc.

Three Join Methods:
NESTED LOOPS
HASH
SORT MERGE

NESTED LOOPS Join
Use the USE_NL hint for using in SQL Query.
The nested loops join is consider best for joining small tables.
In a nested loops join, one table is known as driving table(outer table). For each row of the outer, each row in the inner table is searched for matching rows.

In the Example: Employee table is outer table and Department table is inner table:

SELECT /*+ use_nl(emp, dept) */ FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES emp JOIN DEPARTMENTS dept USING (deptno);

------------------------------------------------------
| Id | Operation                       | Name        |
------------------------------------------------------
| 0 | SELECT STATEMENT                 |             |
| 1 | NESTED LOOPS                     |             |
| 2 | NESTED LOOPS                     |             | 
| 3 | TABLE ACCESS FULL                | DEPARTMENTS |
| 4 | INDEX RANGE SCAN                 | EMP_DEP_IDX |
| 5 | TABLE ACCESS BY INDEX ROWID      | EMPLOYEES   |
------------------------------------------------------

HASH Join
Hash joins are used for joining large amounts of data or larger table. The smaller of the two tables is used by the optimizer to build a hash table on the join key between the two tables.
Use the USE_HASH hint for using in SQL Query.
In the example, the DEPT table is the smaller tablev and used to build the hash table:

SELECT /*+ use_hash(emp , dept) */ first_name, department_name
FROM employees emp JOIN departments dept USING (deptno);

-------------------------------------
| Id | Operation        | Name      |
-------------------------------------
| 0 | SELECT STATEMENT  |           |
| 1 | HASH JOIN         |           |
| 2 | TABLE ACCESS FULL | DEPT      |
| 3 | TABLE ACCESS FULL | EMPLOYEES |
-------------------------------------

SORT MERGE Join
Sort merge joins are also used to join a large data. Sort merge join is used when the join condition between the tables is not an equijoin.
Use the USE_MERGE hint for using in SQL Query.
In the following example, the input data from both tables is sorted on the join key, and then merged together.


select /*+ use_merge(emp, dept) */ first_name , emp.department_id
from hr.employees emp , hr.departments dept
where emp.department_id = dept.department_id and dept. department_id 20;

----------------------------------------------------
| Id | Operation                  | Name           |
----------------------------------------------------
| 0 | SELECT STATEMENT            |                |
| 1 | MERGE JOIN                  |                |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |
| 3 | INDEX FULL SCAN             | DEPARTMENTS_PK |
| 4 | SORT JOIN                   |                |
| 5 | TABLE ACCESS FULL           | EMPLOYEES      |
----------------------------------------------------

Advertisements