Scalar subquery unnesting

Here is a nice example of what Oracle 11.2.0.2 is able to do with a subquery inside an expression. It can unnest it – that is a new 11.2.0.2 feature of the transformation part of the CBO.

Let’s see what’s going on in the 10053 trace of that query:

drop table tests cascade constraints purge;
create table tests  (id number);

insert into tests values(1);
insert into tests values(2);
commit;

exec dbms_stats.gather_table_stats(user, 'tests');

@53
SELECT /*+ qb_name(qb1) */ *
  FROM tests a
 WHERE id = NVL ( (SELECT  /*+ qb_name(qb2) */ MAX (b.id)
                     FROM tests b
                    WHERE b.id > 2  AND a.id = b.id),
                 1);
@53off

SU: Considering subquery unnesting in query block QB1 (#0)
********************
Subquery Unnest (SU)
********************
SU:   Checking validity of unnesting subquery QB2 (#0)
SU:     Heuristic checks passed.
SU:   Unnesting subquery query block QB2 (#0)SU:     Heuristic checks passed.
Subquery removal for query block QB2 (#0)
RSW: Not valid for subquery removal QB2 (#0)
Subquery unchanged.
Registered qb: SEL$135008A8 0xa6aee900 (SUBQ INTO VIEW FOR COMPLEX UNNEST QB2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$135008A8 nbfros=1 flg=0
    fro(0): flg=0 objn=20318 hint_alias="B"@"QB2"

Registered qb: SEL$10521F9C 0xa6b368e8 (VIEW ADDED QB1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$10521F9C nbfros=2 flg=0
    fro(0): flg=0 objn=20318 hint_alias="A"@"QB1"
    fro(1): flg=5 objn=0 hint_alias="VW_SQ_1"@"SEL$10521F9C"

Registered qb: SEL$A3D198B0 0xa6b368e8 (SUBQUERY UNNEST SEL$10521F9C; QB2)
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("QB1") */ "A"."ID" "ID" FROM  (SELECT /*+ QB_NAME ("QB2") */ MAX("B"."ID") "MAX(B.ID)","B"."ID" "ITEM_0" FROM "TIM"."TESTS" "B" WHERE "B"."ID">2 GROUP BY "B"."ID") "VW_SQ_1","TIM"."TESTS" "A" WHERE "A"."ID"=NVL("VW_SQ_1"."MAX(B.ID)",1) AND "A"."ID"="VW_SQ_1"."ITEM_0"
kkoqbc: optimizing query block SEL$135008A8 (#0)

So the transformed query looks like this:

select /*+ qb_name (qb1) */ a.id id 
  from (select /*+ qb_name (qb2) */ max(b.id) "max(b.id)", b.id item_0 
          from tests b
         where b.id > 2
         group by b.id) vw_sq_1
     , tests a
 where a.id = nvl(vw_sq_1."max(b.id)", 1) 
   and a.id = vw_sq_1.item_0;

and is not equivalent to the original query. This is definitely a bug associated with the new 11.2.0.2 feature

SQL> @bug unnest

                                                                                                                           Opt
     BUGNO VALUE SQL_FEATURE                              DESCRIPTION                                                      features
---------- ----- ---------------------------------------- ---------------------------------------------------------------- ----------
   3834770     1 QKSFM_TRANSFORMATION_3834770             Lift restriction on unnest subquery with a view                  8.0.0
   4872602     0 QKSFM_TRANSFORMATION_4872602             Disable unnesting of SQ under certain conditions
   6138746     1 QKSFM_ACCESS_PATH_6138746                Consider only simple column preds in subquery unnest heuristic   10.2.0.5
   6438752     1 QKSFM_CBQT_6438752                       do not store cost annotations for branch QBs in unnested subquer 11.1.0.7
   6681545     1 QKSFM_PARTITION_6681545                  Enable unnesting of correlated subquery containing tbl$ predicat 11.1.0.7
   7032684     1 QKSFM_TRANSFORMATION_7032684             Allow non-well-formed correlated predicates in unnesting         11.2.0.1
   6669103     1 QKSFM_TRANSFORMATION_6669103             an operand of OPTTNN is null-safe for query unnesting            10.2.0.5
   7215982     1 QKSFM_UNNEST_7215982                     unnest subquery embedded inside an expression                    11.2.0.2
   8214022     1 QKSFM_UNNEST_8214022                     perform additional CBQT phase for subquery unnesting             11.2.0.2
   9143856     1 QKSFM_TRANSFORMATION_9143856             uncorrelated OR-ed unary predicates are OK for unnesting         11.2.0.2

If it’s disabled, then everything works fine:

SQL> SELECT /*+ qb_name(qb1) opt_param('_fix_control' '7215982:off') */ *
  2    FROM tests a
  3   WHERE id = NVL ( (SELECT  /*+ qb_name(qb2) */ MAX (b.id)
  4                       FROM tests b
  5                      WHERE b.id > 2  AND a.id = b.id),
  6                   1);

                  ID
--------------------
                   1

I’ve seen this “feature” in a little bit different situation, where I’ve got a strange plan with a subquery unnested from an expression resulted in a full table scan of a huge table. I was unable to reproduce it with a simple test case. Here you can see a different (wrong results) issue, but with the same cause – too smart optimizer

Filed under: CBO, Oracle Tagged: 11.2.0.2, CBQT, subquery unnesting