Ignoring hints

A hint is an instruction to the optimizer

This is what’s written in Oracle documentation. Instruction is defined as

a code that tells a computer to perform a particular operation

Which means Oracle CBO must obey the hints and must perform particular operation. The latter is hard to define correctly and explain precisely because it involves the logic of the block-box (what Cost Based Optimizer is). Some of the operations are mentioned in the standard Oracle documentation, some of them scattered across different places, and there are exceptions as usual. I think I’ll list here these cases which could lead to “ignoring hints” with the links to documentation/blogs.

Description	Hints affected	Reference
The hint has a syntax error, or doesn’t follow DELETE/INSERT/SELECT/MERGE/UPDATE keyword, or conflicts with other hints	All	http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref482
The optimizer ignores FIRST_ROWS in DELETE and UPDATE statement blocks and in SELECT statement blocks that include any blocking operations, such as sorts or groupings	FIRST_ROWS	http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref524
The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints.	LEADING, ORDERED	http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref564
If a NO_INDEX hint and an index hint (INDEX, INDEX_ASC,INDEX_DESC, INDEX_COMBINE, or INDEX_FFS) both specify the same indexes, then the database ignores both the NO_INDEX hint and the index hint for the specified indexes and considers those indexes for use during execution of the statement.	INDEX*	http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref589
If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block.	QB_NAME	http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#sthref692
If a hint specifies an unavailable access path, the optimizer ignores it	Access path	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
If the statement uses an alias for the table, then use the alias rather than the table name in the hint	Access path	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
The table name within the hint should not include the schema name if the schema name is present in the statement	Access path	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
For access path hints, Oracle Database ignores the hint if you specify the SAMPLE option in the FROM clause of a SELECT statement	Access path	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94938
The hints USE_NL & USE_MERGE are ignored if the referenced table is the outer table in the join	Join operations	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId7
Oracle Database ignores global hints that refer to multiple query blocks	?	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#i21188
Access path and join hints on referenced views are ignored unless the view contains a single table or references an Additional Hints view with a single table.	?	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId21
With nonmergeable views, optimization approach and goal hints inside the view are ignored. Access path hints on the view in the top-level query are ignored.	?	http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId23
If an invalid hint is a valid SQL keyword, it causes other hints to be ignored	All	https://support.oracle.com/epmos/faces/DocumentDisplay?id=826893.1
When parallel_instance_group points to a non-existent service name, PARALLEL hint will be ignored	PARALLEL	https://support.oracle.com/epmos/faces/DocumentDisplay?id=1467447.1
INDEX hint may be “ignored” if materialized query rewrite produces plan with lower cost	?	http://jonathanlewis.wordpress.com/2007/02/21/ignoring-hints/
Transitive closure and join elimination may produce a plan which ignores USE_HASH hint	Join operations	http://jonathanlewis.wordpress.com/2010/02/11/ignoring-hints-2/
Hints in ANSI joins could be ignored due to query transformation and introduction of new query blocks	?	http://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/
Undocumented limit of 20 chars for query block name causes QB_NAME to be ignored	QB_NAME	http://oracle-randolf.blogspot.com/2013/02/qbname-hint-query-block-name-length.html

Unsurprisingly, most of the cases are covered by the documentation. Good to know.
PS. Apart from documentation, an excellent source of information about hinting is presentation and paper Hint on Hints by Jonathan Lewis.