Use of Join Order Hints: Ordered and Leading in Oracle

Use of Join Order Hints: Ordered and Leading in Oracle

Following Hints are used for Changing the join order in the Execution plans of SQL Query.

The ORDERED hint: Use the order from the list of tables in the FROM clause
The LEADING hint: Oracle recommends to use the LEADING Hint, as the LEADING hint has more option to choose the order.

Oracle recommends, where possible, to use the LEADING hint over the ORDERED hint, as the
LEADING hint has more versatility built in. When specifying the ORDERED hint, you specify the join order
from the list of tables in the FROM clause, while with the LEADING hint, you specify the join order within the
hint itself.

You have a performance issue with a query which are joining many tables, and the Oracle optimizer is not choosing the correct order which causing the performance issue.
So, Ordered hint and Leading hint have option to make SQL Query to choose the different execution plan.

Following are the examples HR Schema present in the Oracle Database:

Simple Query Execution plan:

SELECT First_name, department_name FROM hr.employees JOIN hr.departments USING(department_id);

----------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     6  (34)| 00:00:01 |
|   1 |  MERGE JOIN                  |                   |     6  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK        |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                   |     4  (50)| 00:00:01 |
|   5 |    VIEW                      | index$_join$_001  |     3  (34)| 00:00:01 |
|*  6 |     HASH JOIN                |                   |            |          |
|   7 |      INDEX FAST FULL SCAN    | EMP_DEPARTMENT_IX |     1   (0)| 00:00:01 |
|   8 |      INDEX FAST FULL SCAN    | EMP_NAME_IX       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------


Query used by Ordered Hints
It will change the execution plan by changing the order of tables used in joins condition as shown below:

SELECT /*+ ordered */ First_name, department_name FROM hr.employees JOIN hr.departments USING(department_id);

-----------------------------------------------------------------------------
| Id  | Operation               | Name              | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN              |                   |     6  (17)| 00:00:01 |
|   2 |   VIEW                  | index$_join$_001  |     3  (34)| 00:00:01 |
|*  3 |    HASH JOIN            |                   |            |          |
|   4 |     INDEX FAST FULL SCAN| EMP_DEPARTMENT_IX |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| EMP_NAME_IX       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL     | DEPARTMENTS       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Query used the Leading hints
With leading you can change the order as your choose. Below Both example will show the results:

Example 1:

SELECT /*+ leading(departments, employees) */ First_name, department_name FROM hr.employees JOIN hr.departments USING(department_id);

----------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     6  (34)| 00:00:01 |
|   1 |  MERGE JOIN                  |                   |     6  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK        |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                   |     4  (50)| 00:00:01 |
|   5 |    VIEW                      | index$_join$_001  |     3  (34)| 00:00:01 |
|*  6 |     HASH JOIN                |                   |            |          |
|   7 |      INDEX FAST FULL SCAN    | EMP_DEPARTMENT_IX |     1   (0)| 00:00:01 |
|   8 |      INDEX FAST FULL SCAN    | EMP_NAME_IX       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

 
Example 2:

SELECT /*+ leading(employees , departments) */ First_name, department_name FROM hr.employees JOIN hr.departments USING(department_id);

-----------------------------------------------------------------------------
| Id  | Operation               | Name              | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN              |                   |     6  (17)| 00:00:01 |
|   2 |   VIEW                  | index$_join$_001  |     3  (34)| 00:00:01 |
|*  3 |    HASH JOIN            |                   |            |          |
|   4 |     INDEX FAST FULL SCAN| EMP_DEPARTMENT_IX |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| EMP_NAME_IX       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL     | DEPARTMENTS       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s