Database Reference
In-Depth Information
Exceptions to this rule are the
global hints
. With them it's possible to reference objects contained in other query
blocks (provided they're named) by using the dot notation. For example, in the following SQL statement, the main
query contains a hint intended for the subquery. Notice how the subquery name is used for the reference:
WITH
emps AS (SELECT deptno, count(*) AS cnt
FROM emp
GROUP BY deptno)
SELECT /*+
full(dept) full(emps.emp)
*/ dept.dname, emps.cnt
FROM dept, emps
WHERE dept.deptno = emps.deptno
The syntax of global hints supports references for more than two levels (for example, for a view referenced in
another view). The objects must simply be separated by a dot (for example,
view1.view2.view3.table
).
■
Because global hints don't always cope with some query transformations, i advise you to use the syntax based
on query block names (shown shortly) instead.
Tip
Because subqueries in the
WHERE
clause aren't named, their objects can't be referenced with global hints. To solve
this problem, there is another way to achieve the same result. In fact, most hints accept a parameter specifying which
query block they're valid for. In this way, the hints may be grouped at the beginning of a SQL statement and simply
reference the query block to which they apply. To allow these references, not only does the query optimizer generate a
query block name
for each query block, but it also allows you to specify your own names through the
qb_name
hint. For
instance, in the following query, the two query blocks are called
main
and
sq
, respectively. Then, in the
full
hint, the
query block names are referenced by prefixing them with an
@
sign. Notice how the access path hint for the
emp
table
of the subquery is specified in the main query:
WITH
emps AS (SELECT /*+
qb_name(sq)
*/ deptno, count(*) AS cnt
FROM emp
GROUP BY deptno)
SELECT /*+
qb_name(main) full(@main dept) full(@sq emp)
*/ dept.dname, emps.cnt
FROM dept, emps
WHERE dept.deptno = emps.deptno
The previous example showed how to specify your own names. Now let's see how you can use the names
generated by the query optimizer. First, you have to know what they are. For that, you can use the
EXPLAIN PLAN
statement and the
dbms_xplan
package, as shown in the following example. Note that the
alias
option is passed to
the
display
function to make sure that the query block names and aliases are part of the output:
SQL> EXPLAIN PLAN FOR
2 WITH emps AS (SELECT deptno, count(*) AS cnt
3 FROM emp
4 GROUP BY deptno)
5 SELECT dept.dname, emps.cnt
6 FROM dept, emps
7 WHERE dept.deptno = emps.deptno;
