Thus far, hints have been applied directly to tables that are referenced in the SQL command. It is also possible to apply hints to tables that appear in a view when the view is referenced in the SQL command. The hint does not appear in the view, itself, but rather in the SQL command that references the view.
When specifying a hint that is to apply to a table within a view, the view and table names are given in dot notation within the hint argument list.
Synopsis
hint(view.table)
Parameters
hint
Any of the hints in Table 3-11 or Table 3-12.
view
The name of the view containing table.
table
The table on which the hint is to be applied.
Examples
A view named, tx, is created from the three-table join of history, branches, and accounts shown in the final example of Section 3.4.3.
CREATE VIEW tx AS SELECT h.mtime, h.delta, b.bid, a.aid FROM history h, branches b, accounts a WHERE h.bid = b.bid AND h.aid = a.aid;
The query plan produced by selecting from this view is show below:
EXPLAIN SELECT * FROM tx; QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=1.11..207.95 rows=26 width=20) -> Hash Join (cost=1.11..25.40 rows=26 width=20) Hash Cond: (h.bid = b.bid) -> Seq Scan on history h (cost=0.00..20.20 rows=1020 width=20) -> Hash (cost=1.05..1.05 rows=5 width=4) -> Seq Scan on branches b (cost=0.00..1.05 rows=5 width=4) -> Index Scan using accounts_pkey on accounts a (cost=0.00..7.01 rows=1 width=4) Index Cond: (h.aid = a.aid) (8 rows)
The same hints that were applied to this join at the end of Section 3.4.3 can be applied to the view as follows:
EXPLAIN SELECT /*+ USE_MERGE(tx.h tx.b) USE_HASH(tx.a) */ * FROM tx; QUERY PLAN ------------------------------------------------------------------------------------------ - Merge Join (cost=23480.11..23485.60 rows=26 width=20) Merge Cond: (h.bid = b.bid) -> Sort (cost=23479.00..23481.55 rows=1020 width=20) Sort Key: h.bid -> Hash Join (cost=21421.98..23428.03 rows=1020 width=20) Hash Cond: (h.aid = a.aid) -> Seq Scan on history h (cost=0.00..20.20 rows=1020 width=20) -> Hash (cost=13209.88..13209.88 rows=500488 width=4) -> Seq Scan on accounts a (cost=0.00..13209.88 rows=500488 width=4) -> Sort (cost=1.11..1.12 rows=5 width=4) Sort Key: b.bid -> Seq Scan on branches b (cost=0.00..1.05 rows=5 width=4) (12 rows)
In addition to applying hints to tables within stored views, hints can be applied to tables within subqueries as illustrated by the following example. In this query on the sample application emp table, employees and their managers are listed by joining the emp table with a subquery of the emp table identified by the alias, b.
SELECT a.empno, a.ename, b.empno "mgr empno", b.ename "mgr ename" FROM emp a, (SELECT * FROM emp) b WHERE a.mgr = b.empno; empno | ename | mgr empno | mgr ename -------+--------+-----------+----------- 7902 | FORD | 7566 | JONES 7788 | SCOTT | 7566 | JONES 7521 | WARD | 7698 | BLAKE 7844 | TURNER | 7698 | BLAKE 7654 | MARTIN | 7698 | BLAKE 7900 | JAMES | 7698 | BLAKE 7499 | ALLEN | 7698 | BLAKE 7934 | MILLER | 7782 | CLARK 7876 | ADAMS | 7788 | SCOTT 7782 | CLARK | 7839 | KING 7698 | BLAKE | 7839 | KING 7566 | JONES | 7839 | KING 7369 | SMITH | 7902 | FORD (13 rows)
The plan chosen by the query planner is shown below:
EXPLAIN SELECT a.empno, a.ename, b.empno "mgr empno", b.ename "mgr ename" FROM emp a, (SELECT * FROM emp) b WHERE a.mgr = b.empno; QUERY PLAN ------------------------------------------------------------------- Merge Join (cost=2.81..3.08 rows=13 width=26) Merge Cond: (a.mgr = emp.empno) -> Sort (cost=1.41..1.44 rows=14 width=20) Sort Key: a.mgr -> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=20) -> Sort (cost=1.41..1.44 rows=14 width=13) Sort Key: emp.empno -> Seq Scan on emp (cost=0.00..1.14 rows=14 width=13) (8 rows)
A hint can be applied to the emp table within the subquery to perform an index scan on index, emp_pk, instead of a table scan. Note the difference in the query plans.
EXPLAIN SELECT /*+ INDEX(b.emp emp_pk) */ a.empno, a.ename, b.empno "mgr empno", b.ename "mgr ename" FROM emp a, (SELECT * FROM emp) b WHERE a.mgr = b.empno; QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=1.41..13.21 rows=13 width=26) Merge Cond: (a.mgr = emp.empno) -> Sort (cost=1.41..1.44 rows=14 width=20) Sort Key: a.mgr -> Seq Scan on emp a (cost=0.00..1.14 rows=14 width=20) -> Index Scan using emp_pk on emp (cost=0.00..12.46 rows=14 width=13) (6 rows)
