An optimizer hint is an instruction inside a comment that tells Oracle how to run a statement: use a full scan, use this index, join in this order. Hints are useful for testing plans and for the rare case where the optimizer keeps choosing badly, but they are easy to get wrong, and a wrong hint is ignored without any error message. This guide shows the hint syntax, the mistakes that make hints silently fail, how the Hint Report in DBMS_XPLAN exposes them, and when not to use hints at all.
Syntax
select /*+ hint(table_alias) hint(table_alias index_name) */ column_list from table_name table_alias where ...;
A hint comment starts with /*+, with no space between the asterisk and the plus sign, and must follow the first keyword of the statement directly: SELECT, INSERT, UPDATE, DELETE, or MERGE. Several hints are separated by spaces. When the table has an alias in the FROM clause, the hint must use the alias.
Sample Table
Example:
create table demo_orders ( order_id number constraint demo_orders_pk primary key, customer_id number not null, amount number(10,2) not null); insert into demo_orders select level, mod(level * 7, 5000) + 1, mod(level * 13, 500) + 10 from dual connect by level <= 100000; commit; create index demo_ord_cust_ix on demo_orders (customer_id); exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')
Output:
Table DEMO_ORDERS created. 100,000 rows inserted. Commit complete. Index DEMO_ORD_CUST_IX created. PL/SQL procedure successfully completed.
The plans below use the format BASIC +ROWS +COST +HINT_REPORT. HINT_REPORT adds a section that lists every hint and what happened to it.
The Optimizer's Own Plan
Example:
explain plan for select * from demo_orders o where o.customer_id = 77; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT');
Output:
Explained. PLAN_TABLE_OUTPUT ________________________________________________________________________________________ Plan hash value: 2203928416 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 21 (0)| | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20 | 21 (0)| | 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20 | 1 (0)| ------------------------------------------------------------------------------------- 9 rows selected.
For one customer's 20 orders, the optimizer uses the index, at a cost of 21.
A Hint That Works
Example:
explain plan for select /*+ full(o) */ * from demo_orders o where o.customer_id = 77; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT');
Output:
Explained.
PLAN_TABLE_OUTPUT
______________________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 68 (0)|
| 1 | TABLE ACCESS FULL| DEMO_ORDERS | 20 | 68 (0)|
--------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / "O"@"SEL$1"
- full(o)
15 rows selected.FULL(o) forces a full table scan of the table with the alias O, and the Hint Report lists the hint without an error code, which means it was used. The cost rose from 21 to 68: the hint changed the plan, not for the better.
Mistakes That Make Hints Fail Silently
The statement runs in each of the following cases, but the hint is ignored. Only the plan and the Hint Report show it.
A table name instead of the alias:
Example:
explain plan for select /*+ full(demo_orders) */ * from demo_orders o where o.customer_id = 77; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT');
Output:
Explained.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________
Plan hash value: 2203928416
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 21 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20 | 21 (0)|
| 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20 | 1 (0)|
-------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$1
N - full(demo_orders)
16 rows selected.N means unresolved: once the table has the alias O, the name DEMO_ORDERS does not identify it in a hint.
A misspelled hint:
Example:
explain plan for select /*+ ful(o) */ * from demo_orders o where o.customer_id = 77; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT');
Output:
Explained.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________
Plan hash value: 2203928416
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 21 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20 | 21 (0)|
| 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20 | 1 (0)|
-------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - ful
16 rows selected.E means a syntax error: FUL is not a hint.
A hint that cannot be applied:
Example:
explain plan for select /*+ index(o demo_ord_amount_ix) */ * from demo_orders o where o.customer_id = 77; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT');
Output:
Explained.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________
Plan hash value: 2203928416
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 21 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20 | 21 (0)|
| 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20 | 1 (0)|
-------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / "O"@"SEL$1"
U - index(o demo_ord_amount_ix) / index specified in the hint doesn't exist
16 rows selected.U means unused, and the report even gives the reason: the index named in the hint does not exist. The same happens after an index is renamed or dropped, which is how hints in old code quietly stop working.
A hint in the wrong place:
Example:
explain plan for select * /*+ full(o) */ from demo_orders o where o.customer_id = 77; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT');
Output:
Explained. PLAN_TABLE_OUTPUT ________________________________________________________________________________________ Plan hash value: 2203928416 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 21 (0)| | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20 | 21 (0)| | 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20 | 1 (0)| ------------------------------------------------------------------------------------- 9 rows selected.
There is no Hint Report at all: a hint comment that does not follow SELECT directly is an ordinary comment, so Oracle does not even see a hint.
A Hint That Makes Things Worse
Example:
-- the optimizer's own plan for a fifth of the table ... explain plan for select * from demo_orders o where o.customer_id > 4000; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT'); -- ... and the plan forced by an index hint explain plan for select /*+ index(o demo_ord_cust_ix) */ * from demo_orders o where o.customer_id > 4000; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +HINT_REPORT');
Output:
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20004 | 68 (0)|
| 1 | TABLE ACCESS FULL| DEMO_ORDERS | 20004 | 68 (0)|
--------------------------------------------------------------
8 rows selected.
Explained.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________
Plan hash value: 2203928416
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20004 | 20050 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20004 | 20050 (1)|
| 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20004 | 43 (0)|
-------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / "O"@"SEL$1"
- index(o demo_ord_cust_ix)
16 rows selected.For a fifth of the table, the optimizer chooses a full scan with a cost of 68. The INDEX hint is obeyed, and the cost becomes 20,050, about 300 times more, because each of the 20,004 rows is fetched separately. A hint forces a plan even when the data no longer suits it.
Common Hints
- FULL(alias) and INDEX(alias index_name): the access path for one table.
- LEADING(alias1 alias2): the join order. USE_NL, USE_HASH, and USE_MERGE: the join method for a table, usually together with LEADING.
- PARALLEL(alias degree): parallel execution.
- APPEND: direct-path insert.
- GATHER_PLAN_STATISTICS and MONITOR: diagnostics, which collect actual row counts and enable SQL Monitor without changing the plan.
When Not to Use Hints
- A hint fixes one plan for all data and all future data volumes. When the data changes, the hinted plan can become the worst one, as above.
- Most bad plans come from wrong estimates. Fix the statistics, histograms, or column groups first, and the optimizer usually finds the good plan by itself.
- Use hints to test what a different plan would cost, as in this guide, and keep them in production code only with a comment explaining why.
Related Guides
- How to Read Join Methods in Oracle Execution Plans: Nested Loops, Hash Join, and Merge Join
- How to Find and Fix Stale Statistics in Oracle
Conclusion
Write hints as /*+ ... */ right after the first keyword, and refer to tables by their aliases. Check every hint with the Hint Report of DBMS_XPLAN, which marks hints that were unresolved (N), misspelled (E), or unused (U), and prefer fixing estimates over forcing plans.
