Top 60 Oracle Blogs

Recent comments

Scalar subquery unnesting

Here is a nice example of what Oracle is able to do with a subquery inside an expression. It can unnest it – that is a new 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);

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

SELECT /*+ qb_name(qb1) */ *
  FROM tests a
 WHERE id = NVL ( (SELECT  /*+ qb_name(qb2) */ MAX (
                     FROM tests b
                    WHERE > 2  AND =,

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)
  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)
  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) */ id 
  from (select /*+ qb_name (qb2) */ max( "max(", item_0 
          from tests b
         where > 2
         group by vw_sq_1
     , tests a
 where = nvl(vw_sq_1."max(", 1) 
   and = vw_sq_1.item_0;

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

SQL> @bug unnest

     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
   6438752     1 QKSFM_CBQT_6438752                       do not store cost annotations for branch QBs in unnested subquer
   6681545     1 QKSFM_PARTITION_6681545                  Enable unnesting of correlated subquery containing tbl$ predicat
   7032684     1 QKSFM_TRANSFORMATION_7032684             Allow non-well-formed correlated predicates in unnesting
   6669103     1 QKSFM_TRANSFORMATION_6669103             an operand of OPTTNN is null-safe for query unnesting  
   7215982     1 QKSFM_UNNEST_7215982                     unnest subquery embedded inside an expression          
   8214022     1 QKSFM_UNNEST_8214022                     perform additional CBQT phase for subquery unnesting   
   9143856     1 QKSFM_TRANSFORMATION_9143856             uncorrelated OR-ed unary predicates are OK for unnesting

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 (
  4                       FROM tests b
  5                      WHERE > 2  AND =,
  6                   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:, CBQT, subquery unnesting