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 ;)

About these ads

4 Comments

  1. Leonid
    Posted June 29, 2011 at 15:59 | Permalink | Reply

    Nice example.

    I found a Bug 10405897 – “Wrong results from subquery in an expression which includes an NVL() operator [ID 10405897.8]” which was created 30-MAR-2011. It seems well known problem.

    But with set “_fix_control” = ‘7215982:off’ we can see that subquery unnesting isn’t even considered. And explain plan includes the FILTER.

    ------------------------------------------------------------------------------                                          
    | Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                          
    ------------------------------------------------------------------------------                                          
    |   0 | SELECT STATEMENT     |       |       |       |     6 (100)|          |                                          
    |*  1 |  FILTER              |       |       |       |            |          |                                          
    |   2 |   TABLE ACCESS FULL  | TESTS |     2 |    26 |     3   (0)| 00:00:01 |                                          
    |   3 |   SORT AGGREGATE     |       |     1 |    13 |            |          |                                          
    |*  4 |    FILTER            |       |       |       |            |          |                                          
    |*  5 |     TABLE ACCESS FULL| TESTS |     1 |    13 |     3   (0)| 00:00:01 |                                          
    ------------------------------------------------------------------------------                                          
    Predicate Information (identified by operation id):                                                                     
                                                                                                                            
       1 - filter("ID"=NVL(,1))                                                                                             
       4 - filter(22))                                                                            
     

    Once in a while, it may be very costly. A little while ago Jonathan Lewis published a note (http://jonathanlewis.wordpress.com/2011/04/22/star-transformation/) explaining how it was possible to avoid similar situation with star transformation.

    First, I think that subquery unnesting of expression which includes an NVL() operator could be equivalent to the outer join:

    SQL> select /*+ qb_name (qb11) */ a.id id
      2  	 from (select /*+ qb_name (qb12) */ max(b.id) "max(b.id)", b.id item_0
      3  		 from tests b
      4  		      where b.id > 2
      5  		      group by b.id) vw_sq_1
      6  		  , tests a
      7  	      where a.id = nvl(vw_sq_1."max(b.id)", 1)
      8  		and a.id = vw_sq_1.item_0 (+);
    
            ID                                                                                                              
    ----------                                                                                                              
             1                                                                                                              
    

    Explain plan of this query includes bulk operation HASH JOIN instead of row operation FILTER. Sometimes, it may be more preferable.

    PLAN_TABLE_OUTPUT                                                                                                      
    -------------------------------------------------------------------------------                                          
    | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                         
    -------------------------------------------------------------------------------                                         
    |   0 | SELECT STATEMENT      |       |       |       |     8 (100)|          |                                         
    |*  1 |  FILTER               |       |       |       |            |          |                                         
    |*  2 |   HASH JOIN OUTER     |       |     1 |    39 |     8  (25)| 00:00:01 |                                         
    |   3 |    TABLE ACCESS FULL  | TESTS |     2 |    26 |     3   (0)| 00:00:01 |                                         
    |   4 |    VIEW               |       |     1 |    26 |     4  (25)| 00:00:01 |                                         
    |   5 |     HASH GROUP BY     |       |     1 |    13 |     4  (25)| 00:00:01 |                                         
    |*  6 |      TABLE ACCESS FULL| TESTS |     1 |    13 |     3   (0)| 00:00:01 |                                         
    -------------------------------------------------------------------------------                                         
    Predicate Information (identified by operation id):                                                                     
                                                                                                                            
       1 - filter("A"."ID"=NVL("VW_SQ_1"."max(b.id)",1))                                                                    
       2 - access("A"."ID"="VW_SQ_1"."ITEM_0")                                                                              
       6 - filter("B"."ID">2)                                                                                               
    

    Second, we can try to add missing tuples by UNION (UNION ALL) operator like

    SQL> SELECT /*+ qb_name(qb21) */ *
      2    FROM tests a
      3   WHERE id = NVL((SELECT /*+ qb_name(qb2) */
      4  		      MAX(b.id)
      5  		       FROM tests b
      6  		      WHERE b.id > 2
      7  			AND a.id = b.id),
      8  		     1)
      9  union
     10  SELECT /*+ qb_name(qb22) */ *
     11  	       FROM tests a
     12  	      WHERE id = 1;
    
            ID                                                                                                              
    ----------                                                                                                              
             1                                                                                                              
    

    This may require complementary table access and may remove (add) the duplicates but in some cases (like this example) it may work.

    PLAN_TABLE_OUTPUT                                                                                                       
    -------------------------------------------------------------------------------                                         
    | Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                      
    ----------------------------------------------------------------------------------                                      
    |   0 | SELECT STATEMENT       |         |       |       |    13 (100)|          |                                      
    |   1 |  SORT UNIQUE           |         |     2 |    52 |    13  (54)| 00:00:01 |                                      
    |   2 |   UNION-ALL            |         |       |       |            |          |                                      
    |*  3 |    HASH JOIN           |         |     1 |    39 |     8  (25)| 00:00:01 |                                      
    |   4 |     VIEW               | VW_SQ_1 |     1 |    26 |     4  (25)| 00:00:01 |                                      
    |   5 |      HASH GROUP BY     |         |     1 |    13 |     4  (25)| 00:00:01 |                                      
    |*  6 |       TABLE ACCESS FULL| TESTS   |     1 |    13 |     3   (0)| 00:00:01 |                                      
    |   7 |     TABLE ACCESS FULL  | TESTS   |     2 |    26 |     3   (0)| 00:00:01 |                                      
    |*  8 |    TABLE ACCESS FULL   | TESTS   |     1 |    13 |     3   (0)| 00:00:01 |                                      
    -------------------------------------------------------------------------------                                                                                                                                                        Predicate Information (identified by operation id):                                                                     
    ---------------------------------------------------                                                                     
                                                                                                                            
       3 - access("ID"=NVL("MAX(B.ID)",1) AND "A"."ID"="ITEM_0")                                                            
       6 - filter("B"."ID">2)                                                                                               
       8 - filter("ID"=1)
    
    • Posted June 29, 2011 at 22:22 | Permalink | Reply

      Hi Leonid,

      thanks for a comment. I’ve edited it a little bit to add formatting.

      First, I think that subquery unnesting of expression which includes an NVL() operator could be equivalent to the outer join:

      I thought about that too, but for some reason I had a feeling it’s not equivalent query version. Now I think it is.

  2. Yuri
    Posted June 29, 2011 at 16:49 | Permalink | Reply

    Hi, it is not necessary that “UNPARSED QUERY IS” is correct in all sences. See
    http://blog.tanelpoder.com/2011/06/28/knowing-what-you-want-to-achieve-before-thinking-of-how-to-achieve-it-a-query-optimization-example-2/ for more details.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 288 other followers