As
we know, Oracle honors the hints as long as they are properly specified. The
catch phrase here is "properly specified." If you don't specify the
hints properly, Oracle simply ignores the hints without any errors or warnings
which can be quite frustrating. We all have our favorite stories about hints
not being honored by Oracle. We all have tried and tried so many permutations
and combinations to make the hints work, but Oracle somehow stubbornly ignores
our hints. But that's not Oracle's fault! You need to specify the hints
properly. Otherwise, Oracle will simply ignore them. The following are few
useful tips that would help you specify hints properly.
Useful
Tips
- Syntax - You must specify hints within /*+ <hint> */
- Case-insensitive - Hints are not case sensitive so you don't need to worry about upper or lower case.
- Incorrect names - If names of the tables or indexes are not correct in the hints or have typos in them, Oracle will simply ignore the hints.
- Availability of access path - If you specify a hint for an access path which is not available, then Oracle simply ignores the hint. For example, if you specify an index hint, but there are no indexes on the table, then Oracle will simply ignore the hint.
- Join hints - Inner table and outer table must be specified properly. Otherwise hints may not work.
How
to correctly specify Nested Loop Join Hint
If
you want Oracle to use nested loop join method, then you need to specify the
hint as /*+ leading(outer_table) use_nl
(inner_table) */. You may be fine with only use_nl (inner_table) hint, but to leave nothing to
doubt, you should specify leading (outer_table) hint as well. Let's take an
example.
As
shown below, the un-hinted execution plan uses hash join.
SELECT *
FROM T1, T2
WHERE T1.ID = T2.T1_ID
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | HASH JOIN |
|
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE
ACCESS FULL| T2 |
-----------------------------------
If you need nested loop join method on tables t1 and t2,
then specify the hint as follows. Note that T1 is the inner table or row source
and T2 is the outer table or row source.
SELECT /*+ leading(t2) use_nl(t1) */ *
FROM T1, T2
WHERE T1.ID = T2.T1_ID
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | NESTED LOOPS |
|
| 2 | TABLE ACCESS FULL| T2 |
| 3 | TABLE ACCESS FULL| T1 |
-----------------------------------
If you need a nested loop join method with T2 as inner table
and T1 as outer table, then specify hint as follows:
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM T1, T2
WHERE T1.ID = T2.T1_ID
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | NESTED LOOPS |
|
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
How
to correctly specify Nested Loop (Semi) Join Hint - EXISTS Query
If
you want to force nested loop (semi) join method, then you need to specify /*+
use_nl (inner_table) */ in the sub-query. Let's take an example. The below is
un-hinted execution plan.
SELECT *
FROM T1
WHERE EXISTS ( SELECT 'x'
FROM T2
WHERE
T1.ID = T2.T1_ID )
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | HASH JOIN SEMI |
|
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
To force nested loop (semi) join, please note how to specify
leading and use_nl hints along with appropriate inner_table and outer_table.
SELECT /*+ leading(t1) */ *
FROM T1
WHERE EXISTS ( SELECT /*+ use_nl(t2) */ 'x'
FROM
T2
WHERE
T1.ID = T2.T1_ID )
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | NESTED LOOPS SEMI | |
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
How
to correctly specify Nested Loop (Semi) Join Hint - IN Queries
If you have a SQL with IN clause, as shown below, then
use_nl hint must be specified within the inner query as shown below. Leading
hint is specified in the outer query. The first example shows un-hinted
statement while the second example shows hinted statement.
SELECT *
FROM t1
WHERE id IN ( SELECT t1_id FROM t2 )
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | HASH JOIN SEMI |
|
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
SELECT /*+ leading(t1) */ *
FROM t1
WHERE id IN ( SELECT /*+ use_nl(t2) */ t1_id
FROM t2
)
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | NESTED LOOPS SEMI | |
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
How
to correctly specify Nested Loop (anti) Join Hint - NOT EXISTS Queries
The specifications for nested loop (anti) join hint are
similar to nested loop (semi) join hint specifications. You must specify use_nl
hint in the sub-query with inner table. The leading hint should be specified in
the outer query. The first example shows un-hinted statement while the second
example shows hinted statement.
SELECT *
FROM T1
WHERE NOT EXISTS ( SELECT 'x'
FROM T2
WHERE T1.ID = T2.T1_ID )
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | HASH JOIN ANTI |
|
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
SELECT /*+
leading(t1) */ *
FROM T1
WHERE NOT EXISTS ( SELECT /*+ use_nl(t2) */ 'x'
FROM T2
WHERE T1.ID = T2.T1_ID )
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT
STATEMENT | |
| 1 | NESTED LOOPS ANTI | |
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
