Oracle hint ordered and USE_NL

Hint:ordered and Use_nl

Ordered good understanding, that is, according to the order from the following table join, from left to right, the table on the left to do the driver table
Use_nl (T1,T2): Represents a nested loop join when associating with a table T1, T2, and does not allow the optimizer to determine who is the driver table or who is the driven table
Use_nl (), first look at how Oracle Doc says:

In this statement, the USE_NL hint explicitly chooses a nested loops joins with the Customers table as the inner table:
SELECT/*+ ORDERED use_nl (Customers) to get first row faster */
Accounts.balance, Customers.last_name, Customers.first_name
From accounts, customers
WHERE accounts.customer_id = customers.customer_id;

Customers as inner table, that is to say, as a driver. Driver Table called outer table
If the specified table is a outer table (driver table), the optimizer ignores this hint
If you do not want to force it as inner table, you can match the ordered parameter
The oradered represents the left-to-right join, the left table as the driver table, 3 or more of the 3 most useful, based on the order of the From following table
That is, use_nl if only one table name is used as the parameter, the table is the driver table
If you have more than 2 parameters, Oracle does not indicate which is the driver table in Use_nl (A, B), so we often use ordered or full () or index () to reinforce our goal.

Here are the tests:

[SQL]
[Email protected]> Select first_name,departments.department_id from Employees,departments where employees.department_id=departments.department_id;

Execution Plan
----------------------------------------------------------
Plan Hash value:169719308

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 1484 | 3 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | 106 | 1484 | 3 (0) | 00:00:01 |
| 2 | TABLE ACCESS full| EMPLOYEES | 107 | 1070 | 3 (0) | 00:00:01 |
|* 3 | INDEX UNIQUE scan| DEPT_ID_PK | 1 | 4 | 0 (0) | 00:00:01 |
---------------------------------------------------------------------------------

Here the optimizer chooses employees as the driver table because there is an index on the departments and the index is exactly on the connection column
[SQL]
[Email protected]> Select/*+ USE_NL (Employees) */first_name,departments.department_id from employees,departments where employees.department_id=departments.department_id;

Execution Plan
----------------------------------------------------------
Plan Hash value:169719308

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 1484 | 3 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | 106 | 1484 | 3 (0) | 00:00:01 |
| 2 | TABLE ACCESS full| EMPLOYEES | 107 | 1070 | 3 (0) | 00:00:01 |
|* 3 | INDEX UNIQUE scan| DEPT_ID_PK | 1 | 4 | 0 (0) | 00:00:01 |
---------------------------------------------------------------------------------

Because employees is used as the driver table, the optimizer ignores the hint hint
[SQL]
[Email protected]> Select/*+ ordered USE_NL (employees) */FIRST_NAME,DEPARTMENTS.DEPARTMENT_ID from departments, Employees where employees.department_id=departments.department_id;

Execution Plan
----------------------------------------------------------
Plan Hash value:2677871237

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 1484 | 8 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| EMPLOYEES | 4 | 40 | 1 (0) | 00:00:01 |
| 2 | NESTED LOOPS | | 106 | 1484 | 8 (0) | 00:00:01 |
| 3 | INDEX Full SCAN | DEPT_ID_PK | 27 | 108 | 1 (0) | 00:00:01 |
|* 4 | INDEX RANGE SCAN | Emp_department_ix | 10 | | 0 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------------

Now it's departments as the driver table.

Oracle hint ordered and USE_NL