JPPD does not occur for a view with UNION-ALL

Here is a small test case to demonstrate the problem ( on 11.2.0.4)

create table t1(a number);

create table t2(a number);

create index idx_t2 on t2(a);

create view v as

(

select * from t1

UNION ALL

select * from t2

)

create table t3(a number);

insert into t1 values(1);

insert into t2 values(2);

insert into t3 values(1);

commit;

exec dbms_stats.gather_table_stats(user,'T1')

exec dbms_stats.gather_table_stats(user,'T2')

exec dbms_stats.gather_table_stats(user,'T3')

explain plan for

select * from v where a in (select a from t3);

-------------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |     1 |    16 |     6   (0)| 00:00:01 |

|*  1 |  HASH JOIN SEMI              |      |     1 |    16 |     6   (0)| 00:00:01 |

|   2 |   VIEW                       | V    |     2 |    26 |     4   (0)| 00:00:01 |

|   3 |    UNION-ALL                 |      |       |       |            |          |

|   4 |     TABLE ACCESS STORAGE FULL| T1   |     1 |     3 |     2   (0)| 00:00:01 |

|   5 |     TABLE ACCESS STORAGE FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

|   6 |   TABLE ACCESS STORAGE FULL  | T3   |     1 |     3 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"="A")

Looking at the predicates, view is evaluated without any filters or predicates. If my view returns billions of rows, those rows are evaluated first and then a semi join is performed. This is very inefficient. I expect sub-query to be executed first, transformed into a view and that be joined with the view V and join predicates should be pushed down. If the predicates are pushed, you would see smart scans (on Exadata) or Index Range Scan.

There is bug on 11.2.0.4 which should have been fixed on 12.2.

Bug 18798414: JPPD NOT HONOURED ON VIEW WITH UNION, WHEN EXECUTION STARTS WITH SUBQUERY

I did test on 12.2 but the issue still remains. I will be testing this on 18c and 19c soon.

For now, you can rewrite the query as shown below and this provides a good workaround .

select * from T1 where a in  (select a from t3);

UNION ALL

select * from T2 where a in  (select a from t3);

This query takes advantage of JPPD.

Update On 7-SEP-2019 : I followed this up with Oracle support. There are certain heuristics that disables JPPD when one of the tables does not have indexes. Like in the example shown above T1 does not have an index.  But JPPD will still be useful without indexes, so that one branch can take advantage of smart scans (on Exadata) and other branch can benefit from indexes.

Here is the solution to get this working on 11.2.0.4

1) get backport of Bug:23246817 for 11.2.0.4.0

-> this gets heuristic from Bug:19803410 in its latest improved version

2) get backport of Bug:18798414 for 11.2.0.4.0

3) get merge patch of 23246817 with 18798414 and install it

-> these two fixes have common files so we need a merge

4) set the following in the session where the JPPD is needed

alter session set "_fix_control"='4569940:0','19803410:0','16053273:1';