Mohamed Houri’s Oracle Notes

September 21, 2020

OR-Expansion: bypassed – query block has subquery correlated to non-parent

Filed under: Oracle — hourim @ 6:04 pm

It looks like Oracle 19c release comes up with a new heuristic impeachment for the OR-expansion transformation:

  ORE: bypassed - query block has subquery correlated to non-parent.

Let’s see this in action via a reproducible model based on a real-life query which completes instantaneously in 12cR2 but needs 2 minutes when ran in 19c

1. Reproducible model

First, the three tables approaching as much as possible the real-life case.

SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0 
 create table t1 as 
    select rownum id
          , trunc((rownum -1/3)) n2
          , trunc(rownum -1/5) n3
          , mod(rownum,10) n4
          , lpad('x',10) vc 
    from dual 
connect by level <=1e3;

create table t2 as 
     select rownum id
          , trunc((rownum -1/6)) n2
          , trunc(rownum -1/8) n3
          , mod(rownum,5) n4
          , lpad('z',10) vc 
      from dual 
      connect by level <=1e3;
      
create table t3 as 
       select rownum id
            , trunc(sysdate + rownum -1/3) d1
            , trunc(dbms_random.value(1,1000)) r 
        from dual 
        connect by level <=20;

And then the query

explain plan for
  select /*+ or_expand */
      t1.id
	 ,t1.n2
	 ,t2.vc
  from
     t1, t2
  where
     t1.id = t2.id
  and trim(t1.vc) = 'x'
  or t1.n3 = (select
                  t3.r
              from t3
              where d1 = trunc(sysdate +1)
              )
  and t1.n4 = (select
                   max(n4)
               from t1 e
               where e.n4 = t1.n3
               and e.n2 = (select 
                               max(f.n2)
                           from t1 f
                           where f.n3 = t1.n3 -- this is the root cause of the or expansion bypass
						   )
			   );

As you can see this query is already in a DNF (Disjunctive Normal Form) since it has two distinct ORed conjuncts as the following shows:

  select 
      {list of columns}
  from
     t1, t2
  where
     t1.id = t2.id
  and 
    (trim(t1.vc) = 'x' –- conjunct n°1
  or 
     t1.n3 = (select   –- conjunct n°2
                  t3.r
              from t3
              where d1 = trunc(sysdate +1)
              )    
     )
  and t1.n4 = (select
                   max(n4)
               from t1 e
               where e.n4 = t1.n3
               and e.n2 = (select 
                               max(f.n2)
                           from t1 f
                           where f.n3 = t1.n3)
			   );

For such kind of queries, Oracle doesn’t need to have a DNF beforehand transformation prior to envisage the OR- expansion.

Now, here’re below, respectively, the 12cR2 and 19c execution plan of the above query

-------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |    11 |   363 |  3024   (1)| 00:00:01 |
|   1 |  VIEW                   | VW_ORE_F79C84EE |    11 |   363 |  3024   (1)| 00:00:01 |
|   2 |   UNION-ALL             |                 |       |       |            |          |
|*  3 |    FILTER               |                 |       |       |            |          |
|   4 |     MERGE JOIN CARTESIAN|                 |  1000 | 26000 |     6   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T1              |     1 |    15 |     3   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL | T3              |     1 |    12 |     2   (0)| 00:00:01 |
|   7 |      BUFFER SORT        |                 |  1000 | 11000 |     3   (0)| 00:00:01 |
|   8 |       TABLE ACCESS FULL | T2              |  1000 | 11000 |     3   (0)| 00:00:01 |
|   9 |     SORT AGGREGATE      |                 |     1 |     7 |            |          |
|* 10 |      FILTER             |                 |       |       |            |          |
|* 11 |       TABLE ACCESS FULL | T1              |   100 |   700 |     3   (0)| 00:00:01 |
|  12 |       SORT AGGREGATE    |                 |     1 |     8 |            |          |
|* 13 |        TABLE ACCESS FULL| T1              |     1 |     8 |     3   (0)| 00:00:01 |
|* 14 |    FILTER               |                 |       |       |            |          |
|* 15 |     HASH JOIN           |                 |    10 |   410 |     6   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS FULL  | T1              |    10 |   260 |     3   (0)| 00:00:01 |
|  17 |      TABLE ACCESS FULL  | T2              |  1000 | 15000 |     3   (0)| 00:00:01 |
|* 18 |     TABLE ACCESS FULL   | T3              |     1 |    12 |     2   (0)| 00:00:01 |
|  19 |     SORT AGGREGATE      |                 |     1 |     7 |            |          |
|* 20 |      FILTER             |                 |       |       |            |          |
|* 21 |       TABLE ACCESS FULL | T1              |   100 |   700 |     3   (0)| 00:00:01 |
|  22 |       SORT AGGREGATE    |                 |     1 |     8 |            |          |
|* 23 |        TABLE ACCESS FULL| T1              |     1 |     8 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."N4"= (SELECT MAX("N4") FROM "T1" "E" WHERE "E"."N2"= (SELECT
              MAX("F"."N2") FROM "T1" "F" WHERE "F"."N3"=:B1) AND "E"."N4"=:B2))
   5 - filter("T1"."N3"= (SELECT "T3"."R" FROM "T3" "T3" WHERE
              "D1"=TRUNC(SYSDATE@!+1)))
   6 - filter("D1"=TRUNC(SYSDATE@!+1))
  10 - filter("E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE "F"."N3"=:B1))
  11 - filter("E"."N4"=:B1)
  13 - filter("F"."N3"=:B1)
  14 - filter(LNNVL("T1"."N3"= (SELECT "T3"."R" FROM "T3" "T3" WHERE
              "D1"=TRUNC(SYSDATE@!+1))) OR LNNVL("T1"."N4"= (SELECT MAX("N4") FROM "T1" "E"
              WHERE "E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE "F"."N3"=:B1) AND
              "E"."N4"=:B2)))
  15 - access("T1"."ID"="T2"."ID")
  16 - filter(TRIM("T1"."VC")='x')
  18 - filter("D1"=TRUNC(SYSDATE@!+1))
  20 - filter("E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE "F"."N3"=:B1))
  21 - filter("E"."N4"=:B1)
  23 - filter("F"."N3"=:B1)
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   110 |  4510 |  2201   (2)| 00:00:01 |
|*  1 |  FILTER               |      |       |       |            |          |
|   2 |   MERGE JOIN CARTESIAN|      |  1000K|    39M|  2201   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T1   |  1000 | 26000 |     4   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |  1000 | 15000 |  2197   (2)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T2   |  1000 | 15000 |     2   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL   | T3   |     1 |    12 |     3   (0)| 00:00:01 |
|   7 |   SORT AGGREGATE      |      |     1 |     7 |            |          |
|*  8 |    FILTER             |      |       |       |            |          |
|*  9 |     TABLE ACCESS FULL | T1   |   100 |   700 |     4   (0)| 00:00:01 |
|  10 |     SORT AGGREGATE    |      |     1 |     8 |            |          |
|* 11 |      TABLE ACCESS FULL| T1   |     1 |     8 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."ID"="T2"."ID" AND TRIM("T1"."VC")='x' OR "T1"."N3"=
              (SELECT "T3"."R" FROM "T3" "T3" WHERE "D1"=TRUNC(SYSDATE@!+1)) AND
              "T1"."N4"= (SELECT MAX("N4") FROM "T1" "E" WHERE "E"."N2"= (SELECT
              MAX("F"."N2") FROM "T1" "F" WHERE "F"."N3"=:B1) AND "E"."N4"=:B2))
   6 - filter("D1"=TRUNC(SYSDATE@!+1))
   8 - filter("E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE
              "F"."N3"=:B1))
   9 - filter("E"."N4"=:B1)
  11 - filter("F"."N3"=:B1)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         U -  or_expand 

Notice how the OR-expansion has been used in 12cR2 and ignored in 19c.

The impeachment reason recorded in the 10053 trace file is

 ORE: bypassed - query block has subquery correlated to non-parent. 

It appears 6 times in the trace file


ORE: Predicate chain after QB validity check - SEL$4
"F"."N3"="T1"."N3"
ORE: bypassed - query block has subquery correlated to non-parent

ORE: Predicate chain after QB validity check - SEL$3
"E"."N4"="T1"."N3" AND "E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F")
ORE: bypassed - query block has subquery correlated to non-parent.

ORE: Predicate chain after QB validity check - SEL$1
"T1"."ID"="T2"."ID" AND TRIM("T1"."VC")='x' OR "T1"."N3"= (SELECT "T3"."R" FROM "T3" "T3") 
AND "T1"."N4"= (SELECT MAX("E"."N4") FROM "T1" "E")
ORE: bypassed - query block has subquery correlated to non-parent.

ORE: Predicate chain after QB validity check - SEL$4
"F"."N3"=:B1
ORE: bypassed - query block has subquery correlated to non-parent.


ORE: Predicate chain after QB validity check - SEL$3
"E"."N4"=:B1 AND "E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F")
ORE: bypassed - query block has subquery correlated to non-parent.

ORE: Predicate chain after QB validity check - SEL$1
"T1"."ID"="T2"."ID" AND TRIM("T1"."VC")='x' OR "T1"."N3"= (SELECT "T3"."R" FROM "T3" "T3") 
AND "T1"."N4"= (SELECT MAX("E"."N4") FROM "T1" "E")
ORE: bypassed - query block has subquery correlated to non-parent.

But it is fairly likely that the impeachment reason is due to this


ORE: Predicate chain after QB validity check - SEL$4
"F"."N3"="T1"."N3"
ORE: bypassed - query block has subquery correlated to non-parent

Because if I comment this part of the query then the OR expansion will be used


select /*+ or_expand */
      t1.id
     ,t1.n2
     ,t2.vc
  from
     t1, t2
  where
     t1.id = t2.id
  and trim(t1.vc) = 'x'
  or t1.n3 = (select
                  t3.r
              from t3
              where d1 = trunc(sysdate +1)
              )
  and t1.n4 = (select
                   max(n4)
               from t1 e
               where e.n4 = t1.n3
               and e.n2 = (select
                               max(f.n2)
                           from t1 f
                           --where f.n3 = t1.n3
                           )
               ); 

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |       |       |    31 (100)|          |
|   1 |  VIEW                     | VW_ORE_F79C84EE |  1010 | 33330 |    31   (4)| 00:00:01 |
|   2 |   UNION-ALL               |                 |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN   |                 |  1000 | 52000 |    17   (6)| 00:00:01 |
|*  4 |     HASH JOIN             |                 |     1 |    41 |    13   (8)| 00:00:01 |
|   5 |      VIEW                 | VW_SQ_1         |     1 |    26 |     9  (12)| 00:00:01 |
|   6 |       HASH GROUP BY       |                 |     1 |     7 |     9  (12)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL  | T1              |     1 |     7 |     4   (0)| 00:00:01 |
|   8 |         SORT AGGREGATE    |                 |     1 |     4 |            |          |
|   9 |          TABLE ACCESS FULL| T1              |  1000 |  4000 |     4   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL    | T1              |     1 |    15 |     4   (0)| 00:00:01 |
|* 11 |       TABLE ACCESS FULL   | T3              |     1 |    12 |     3   (0)| 00:00:01 |
|  12 |     BUFFER SORT           |                 |  1000 | 11000 |    13   (8)| 00:00:01 |
|  13 |      TABLE ACCESS FULL    | T2              |  1000 | 11000 |     4   (0)| 00:00:01 |
|* 14 |    FILTER                 |                 |       |       |            |          |
|* 15 |     HASH JOIN             |                 |    10 |   410 |     8   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS FULL    | T1              |    10 |   260 |     4   (0)| 00:00:01 |
|  17 |      TABLE ACCESS FULL    | T2              |  1000 | 15000 |     4   (0)| 00:00:01 |
|* 18 |     TABLE ACCESS FULL     | T3              |     1 |    12 |     3   (0)| 00:00:01 |
|  19 |     SORT AGGREGATE        |                 |     1 |     7 |            |          |
|* 20 |      TABLE ACCESS FULL    | T1              |     1 |     7 |     4   (0)| 00:00:01 |
|  21 |       SORT AGGREGATE      |                 |     1 |     4 |            |          |
|  22 |        TABLE ACCESS FULL  | T1              |  1000 |  4000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."N4"="MAX(N4)" AND "ITEM_1"="T1"."N3")
   7 - filter("E"."N2"=)
  10 - filter("T1"."N3"=)
  11 - filter("D1"=TRUNC(SYSDATE@!+1))
  14 - filter((LNNVL("T1"."N3"=) OR LNNVL("T1"."N4"=)))
  15 - access("T1"."ID"="T2"."ID")
  16 - filter(TRIM("T1"."VC")='x')
  18 - filter("D1"=TRUNC(SYSDATE@!+1))
  20 - filter(("E"."N4"=:B1 AND "E"."N2"=))

2. Work-around

In my 19c real life query which was taking 150 seconds because the OR-expansion has been refused, I’ve simply used the /*+ use_concat */ hint to get the 12cR2 execution time i.e few milliseconds:

select /*+ use_concat */
      t1.id
     ,t1.n2
     ,t2.vc
  from
     t1, t2
  where
     t1.id = t2.id
  and trim(t1.vc) = 'x'
  or t1.n3 = (select
                  t3.r
              from t3
              where d1 = trunc(sysdate +1)
              )
  and t1.n4 = (select
                   max(n4)
               from t1 e
               where e.n4 = t1.n3
               and e.n2 = (select
                               max(f.n2)
                           from t1 f
                           where f.n3 = t1.n3
                           )
               );
-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |  1010 | 41410 |    30   (0)| 00:00:01 |
|   1 |  CONCATENATION         |      |       |       |            |          |
|*  2 |   FILTER               |      |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN|      |  1000 | 41000 |     8   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL  | T1   |     1 |    26 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL | T3   |     1 |    12 |     3   (0)| 00:00:01 |
|   6 |     BUFFER SORT        |      |  1000 | 15000 |     4   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL | T2   |  1000 | 15000 |     4   (0)| 00:00:01 |
|   8 |    SORT AGGREGATE      |      |     1 |     7 |            |          |
|*  9 |     FILTER             |      |       |       |            |          |
|* 10 |      TABLE ACCESS FULL | T1   |   100 |   700 |     4   (0)| 00:00:01 |
|  11 |      SORT AGGREGATE    |      |     1 |     8 |            |          |
|* 12 |       TABLE ACCESS FULL| T1   |     1 |     8 |     4   (0)| 00:00:01 |
|* 13 |   FILTER               |      |       |       |            |          |
|* 14 |    HASH JOIN           |      |    10 |   410 |     8   (0)| 00:00:01 |
|* 15 |     TABLE ACCESS FULL  | T1   |    10 |   260 |     4   (0)| 00:00:01 |
|  16 |     TABLE ACCESS FULL  | T2   |  1000 | 15000 |     4   (0)| 00:00:01 |
|* 17 |    TABLE ACCESS FULL   | T3   |     1 |    12 |     3   (0)| 00:00:01 |
|  18 |    SORT AGGREGATE      |      |     1 |     7 |            |          |
|* 19 |     FILTER             |      |       |       |            |          |
|* 20 |      TABLE ACCESS FULL | T1   |   100 |   700 |     4   (0)| 00:00:01 |
|  21 |      SORT AGGREGATE    |      |     1 |     8 |            |          |
|* 22 |       TABLE ACCESS FULL| T1   |     1 |     8 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N4"= (SELECT MAX("N4") FROM "T1" "E" WHERE
              "E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE "F"."N3"=:B1) AND
              "E"."N4"=:B2))
   4 - filter("T1"."N3"= (SELECT "T3"."R" FROM "T3" "T3" WHERE
              "D1"=TRUNC(SYSDATE@!+1)))
   5 - filter("D1"=TRUNC(SYSDATE@!+1))
   9 - filter("E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE
              "F"."N3"=:B1))
  10 - filter("E"."N4"=:B1)
  12 - filter("F"."N3"=:B1)
  13 - filter(LNNVL("T1"."N3"= (SELECT "T3"."R" FROM "T3" "T3" WHERE
              "D1"=TRUNC(SYSDATE@!+1))) OR LNNVL("T1"."N4"= (SELECT MAX("N4") FROM
              "T1" "E" WHERE "E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE
              "F"."N3"=:B1) AND "E"."N4"=:B2)))
  14 - access("T1"."ID"="T2"."ID")
  15 - filter(TRIM("T1"."VC")='x')
  17 - filter("D1"=TRUNC(SYSDATE@!+1))
  19 - filter("E"."N2"= (SELECT MAX("F"."N2") FROM "T1" "F" WHERE
              "F"."N3"=:B1))
  20 - filter("E"."N4"=:B1)
  22 - filter("F"."N3"=:B1)

2. Conclusion

It looks like 19c has introduced a new heuristics or-expansion limit:

ORE: bypassed - query block has subquery correlated to non-parent

So, if you come to encounter it, then you know a couple of details on how to reproduce it when it happens, why it has been implemented, and how to work around it

2 Comments »

  1. Hello Mohamed,
    Hope you are doing well.
    great post as usual.
    I think this feature was introduced by 12.2 but in your case may be it was enabled under 12c and it was disabled under 19c.
    Check the fix control identifier
    26019148 = Allow ORE in select list subq

    Thanks,
    Cherif

    Comment by Cherif BEN HENDA — September 23, 2020 @ 12:34 pm | Reply

  2. Hi Cherif,

    The OR-Expansion has not been disabled in 19c. It seems, rather, that the CBO decided to block this transformations in 19c when the ORed conjunct (t1.n3 in this case) is referenced into a subquery that doesn’t make reference to the parent query block (not very clear though)

    Salam

    Comment by hourim — September 29, 2020 @ 2:10 pm | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: