How many times have you heard or read that the Oracle Database query optimizer may freely ignore hints if it so chooses? How many times have you heard or read that the query optimizer silently ignores hints that are invalid? I was recently reminded of both of these concepts while reading the book “Oracle Database 11g Performance Tuning Recipes“.

Page 494 of the book states:

“Tip: Hints with incorrect or improper syntax are ignored by the optimizer.”

Page 496 of the book states:

“Note: Hints influence the optimizer, but the optimizer may still choose to ignore any hints specified in the query.”

Hints with incorrect syntax may cause the optimizer to consider the hints as invalid, but the changed outcome is potentially much worse (or better) than the hint being simply ignored, as demonstrated in this blog article. As described in another blog article, hints are directives and must be obeyed unless…

I thought that I would craft an interesting, yet simple test case to see if invalid hints are silently ignored. Here is the test case script:

So, the USE_HASH hint which is invalid because a hash join cannot be used when the join condition is an inequality, causes the optimizer to switch from a sort-merge join to a nested loops join. Does this mean that invalid hints are potentially NOT silently ignored?

Actions

Information

4 responses

Arguably, the USE_HASH hint is not invalid – just inapplicable. An invalid hint would be something like /*+ GO_FASTER */. But your test suggests it may be misnamed – perhaps it should have been NO_USE_SORT_MERGE? That’s the trouble with the CBO – what looks like a binary choice in 1990 becomes much more complex by 2011.

To the casual observer, the order of the hints potentially matters. However, for those who read the articles at the start of this blog article, it is quite clear why the execution plan reverted back from the sort-merge join (well, almost… why was that 10053 trace enabled?).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: