Database Reference
In-Depth Information
While processing a SQL statement, the parser checks the syntax of the hints. In spite of this, no error is raised
when a hint is found with invalid syntax. This implies that the parser considers this particular pseudohint to be a
comment. From one perspective, this is annoying if it's because of a typing error. On the other hand, this is beneficial
because it avoids breaking an already deployed application because of changes to the access structures, which are
often referenced in hints (for example, the
index
hint might reference an index name), or an upgrade to a newer
database version. That said, I would welcome a way of validating the hints contained in a SQL statement. For instance,
through the
EXPLAIN PLAN
statement, it should be simple enough to provide a warning (for example, a new note in the
dbms_xplan
output) in that regard. The only way I know to be able to do this partially is by setting the event 10132. In
fact, at the end of the output generated by this event is a section dedicated to hints. You can check two things in this
section. First, each hint should be listed. If a hint is missing, it means that it hasn't been recognized as such. Second,
check whether a message informing that some hints have errors is present. (The
err
field is set to a value greater than
0 in such cases.) Note that to get the following output, two initialization parameter hints that conflict with each other
were specified:
Dumping Hints
=============
atom_hint=(@=0x6b796498 err=4 resol=0 used=0 token=454 org=1 lvl=1 txt=ALL_ROWS )
atom_hint=(@=0x6b796578 err=4 resol=0 used=0 token=453 org=1 lvl=1 txt=FIRST_ROWS )
********** WARNING: SOME HINTS HAVE ERRORS *********
Be aware that with this method, hints having good syntax but referencing wrong objects aren't reported as having
errors. So, this isn't a definitive check that everything is fine.
One of the most common mistakes made in the utilization of hints is related to table aliases. The rule is that when
a table is referenced in a hint, the alias should be used instead of the table name, whenever the table has an alias. In
the following example, you can see how a table alias (
e
) is defined for the
emp
table. In such a case, when the
full
hint
referencing the table uses the table name, the hint has no effect. Notice how in the first example, an index scan is used
instead of the wanted full table scan:
SQL> EXPLAIN PLAN FOR SELECT /*+
full(emp)
*/ * FROM emp
e
WHERE empno = 7788;
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | EMP_PK |
----------------------------------------------
SQL> EXPLAIN PLAN FOR SELECT /*+
full(e)
*/ * FROM emp
e
WHERE empno = 7788;
SQL> SELECT * FROM table(dbms_xplan.display(null,null,'basic'));
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
