COALESCE_SQ

There are 19 new hints in Oracle 11.2.0.1:

SQL> select name from v$sql_hint where version='11.2.0.1';

NAME
------------------------------
COALESCE_SQ
NO_COALESCE_SQ
TRANSFORM_DISTINCT_AGG
NO_TRANSFORM_DISTINCT_AGG
CONNECT_BY_ELIM_DUPS
NO_CONNECT_BY_ELIM_DUPS
DST_UPGRADE_INSERT_CONV
NO_DST_UPGRADE_INSERT_CONV
STATEMENT_QUEUING
NO_STATEMENT_QUEUING
EXPAND_TABLE
NO_EXPAND_TABLE
XMLINDEX_SEL_IDX_TBL
FACTORIZE_JOIN
NO_FACTORIZE_JOIN
APPEND_VALUES
PLACE_DISTINCT
NO_PLACE_DISTINCT
NO_SUBSTRB_PAD

19 rows selected.

Some of them represents new CBO features (like FACTORIZE_JOIN/NO_FACTORIZE_JOIN). Here I’ll show purpose of COALESCE_SQ hint (NO_COALESCE is it’s inverse).

Hint name suggest it is related to subqueries transformation with coalescing (or merging, I think). So I’ve started to play with it using following tables:

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

create table t1 (id, x, y, pad)
as
select rownum
     , trunc(dbms_random.value(0, 100))
     , mod(rownum-1, 100)
     , lpad('x', 50, 'x')
  from dual
connect by level <= 10000;

create table t2 (t1_id, x2, y2, pad)
as
select mod(rownum, 10000) + 1
     , trunc(dbms_random.value(0, 100))
     , mod(rownum-1, 100)
     , lpad('x', 50, 'x')
  from dual
connect by level <= 100000;

exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2')

The first test gives interesting result:

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where x = 20
   and id in (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = 1)
   and id in (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = 1);
select * from table(dbms_xplan.display(null,null,'+outline'));

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  6800 |   288   (1)| 00:00:04 |
|*  1 |  HASH JOIN SEMI    |      |   100 |  6800 |   288   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |  1000 |  7000 |   259   (1)| 00:00:04 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$28465E12" "T2"@"SQ_1")
      LEADING(@"SEL$28465E12" "T1"@"MAIN" "T2"@"SQ_1")
      FULL(@"SEL$28465E12" "T2"@"SQ_1")
      FULL(@"SEL$28465E12" "T1"@"MAIN")
      OUTLINE(@"SQ_2")
      OUTLINE(@"SQ_1")
      OUTLINE(@"MAIN")
      UNNEST(@"SQ_2")
      UNNEST(@"SQ_1")
      OUTLINE(@"SEL$CC6FBB8A")
      ELIMINATE_JOIN(@"SEL$CC6FBB8A" "T2"@"SQ_2")
      OUTLINE_LEAF(@"SEL$28465E12")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - access("ID"="T1_ID")
   2 - filter("X"=20)
   3 - filter("X2"=1)

Oracle unnests & merges two identical subqueries and then applies join elimination, but important thing is tables in query are without any PK/UQ/FK constraints; I would call this scenario “join deduplication” rather than “join elimination”. 10053 trace is not verbose and contains only the following lines:

Join Elimination (JE)    
*************************
SQL:
FJE:   eliminate table: T2 T2
SQL:
Registered qb: SEL$28465E12 0x2f57aee0 (JOIN REMOVED FROM QUERY BLOCK SEL$CC6FBB8A; SEL$CC6FBB8A; "T2"@"SQ_2")

The same behavior is reproduced in case of unnested+merged correlated subqueries:

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where x = 20
   and id in (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y)
   and id in (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y);
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  6800 |   289   (2)| 00:00:04 |
|*  1 |  HASH JOIN SEMI    |      |   100 |  6800 |   289   (2)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   100K|   683K|   258   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - access("ID"="T1_ID" AND "X2"="T1"."Y")
   2 - filter("X"=20)

It seems like this feature is 11.2.0.1-specific:

explain plan for
select /*+ qb_name(main) optimizer_features_enable('11.1.0.7') */ *
  from t1
 where x = 20
   and id in (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = 1)
   and id in (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = 1);
select * from table(dbms_xplan.display);

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   100 |  7500 |   548   (1)| 00:00:07 |
|*  1 |  HASH JOIN SEMI     |      |   100 |  7500 |   548   (1)| 00:00:07 |
|*  2 |   HASH JOIN SEMI    |      |   100 |  6800 |   288   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T2   |  1000 |  7000 |   259   (1)| 00:00:04 |
|*  5 |   TABLE ACCESS FULL | T2   |  1000 |  7000 |   259   (1)| 00:00:04 |
----------------------------------------------------------------------------

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

   1 - access("ID"="T1_ID")
   2 - access("ID"="T1_ID")
   3 - filter("X"=20)
   4 - filter("X2"=1)
   5 - filter("X2"=1)

Back to the main topic
Next queries didn’t show anything related to “coalescing” so I won’t post execution plans for them:

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where x = 20
   and id not in (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = 1)
   and id not in (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = 1);
select * from table(dbms_xplan.display(null,null,'+outline'));

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where x = 20
   and exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = 1)
   and exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = 1);
select * from table(dbms_xplan.display(null,null,'+outline'));

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where x = 20
   and not exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = -10)
   and not exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = -10);
select * from table(dbms_xplan.display(null,null,'+outline'));

The first case where COALESCE_SQ reveals itself is correlated EXISTS:

explain plan for
select /*+ qb_name(main) */ *
  from t1
 where x = 20
   and exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y)
   and exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y);
select * from table(dbms_xplan.display(null,null,'+outline'));

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  6400 |   289   (2)| 00:00:04 |
|*  1 |  HASH JOIN SEMI    |      |   100 |  6400 |   289   (2)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   100K|   292K|   258   (1)| 00:00:04 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$AA1FF8EF" "T2"@"SQ_1")
      LEADING(@"SEL$AA1FF8EF" "T1"@"MAIN" "T2"@"SQ_1")
      FULL(@"SEL$AA1FF8EF" "T2"@"SQ_1")
      FULL(@"SEL$AA1FF8EF" "T1"@"MAIN")
      OUTLINE(@"SQ_1")
      OUTLINE(@"SQ_2")
      COALESCE_SQ(@"SQ_1")
      OUTLINE(@"SEL$875AEE96")
      COALESCE_SQ(@"SQ_2")
      OUTLINE(@"SEL$D546775E")
      OUTLINE(@"MAIN")
      UNNEST(@"SEL$D546775E")
      OUTLINE_LEAF(@"SEL$AA1FF8EF")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - access("X2"="T1"."Y")
   2 - filter("X"=20)

Let’s block subqueries unnesting:

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y)
   and exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y);
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    61 |   129   (0)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME
              ("SQ_1") */ 0 FROM "T2" "T2" WHERE "X2"=:B1))
   2 - filter("X"=20)
   3 - filter("X2"=:B1)

The cost of the query with blocked unnesting is lower. It means subquery coalescing is rule-based or it’s really cost-based with some special considerations – looking at the 10053 trace file I can’t say for sure. Next queries are similar to the last one except for additional predicates:

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y and y2 = 10)
   and exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y);
select * from table(dbms_xplan.display);

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y and y2 = 10)
   and exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y and y2 = 20);
select * from table(dbms_xplan.display);

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y and y2 = 10)
   and exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y and y2 >= 10);
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    61 |  1388   (1)| 00:00:17 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME
              ("SQ_1") */ 0 FROM "T2" "T2" WHERE "X2"=:B1 AND "Y2"=10))
   2 - filter("X"=20)
   3 - filter("X2"=:B1 AND "Y2"=10)

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    61 |  1415   (1)| 00:00:17 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |    27   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| T2   |     1 |     6 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_1") */ 0 FROM
              "T2" "T2" WHERE "X2"=:B1 AND "Y2"=10) AND  EXISTS (SELECT /*+ NO_UNNEST
              QB_NAME ("SQ_2") */ 0 FROM "T2" "T2" WHERE "X2"=:B2 AND "Y2"=20))
   2 - filter("X"=20)
   3 - filter("X2"=:B1 AND "Y2"=10)
   4 - filter("X2"=:B1 AND "Y2"=20)

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    61 |  1390   (1)| 00:00:17 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |    27   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| T2   |     2 |    12 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_1") */ 0 FROM
              "T2" "T2" WHERE "X2"=:B1 AND "Y2"=10) AND  EXISTS (SELECT /*+ NO_UNNEST
              QB_NAME ("SQ_2") */ 0 FROM "T2" "T2" WHERE "X2"=:B2 AND "Y2">=10))
   2 - filter("X"=20)
   3 - filter("X2"=:B1 AND "Y2"=10)
   4 - filter("X2"=:B1 AND "Y2">=10)

Only the first query has undergone subquery coalescing. Here is 10053 trace file excerpt for it:

Query before EXCN SQ coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "T1"."ID" "ID","T1"."X" "X","T1"."Y" "Y","T1"."PAD" "PAD" FROM "TIM"."T1" "T1" WHERE "T1"."X"=20 AND  EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_1") */ "T2"."T1_ID" "T1_ID" FROM "TIM"."T2" "T2" WHERE "T2"."X2"="T1"."Y" AND "T2"."Y2"=10) AND  EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_2") */ "T2"."T1_ID" "T1_ID" FROM "TIM"."T2" "T2" WHERE "T2"."X2"="T1"."Y")
Registered qb: SEL$875AEE96 0x317a7924 (SUBQUERY COALESCE SQ_1; SQ_1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$875AEE96 nbfros=1 flg=0
    fro(0): flg=0 objn=64960 hint_alias="T2"@"SQ_1"

Registered qb: SEL$D546775E 0x317a7924 (SUBQUERY COALESCE SEL$875AEE96; SQ_2)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$D546775E nbfros=1 flg=0
    fro(0): flg=0 objn=64960 hint_alias="T2"@"SQ_1"

Query after subquery coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "T1"."ID" "ID","T1"."X" "X","T1"."Y" "Y","T1"."PAD" "PAD" FROM "TIM"."T1" "T1" WHERE "T1"."X"=20 AND  EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME ("SQ_1") */ "T2"."T1_ID" "T1_ID" FROM "TIM"."T2" "T2" WHERE "T2"."X2"="T1"."Y" AND "T2"."Y2"=10)

And 10053 trace for one without subquery coalescing:

Query before EXCN SQ coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "T1"."ID" "ID","T1"."X" "X","T1"."Y" "Y","T1"."PAD" "PAD" FROM "TIM"."T1" "T1" WHERE "T1"."X"=20 AND  EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_1") */ "T2"."T1_ID" "T1_ID" FROM "TIM"."T2" "T2" WHERE "T2"."X2"="T1"."Y" AND "T2"."Y2"=10) AND  EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_2") */ "T2"."T1_ID" "T1_ID" FROM "TIM"."T2" "T2" WHERE "T2"."X2"="T1"."Y" AND "T2"."Y2"=20)
Query before EXCN SQ coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "T1"."ID" "ID","T1"."X" "X","T1"."Y" "Y","T1"."PAD" "PAD" FROM "TIM"."T1" "T1" WHERE "T1"."X"=20 AND  EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_1") */ "T2"."T1_ID" "T1_ID" FROM "TIM"."T2" "T2" WHERE "T2"."X2"="T1"."Y" AND "T2"."Y2"=10) AND  EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_2") */ "T2"."T1_ID" "T1_ID" FROM "TIM"."T2" "T2" WHERE "T2"."X2"="T1"."Y" AND "T2"."Y2"=20)

It looks like subquery coalescing has not been fully incorporated into the CBO trace facility yet and contains only minimum information.
Next 4 examples are with correlated NOT EXISTS subqueries:

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and not exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y)
   and not exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y);
select * from table(dbms_xplan.display);

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and not exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y and y2 = -10)
   and not exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y);
select * from table(dbms_xplan.display);

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and not exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y and y2 = -10)
   and not exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y and y2 = -20);
select * from table(dbms_xplan.display);

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and not exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y and y2 = -10)
   and not exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y and y2 <= 0);
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 |  6039 |   129   (0)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST
              QB_NAME ("SQ_1") */ 0 FROM "T2" "T2" WHERE "X2"=:B1))
   2 - filter("X"=20)
   3 - filter("X2"=:B1)

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 |  6039 |   129   (0)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST
              QB_NAME ("SQ_1") */ 0 FROM "T2" "T2" WHERE "X2"=:B1))
   2 - filter("X"=20)
   3 - filter("X2"=:B1)

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 |  6039 |   885   (1)| 00:00:11 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |    17   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST
              QB_NAME ("SQ_1") */ 0 FROM "T2" "T2" WHERE "X2"=:B1 AND ("Y2"=(-10) OR
              "T2"."Y2"=(-20))))
   2 - filter("X"=20)
   3 - filter("X2"=:B1 AND ("Y2"=(-10) OR "T2"."Y2"=(-20)))

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 |  6039 |   784   (1)| 00:00:10 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST
              QB_NAME ("SQ_1") */ 0 FROM "T2" "T2" WHERE "X2"=:B1 AND ("T2"."Y2"<=0
              OR "Y2"=(-10))))
   2 - filter("X"=20)
   3 - filter("X2"=:B1 AND ("T2"."Y2"<=0 OR "Y2"=(-10)))

All 4 queries, unlike those with correlated EXISTS, take advantage of subquery coalescing. Mixed types of subqueries are not coalesced:

explain plan for
select /*+ qb_name(main) no_unnest(@sq_1) no_unnest(@sq_2) */ *
  from t1
 where x = 20
   and     exists (select /*+ qb_name(sq_1) */ t1_id from t2 where x2 = t1.y and y2 = 10)
   and not exists (select /*+ qb_name(sq_2) */ t1_id from t2 where x2 = t1.y and y2 = 10);
select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    61 |  1415   (1)| 00:00:17 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |    27   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| T2   |     1 |     6 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("SQ_1") */ 0 FROM
              "T2" "T2" WHERE "X2"=:B1 AND "Y2"=10) AND  NOT EXISTS (SELECT /*+
              NO_UNNEST QB_NAME ("SQ_2") */ 0 FROM "T2" "T2" WHERE "X2"=:B2 AND
              "Y2"=10))
   2 - filter("X"=20)
   3 - filter("X2"=:B1 AND "Y2"=10)
   4 - filter("X2"=:B1 AND "Y2"=10)

Now big one:

explain plan for
select /*+ 
           qb_name(main) 
           no_unnest(@sq_1)  no_unnest(@sq_2)  no_unnest(@sq_3)  no_unnest(@sq_4) 
           no_unnest(@sq_5)  no_unnest(@sq_6)  no_unnest(@sq_7)  no_unnest(@sq_8) 
           no_unnest(@sq_9)  no_unnest(@sq_10) no_unnest(@sq_11) no_unnest(@sq_12)
           no_unnest(@sq_13) no_unnest(@sq_14) no_unnest(@sq_15) no_unnest(@sq_16)
       */ *
  from t1
 where x = 20
   and not exists (select /*+ qb_name(sq_1)  */ t1_id from t2 where x2 = t1.y and y2 = -10)
   and not exists (select /*+ qb_name(sq_2)  */ t1_id from t2 where x2 = t1.y and y2 = -11)
   and not exists (select /*+ qb_name(sq_3)  */ t1_id from t2 where x2 = t1.y and y2 = -12)
   and not exists (select /*+ qb_name(sq_4)  */ t1_id from t2 where x2 = t1.y and y2 = -13)
   and not exists (select /*+ qb_name(sq_5)  */ t1_id from t2 where x2 = t1.y and y2 = -14)
   and not exists (select /*+ qb_name(sq_6)  */ t1_id from t2 where x2 = t1.y and y2 = -15)
   and not exists (select /*+ qb_name(sq_7)  */ t1_id from t2 where x2 = t1.y and y2 = -16)
   and not exists (select /*+ qb_name(sq_8)  */ t1_id from t2 where x2 = t1.y and y2 = -17)
   and not exists (select /*+ qb_name(sq_9)  */ t1_id from t2 where x2 = t1.y and y2 = -18)
   and not exists (select /*+ qb_name(sq_10) */ t1_id from t2 where x2 = t1.y and y2 = -19)
   and not exists (select /*+ qb_name(sq_11) */ t1_id from t2 where x2 = t1.y and y2 = -20)
   and not exists (select /*+ qb_name(sq_12) */ t1_id from t2 where x2 = t1.y and y2 = -21)
   and not exists (select /*+ qb_name(sq_13) */ t1_id from t2 where x2 = t1.y and y2 = -22)
   and not exists (select /*+ qb_name(sq_14) */ t1_id from t2 where x2 = t1.y and y2 = -23)
   and not exists (select /*+ qb_name(sq_15) */ t1_id from t2 where x2 = t1.y and y2 = -24)
   and not exists (select /*+ qb_name(sq_16) */ t1_id from t2 where x2 = t1.y and y2 = -25);
select * from table(dbms_xplan.display(null,null,'+outline'));

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    92 |  5612 |  9054   (1)| 00:01:49 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100 |  6100 |    29   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |    16   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| T2   |     2 |    12 |    31   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL| T2   |     1 |     6 |    17   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL| T2   |     1 |     6 |    17   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL| T2   |     2 |    12 |    33   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL| T2   |     1 |     6 |    18   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS FULL| T2   |     2 |    12 |    35   (0)| 00:00:01 |
|* 10 |   TABLE ACCESS FULL| T2   |     1 |     6 |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$D546775E" "T2"@"SQ_1")
      FULL(@"SEL$EE2B32E8" "T2"@"SQ_3")
      FULL(@"SEL$4E796B71" "T2"@"SQ_5")
      FULL(@"SEL$88839611" "T2"@"SQ_7")
      FULL(@"SEL$C46A81E6" "T2"@"SQ_9")
      FULL(@"SEL$A5C7D0D6" "T2"@"SQ_11")
      FULL(@"SEL$63997955" "T2"@"SQ_13")
      FULL(@"SEL$32EB4257" "T2"@"SQ_15")
      FULL(@"MAIN" "T1"@"MAIN")
      OUTLINE(@"SQ_15")
      OUTLINE(@"SQ_13")
      OUTLINE(@"SQ_11")
      OUTLINE(@"SQ_9")
      OUTLINE(@"SQ_7")
      OUTLINE(@"SQ_5")
      OUTLINE(@"SQ_3")
      OUTLINE(@"SQ_1")
      OUTLINE(@"MAIN")
      OUTLINE(@"SQ_16")
      COALESCE_SQ(@"SQ_15")
      OUTLINE(@"SEL$C2663FBB")
      OUTLINE(@"SQ_14")
      COALESCE_SQ(@"SQ_13")
      OUTLINE(@"SEL$2351EECD")
      OUTLINE(@"SQ_12")
      COALESCE_SQ(@"SQ_11")
      OUTLINE(@"SEL$030F5B05")
      OUTLINE(@"SQ_10")
      COALESCE_SQ(@"SQ_9")
      OUTLINE(@"SEL$A75F0308")
      OUTLINE(@"SQ_8")
      COALESCE_SQ(@"SQ_7")
      OUTLINE(@"SEL$82E658C4")
      OUTLINE(@"SQ_6")
      COALESCE_SQ(@"SQ_5")
      OUTLINE(@"SEL$F5B1C1EE")
      OUTLINE(@"SQ_4")
      COALESCE_SQ(@"SQ_3")
      OUTLINE(@"SEL$794FC390")
      OUTLINE(@"SQ_2")
      COALESCE_SQ(@"SQ_1")
      OUTLINE(@"SEL$875AEE96")
      OUTLINE_LEAF(@"MAIN")
      COALESCE_SQ(@"SQ_16")
      OUTLINE_LEAF(@"SEL$32EB4257")
      COALESCE_SQ(@"SQ_14")
      OUTLINE_LEAF(@"SEL$63997955")
      COALESCE_SQ(@"SQ_12")
      OUTLINE_LEAF(@"SEL$A5C7D0D6")
      COALESCE_SQ(@"SQ_10")
      OUTLINE_LEAF(@"SEL$C46A81E6")
      COALESCE_SQ(@"SQ_8")
      OUTLINE_LEAF(@"SEL$88839611")
      COALESCE_SQ(@"SQ_6")
      OUTLINE_LEAF(@"SEL$4E796B71")
      COALESCE_SQ(@"SQ_4")
      OUTLINE_LEAF(@"SEL$EE2B32E8")
      COALESCE_SQ(@"SQ_2")
      OUTLINE_LEAF(@"SEL$D546775E")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST
              QB_NAME ("SQ_1") */ 0 FROM "T2" "T2" WHERE "X2"=:B1 AND ("Y2"=(-10) OR
              "T2"."Y2"=(-11))) AND  NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST
              NO_UNNEST QB_NAME ("SQ_3") */ 0 FROM "T2" "T2" WHERE "X2"=:B2 AND
              ("Y2"=(-12) OR "T2"."Y2"=(-13))) AND  NOT EXISTS (SELECT /*+ NO_UNNEST
              NO_UNNEST NO_UNNEST QB_NAME ("SQ_5") */ 0 FROM "T2" "T2" WHERE "X2"=:B3
              AND ("Y2"=(-14) OR "T2"."Y2"=(-15))) AND  NOT EXISTS (SELECT /*+
              NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME ("SQ_7") */ 0 FROM "T2" "T2"
              WHERE "X2"=:B4 AND ("Y2"=(-16) OR "T2"."Y2"=(-17))) AND  NOT EXISTS
              (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME ("SQ_9") */ 0 FROM
              "T2" "T2" WHERE "X2"=:B5 AND ("Y2"=(-18) OR "T2"."Y2"=(-19))) AND  NOT
              EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME ("SQ_11") */ 0
              FROM "T2" "T2" WHERE "X2"=:B6 AND ("Y2"=(-20) OR "T2"."Y2"=(-21))) AND
              NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME ("SQ_13")
              */ 0 FROM "T2" "T2" WHERE "X2"=:B7 AND ("Y2"=(-22) OR "T2"."Y2"=(-23)))
              AND  NOT EXISTS (SELECT /*+ NO_UNNEST NO_UNNEST NO_UNNEST QB_NAME
              ("SQ_15") */ 0 FROM "T2" "T2" WHERE "X2"=:B8 AND ("Y2"=(-24) OR
              "T2"."Y2"=(-25))))
   2 - filter("X"=20)
   3 - filter("X2"=:B1 AND ("Y2"=(-10) OR "T2"."Y2"=(-11)))
   4 - filter("X2"=:B1 AND ("Y2"=(-12) OR "T2"."Y2"=(-13)))
   5 - filter("X2"=:B1 AND ("Y2"=(-14) OR "T2"."Y2"=(-15)))
   6 - filter("X2"=:B1 AND ("Y2"=(-16) OR "T2"."Y2"=(-17)))
   7 - filter("X2"=:B1 AND ("Y2"=(-18) OR "T2"."Y2"=(-19)))
   8 - filter("X2"=:B1 AND ("Y2"=(-20) OR "T2"."Y2"=(-21)))
   9 - filter("X2"=:B1 AND ("Y2"=(-22) OR "T2"."Y2"=(-23)))
  10 - filter("X2"=:B1 AND ("Y2"=(-24) OR "T2"."Y2"=(-25)))

It shows that coalescing is applied for two subqueries at a time and isn’t applied recursively; thus 16 subqueries were transformed to 8.

As a summary: Oracle continues to improve CBO, query transformation in particular. Subquery coalescing, which works for a limited set of scenarios for correlated filter subqueries, can provide some benefit – reducing number of similar subqueries – to applications written in “generative SQL” style. I think it will be improved further in patch sets and major releases to support comprehensive scenarios.

About these ads