Unnesting disjunctive subqueries (with OR predicate)

Jonathan Lewis has recently posted a good example of CBO not good enough in transforming specific query types. A recent thread on the SQL.ru Oracle forum reminded me of this issue.

Basically OP has a query with disjuncted (OR-ed) predicate which started to fail after 11.2.0.3 upgrade with ORA-01790: expression must have same datatype as corresponding expression. Here is a test case (I’ve renamed column and table names cause I’ve used to such naming):

drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;

create table t1 (
  x int not null,
  y varchar2(13)
);
                 
insert into t1 values(0,100);
insert into t1 values(0,100);
insert into t1 values(0,100);
                 
create table t2 (
 x number,
 y number(15)
);

insert into t2 values(1,100);
insert into t2 values(2,100);
insert into t2 values(1,100);
insert into t2 values(1,100);

commit;

exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false)
exec dbms_stats.gather_table_stats(user, 't2', no_invalidate=>false)

Now let’s try to run following query:

SQL> select *
  2    from t2
  3   where not exists
  4          (select 1
  5            from t1
  6           where (t1.y = t2.y)
  7              or (t1.x = t2.y));
            or (t1.x = t2.y))
                *
ERROR at line 7:
ORA-01790: expression must have same datatype as corresponding expression

Oops. But if you change second condition with swapping left and right parts it is executed OK:

SQL> ed
Wrote file afiedt.buf

  1  select *
  2    from t2
  3   where not exists
  4          (select 1
  5            from t1
  6           where (t1.y = t2.y)
  7*             or (t2.y = t1.x))
SQL> /

no rows selected

And the plan is FILTER-based

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |     7   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T2   |     4 |    24 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT 0 FROM "T1" "T1" WHERE "T1"."X"=:B1
              OR TO_NUMBER("T1"."Y")=:B2))
   3 - filter("T1"."X"=:B1 OR TO_NUMBER("T1"."Y")=:B2)

So why first query error-ed? Let’s look at the critical lines of 10053 trace file (which is incomplete due to error):

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:   Passed validity checks.
Branch query block:******* UNPARSED QUERY IS *******
SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y" OR "T1"."X"="T2"."Y"
Branch query block:******* UNPARSED QUERY IS *******
SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y" OR "T1"."X"="T2"."Y"
Set query block:******* UNPARSED QUERY IS *******
 (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y") UNION ALL  (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."X"="T2"."Y")
Registered qb: SET$E74BECDC 0xd0e9a0c8 (COMPLEX SUBQUERY UNNEST SEL$2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SET$E74BECDC nbfros=1 flg=0
    fro(0): flg=0 objn=0 hint_alias="NULL_HALIAS"@"SET$E74BECDC"

Query block after disjuncts converted to set:******* UNPARSED QUERY IS *******
SELECT "T2"."X" "X","T2"."Y" "Y" FROM "TIM"."T2" "T2" WHERE  NOT EXISTS ( (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y") UNION ALL  (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."X"="T2"."Y"))
SU:   Unnesting subquery query block SET$E74BECDC (#0)Registered qb: SET$7FD77EFD 0xd0e9a0c8 (SUBQ INTO VIEW FOR COMPLEX UNNEST SET$E74BECDC)
...
SELECT "T2"."X" "X","T2"."Y" "Y" FROM  ( (SELECT "T1"."Y" "ITEM_1" FROM "TIM"."T1" "T1") UNION ALL  (SELECT "T1"."X" "ITEM_2" FROM "TIM"."T1" "T1")) "VW_SQ_1","TIM"."T2" "T2" WHERE "VW_SQ_1"."VW_COL_1"="T2"."Y"

Basically Oracle tries to transform the query to replace disjunct predicate with a set (UNION ALL) of two subqueries, which allows it to unnest the resulting subquery and then join it. It’s not merged though due to UNION ALL presence.
And since for reasons unknown to me Oracle throws an exception for a query block:

select 'x' from dual
union all
select 2 from dual

original query fails. It can be avoided with an explicit type conversion which will still allow unnesting to happen:

SQL> explain plan for
  2  select *
  3    from t2
  4   where not exists
  5          (select 1
  6            from t1
  7           where (to_number(t1.y) = t2.y)
  8              or (t1.x = t2.y));

Explained.

SQL> @xp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1288684780

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    19 |    10  (10)| 00:00:01 |
|*  1 |  HASH JOIN ANTI      |         |     1 |    19 |    10  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T2      |     4 |    24 |     3   (0)| 00:00:01 |
|   3 |   VIEW               | VW_SQ_1 |     6 |    78 |     6   (0)| 00:00:01 |
|   4 |    UNION-ALL         |         |       |       |            |          |
|   5 |     TABLE ACCESS FULL| T1      |     3 |    12 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T1      |     3 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("VW_COL_1"="T2"."Y")

But if you don’t need unnesting, then by using hidden _optimizer_unnest_disjunctive_subq parameter you can turn off the transformation (for me it does so, but be careful with the hidden stuff):

explain plan for
select /*+ opt_param('_optimizer_unnest_disjunctive_subq', 'false') */ *
  from t2
 where not exists 
        (select 1
          from t1
         where (t1.y = t2.y)
            or (t1.x = t2.y));
@xp

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |     7   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T2   |     4 |    24 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT 0 FROM "T1" "T1" WHERE "T1"."X"=:B1
              OR TO_NUMBER("T1"."Y")=:B2))
   3 - filter("T1"."X"=:B1 OR TO_NUMBER("T1"."Y")=:B2)

And in Jonathan’s case it’s also possible to invoke such optimization. All that is needed is a simple modification of a predicate to move 1000 to the left, which will allow Oracle to hash join the resulting view without a problem:

SQL> explain plan for
  2  select
  3      *
  4  from
  5      t2
  6  where
  7      not exists (
  8          select  /*+ unnest */
  9              null
 10          from    t3
 11          where   n3a = n2a
 12          and n3b = n2b
 13          and (id3 = id2 or id3 - 1000 = id2)
 14      )
 15  ;

Explained.

SQL> @xp

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 3482347739

-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |  5000 |   776K|    33   (4)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT ANTI   |         |  5000 |   776K|    33   (4)| 00:00:01 |
|   2 |   VIEW                  | VW_SQ_1 | 10000 |   380K|    10   (0)| 00:00:01 |
|   3 |    UNION-ALL            |         |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN| T3_PK   |  5000 | 60000 |     5   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| T3_PK   |  5000 | 60000 |     5   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL     | T2      |  5000 |   585K|    22   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("VW_COL_1"="ID2" AND "VW_COL_2"="N2A" AND "VW_COL_3"="N2B")

What’s in total:

  1. Predicates order matters sometimes
  2. The way a predicate is written matters sometimes
About these ads

3 Comments

  1. Narendra
    Posted June 8, 2012 at 13:05 | Permalink | Reply

    What’s in total:
    Predicates order matters sometimes
    The way a predicate is written matters sometimes

    After so many years of “matured CBO”, this quote is not going to help the “deprecation” of RB, I guess.
    BTW, nice post.

    • Posted June 8, 2012 at 13:48 | Permalink | Reply

      Hi Narendra

      thank you for your comment.
      Well, RBO is already deprecated but it’s not always possible to get rid of it.

  2. Igor Usoltsev
    Posted June 9, 2012 at 18:40 | Permalink | Reply

    Thanks Timur, very interesting
    and another MOS-documented solution – by disabling of any unnest operations:

    11.2.0.3.@SQL> select *
      2    from t2
      3   where not exists
      4          (select 1
      5            from t1
      6           where (t1.y = t2.y)
      7              or (t1.x = t2.y))
      8  /
                or (t1.x = t2.y))
                    *
    ERROR at line 7:
    ORA-01790: expression must have same datatype as corresponding expression
    
    
    SQL> select *
      2    from t2
      3   where not exists
      4          (select /*+ NO_UNNEST*/ 1
      5            from t1
      6           where (t1.y = t2.y)
      7              or (t1.x = t2.y))
      8  /
    
    no rows selected

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