Database Reference
In-Depth Information
I need to make one last comment about the naming of the query blocks generated during the query transformation
phase. Because they aren't part of the SQL statement during the parse, they can't be numbered like the others. In
such cases, the query optimizer generates an eight-character hash value for them. The following example shows that
situation. Here, the system-generated query block name is
SEL$5DA710D3
:
SQL> EXPLAIN PLAN FOR
2 SELECT deptno
3 FROM dept
4 WHERE NOT EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic +alias'));
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN ANTI | |
| 2 | TABLE ACCESS FULL| DEPT |
| 3 | TABLE ACCESS FULL| EMP |
-----------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / DEPT@SEL$1
3 - SEL$5DA710D3 / EMP@SEL$2
In the preceding output, it's interesting to notice that when such a query transformation takes place, for some
lines in the execution plan (for example, line 2) there are two query block names. Both can be used in hints. However,
the query block name after the query transformation (in this case
SEL$5DA710D3
) is available, from a query optimizer
point of view, only when the very same query transformation takes place.
When to Use It
The purpose of hints is twofold. First, they're convenient as workarounds when the query optimizer doesn't manage
to automatically generate an efficient execution plan. In such cases, you would use them to get a better execution
plan. The important thing to emphasize is that hints are workarounds and, therefore, should not be used as long-term
solutions. In some situations, however, they're the only practicable way to solve a problem. Second, hints are useful
for assessing the decisions of the query optimizer in that they lead to the generation of alternative execution plans.
In such cases, you would use them to do a kind of what-if analysis.
Pitfalls and Fallacies
Every time you want to lock up a specific execution plan through access path hints, join hints, or parallel processing
hints, you must carefully specify enough hints to achieve stability. Here, stability means that even if the object
statistics and, to some extent, the access structures change, the execution plan doesn't change. To lock up a specific
execution plan, it's not unusual to have to add not only an access path hint for each table in the SQL statement but
also several join hints to control the join methods and order. Note that other types of hints (for example, initialization
