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:

Predicates order matters sometimes
The way a predicate is written matters sometimes

Search

Navigation

Top 60 Oracle Blogs

Recent comments

Unnesting disjunctive subqueries (with OR predicate)

RSS Feeds

About Timur Akhmadeev

Popular content

Today's:

All time:

Monthly archive