So there is a view we use at work, it is quite complicated but internally it joins a lot of tables with a view (note this view is using aggregation, so is non mergeable). Up until now we have been filtering using a single value or occasionally a static inlist and the explain plan pushes the inlist into the view, filtering nicely. Recently, a requirement has cropped up where it needs to work with a collection of ids (the requirements say a collection... long story).... That's when the problems began. No matter what I've tried, I could not get the optimiser to push the predicates from the collection into the non mergeable view and performance is abysmal (the tables involved are huge). If I swapped out the collection for a temp table of ids, with the correct hints, the predicate gets pushed.
Anyway, I've created a simple test scenario, which demonstrates. Test 4 and 5 are an abridged version of the attempts I've tried to get JPPD to work with collection iterators. I just can't. This problem exists on 11R2 and 12.1C. Any suggestions? It's probably the sort of thing tuning gurus like Jonathan Lewis may know a solution or workaround to, but I've hit an impasse. Oh and yes I expect people will suggest MViews, pipelined functions, dynamic SQL, etc, etc, but I'd really like to see if this can be solved as per requirements.
create table t1(c1, c2) as
select level, mod(level, 100)
from dual
connect by level <= 4000;
create table t2(c1, c2, c3) as
select mod(level, 4000), level, level
from dual
connect by level <= 100000;
alter table t1 add constraint pk_t1 primary key (c1);
alter table t2 add constraint pk_t2 primary key (c1, c2);
exec dbms_stats.gather_table_stats(user, 't1', no_invalidate => false);
exec dbms_stats.gather_table_stats(user, 't2', no_invalidate => false);
create type ty_numbers as table of integer;
create global temporary table t_gtt (
id integer
)
on commit delete rows;
Test 1. Inlist, without hints.. Bad - hash group, hash join, full table scans!!!
select t1.c1 as tc1, t1.c2 as tc2, v.*
from t1
left join (select c1, sum(c2), max(c3)
from t2
group by c1) v on v.c1 = t1.c1
where t1.c2 in (1, 2)
Plan hash value: 577572187
-----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 42000 | 79 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 2000 | 42000 | 79 (4)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 2000 | 42000 | 78 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 80 | 560 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 100K| 1367K| 73 (2)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"(+)="T1"."C1")
3 - filter("T1"."C2"=1 OR "T1"."C2"=2)
Test 2. Inlist with hints.. Nice, predicate is pushed, JPPD occurs, good execution
select /*+ no_merge(v) push_pred(v)*/ t1.c1 as tc1, t1.c2 as tc2, v.*
from t1
left join (select c1, sum(c2), max(c3)
from t2
group by c1) v on v.c1 = t1.c1
where t1.c2 in (1, 2)
Plan hash value: 2725946496
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 3680 | 2165 (1)| 00:00:26 |
| 1 | NESTED LOOPS OUTER | | 80 | 3680 | 2165 (1)| 00:00:26 |
|* 2 | TABLE ACCESS FULL | T1 | 80 | 560 | 4 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 39 | 27 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 14 | 27 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 25 | 350 | 27 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_T2 | 25 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."C2"=1 OR "T1"."C2"=2)
6 - access("C1"="T1"."C1")
Test 3. GTT with hints.. OK execuction, T1 full scan (ok, expected as no leading index), but predicate is pushed, JPPD occurs, ok execution
insert all
into t_gtt (id) values (1)
into t_gtt (id) values (2)
select null from dual
/
select /*+ no_merge(v) push_pred(v) */ t1.c1 as tc1, t1.c2 as tc2, v.*
from t1
left join (select c1, sum(c2), max(c3)
from t2
group by c1) v on v.c1 = t1.c1
where t1.c2 in (select id from t_gtt)
Plan hash value: 1537285856
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 4720 | 2167 (1)| 00:00:27 |
| 1 | NESTED LOOPS OUTER | | 80 | 4720 | 2167 (1)| 00:00:27 |
|* 2 | HASH JOIN RIGHT SEMI | | 80 | 1600 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T_GTT | 2 | 26 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 4000 | 28000 | 4 (0)| 00:00:01 |
| 5 | VIEW PUSHED PREDICATE | | 1 | 39 | 27 (0)| 00:00:01 |
| 6 | SORT GROUP BY | | 1 | 14 | 27 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 25 | 350 | 27 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | PK_T2 | 25 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C2"="ID")
8 - access("C1"="T1"."C1")
Test 4. Collection iterator. Bad. Full scans, hash joins, no JPPD
select /*+ no_merge(v) push_pred(v)*/ t1.c1 as tc1, t1.c2 as tc2, v.*
from t1
left join (select c1, sum(c2), max(c3)
from t2
group by c1) v on v.c1 = t1.c1
where t1.c2 in (select /*+ dynamic_sampling(2) */ column_value from table(ty_numbers(1, 2)))
Plan hash value: 3745820365
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 1920 | 93 (7)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 40 | 1920 | 93 (7)| 00:00:02 |
|* 2 | HASH JOIN RIGHT SEMI | | 40 | 360 | 16 (7)| 00:00:01 |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 2 | 4 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 4000 | 28000 | 4 (0)| 00:00:01 |
| 5 | VIEW | | 4000 | 152K| 77 (7)| 00:00:01 |
| 6 | HASH GROUP BY | | 4000 | 56000 | 77 (7)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T2 | 100K| 1367K| 73 (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V"."C1"(+)="T1"."C1")
2 - access("T1"."C2"=VALUE(KOKBF$))
Test 5. Collection iterator, sub query factoring, materializing. Bad. Full scans galore, hash joins, no JPPD
with data as (
select /*+ materialize dynamic_sampling(2) */ *
from table(TY_NUMBERS(1, 2))
)
select /*+ no_merge(v) push_pred(v)*/ t1.c1 as tc1, t1.c2 as tc2, v.*
from t1
left join (select c1, sum(c2), max(c3)
from t2
group by c1) v on v.c1 = t1.c1
where t1.c2 in (select column_value from data)
Plan hash value: 496349719
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 2360 | 95 (7)| 00:00:02 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_29991D | | | | |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 2 | 4 | 11 (0)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 40 | 2360 | 84 (8)| 00:00:02 |
|* 5 | HASH JOIN RIGHT SEMI | | 40 | 800 | 7 (15)| 00:00:01 |
| 6 | VIEW | VW_NSO_1 | 2 | 26 | 2 (0)| 00:00:01 |
| 7 | VIEW | | 2 | 26 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_29991D | 2 | 4 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T1 | 4000 | 28000 | 4 (0)| 00:00:01 |
| 10 | VIEW | | 4000 | 152K| 77 (7)| 00:00:01 |
| 11 | HASH GROUP BY | | 4000 | 56000 | 77 (7)| 00:00:01 |
| 12 | TABLE ACCESS FULL | T2 | 100K| 1367K| 73 (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("V"."C1"(+)="T1"."C1")
5 - access("T1"."C2"="COLUMN_VALUE")