Hints again

Hints again:
A recent posting on OTN came up with a potentially interesting problem – it started roughly like this:

I have two queries like this:

select * from emp where dept_id=10 and emp_id=15;

select * from emp where dept_id=10 and emp_id=16;

When I run them separately I get the execution plan I want, but when I run a union of the two the plans change.

This, of course, is extremely unlikely – even if we assume that the two queries are more complex than the text shown. On the other hand you might, after a little thought, come up with the idea that perhaps the optimizer had done something really clever like join factorization (moving a join that’s common to the two parts of the UNION from inside to outside the UNION), or maybe there’s some really new trick the optimizer had played because a UNION ultimately requires a SORT UNIQUE, and the optimizer had chosen a different path that returned the data from each part of the UNION in sorted order to decrease the cost of that final sort.
In fact it turned out to be a lot simpler than that. The query looked more like this:

select
 /*+
  index(@qb_view_a t1)
  index(@qb_view_b t1)
 */
 *
from
 t2, qb_view
where
 t2.n1 = 10
and qb_view.n2  = t2.n2
union
select
 /*+
  index(@qb_view_a t1)
  index(@qb_view_b t1)
 */
 *
from
 t2, qb_view
where
 t2.n1 = 12                    -- the real code referenced an alternative column here.
and qb_view.n2  = t2.n2
;

Of particular note is the fact that it’s a join, the join involves a view (guessing from the names in the FROM clause) and there are hints that reference query block names for query blocks that don’t exist – but perhaps are present inside the view. So what does the view look like.

create or replace view qb_view 
as 
select /*+ qb_name(qb_view_a) */ * from t1 
union all 
select /*+ qb_name(qb_view_b) */ * from t1
;

It’s a union all view – and the two query blocks named from the outside query are the two halves of the inner union.
Here’s an important thought – it’s quite easy to get Oracle to do what you want in a simple query (at least in the short term) by sticking in a few hints – especially if you create and reference query block names; but when you start compounding queries by combining bits of code that currently do what you want, you may find that Oracle introduces extra query blocks during transformation, and perhaps some of the query blocks you’ve referenced originally cease to exist, so the hints no longer apply.
Let’s look at the execution plan – including the ALIAS and OUTLINE sections – for the final query, and compare it with the execution plan for just one of the two pieces; starting with the single piece first:

explain plan for
select
 /*+
  index(@qb_view_a t1)
  index(@qb_view_b t1)
 */
 *
from
 t2, qb_view
where
 t2.n1 = 10
and qb_view.n2  = t2.n2
;

select * from table(dbms_xplan.display(null,null,'basic +outline +alias'));

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|   1 |  HASH JOIN                     |         |
|   2 |   TABLE ACCESS FULL            | T2      |
|   3 |   VIEW                         | QB_VIEW |
|   4 |    UNION-ALL                   |         |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1      |
|   6 |      INDEX FULL SCAN           | T1_I1   |
|   7 |     TABLE ACCESS BY INDEX ROWID| T1      |
|   8 |      INDEX FULL SCAN           | T1_I1   |
--------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1     / T2@SEL$1
   3 - SET$1     / QB_VIEW@SEL$1
   4 - SET$1
   5 - QB_VIEW_A / T1@QB_VIEW_A
   6 - QB_VIEW_A / T1@QB_VIEW_A
   7 - QB_VIEW_B / T1@QB_VIEW_B
   8 - QB_VIEW_B / T1@QB_VIEW_B

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"QB_VIEW_A" "T1"@"QB_VIEW_A" ("T1"."N1"))
      INDEX(@"QB_VIEW_B" "T1"@"QB_VIEW_B" ("T1"."N1"))
      USE_HASH(@"SEL$1" "QB_VIEW"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "QB_VIEW"@"SEL$1")
      NO_ACCESS(@"SEL$1" "QB_VIEW"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE(@"QB_VIEW_B")
      OUTLINE(@"QB_VIEW_A")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"QB_VIEW_B")
      OUTLINE_LEAF(@"QB_VIEW_A")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan shows us that we have used an index to access table t1 (with an index full scan) in both halves of the QB_VIEW’s union all; and the alias section shows us that we have a table t1 in a query block name qb_view_a, and the outline section shows that we have a hint applied in that query block that directs the optimizer to use an index on that table: INDEX(@”QB_VIEW_A” “T1″@”QB_VIEW_A” (“T1″.”N1″)); and we can see the same strategy appearing for a table t1 in query block qb_view_b. By the way, I checked the plan without the hints, and the optimizer chose to do full tablescans on t1 – so the hints were actually having an effect.
So what happens when we check the plan for the UNION of the two variants of the query:

------------------------------------------
| Id  | Operation              | Name    |
------------------------------------------
|   0 | SELECT STATEMENT       |         |
|   1 |  SORT UNIQUE           |         |
|   2 |   UNION-ALL            |         |
|   3 |    HASH JOIN           |         |
|   4 |     TABLE ACCESS FULL  | T2      |
|   5 |     VIEW               | QB_VIEW |
|   6 |      UNION-ALL         |         |
|   7 |       TABLE ACCESS FULL| T1      |
|   8 |       TABLE ACCESS FULL| T1      |
|   9 |    HASH JOIN           |         |
|  10 |     TABLE ACCESS FULL  | T2      |
|  11 |     VIEW               | QB_VIEW |
|  12 |      UNION-ALL         |         |
|  13 |       TABLE ACCESS FULL| T1      |
|  14 |       TABLE ACCESS FULL| T1      |
------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1
   3 - SEL$1
   4 - SEL$1 / T2@SEL$1
   5 - SET$2 / QB_VIEW@SEL$1
   6 - SET$2
   7 - SEL$2 / T1@SEL$2
   8 - SEL$3 / T1@SEL$3
   9 - SEL$4
  10 - SEL$4 / T2@SEL$4
  11 - SET$3 / QB_VIEW@SEL$4
  12 - SET$3
  13 - SEL$5 / T1@SEL$5
  14 - SEL$6 / T1@SEL$6

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$5" "T1"@"SEL$5")
      FULL(@"SEL$6" "T1"@"SEL$6")
      FULL(@"SEL$2" "T1"@"SEL$2")
      FULL(@"SEL$3" "T1"@"SEL$3")
      USE_HASH(@"SEL$1" "QB_VIEW"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "QB_VIEW"@"SEL$1")
      NO_ACCESS(@"SEL$1" "QB_VIEW"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$4" "QB_VIEW"@"SEL$4")
      LEADING(@"SEL$4" "T2"@"SEL$4" "QB_VIEW"@"SEL$4")
      NO_ACCESS(@"SEL$4" "QB_VIEW"@"SEL$4")
      FULL(@"SEL$4" "T2"@"SEL$4")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SET$3")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan is completely different. We’ve lost the index full scans and we’ve reverted to the tablescans that we would have got from an unhinted query. When you look at the outline you can see why – the query blocks qb_view_a and qb_view_b have disappeared – so the hints are no longer valid. As you can see we now have four occurrences of table t1, but as the alias section shows they come from query blocks sel$2, sel$3, sel$5 and sel$6).
Is this a bug ? I don’t think so. When the optimizer produces the outline information (which can be stored as an SQL Baseline in 11g) it’s producing a set of hints that will be applied at the outermost query block, with hints that point, as necessary, to inner query blocks; this means you can’t use the same query block name twice in a query or the optimizer wouldn’t be able to identify which query block a hint was supposed to apply to. So Oracle has eliminated duplicate query block names and replaced them with the standard internally generated ones – the user’s hints no longer apply.
Footnote: Checking the clock – it took me about 15 minutes to create a simplified model based on the information available on OTN: it’s taken me 75 minutes to describe what I did and what I learned as a result. With a little practice you can get very good at creating models that help you to identify and solve problems very quickly.

DIGITAL JUICE