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.

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 )

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: