------------------------------------------------------------- -- @name: subquery_unnesting -- @author: dion cho -- @description: test for subquery unnesting -------------------------------------------------------------- @capture_on @version -- disable cost based query transformation -- to make the situation simple @off_cbqt -- create objects drop table t1 purge; drop table t2 purge; drop table t3 purge; create table t1(c1 int, c2 char(10), c3 int); create table t2(c1 int, c2 char(10), c3 int); create table t3(c1 int, c2 char(10), c3 int); create index t1_n1 on t1(c1); create index t2_n1 on t2(c1); create index t3_n1 on t3(c1); -- generate data -- c1 = unique, c2 = 1 distinct, c3 = 10 distinct insert into t1 select level, 'dummy', mod(level, 10)+1 from dual connect by level <= 10000 ; insert into t2 select level, 'dummy', mod(level, 10)+1 from dual connect by level <= 1000 ; insert into t3 select level, 'dummy', mod(level, 10)+1 from dual connect by level <= 100 ; commit; @gather t1 @gather t2 @gather t3 set line 200 ------------------------------------------- -- subquery unnesting -- subquery unnesting for "in" -- when nested. filter operation being used @capture_on select count(*) from ( select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select /*+ no_unnest */ t2.c1 from t2) ) ; @stat @capture_off -- when unnested. hash semi join being used @capture_on select count(*) from ( select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select t2.c1 from t2) ) ; @stat @capture_off -- is filter that evil? -- Almost yes, but sometimes not that bad -- for instance, following has same reads select /*+ gather_plan_statistics */ max(c1) from t1 where c2 = 'dummy' ; @stat select /*+ gather_plan_statistics */ max(c1) from t1 where c3 = 1 ; @stat -- low how evil filter is @capture_on select /*+ gather_plan_statistics */ count(c2) from t1 where c1 = (select /*+ no_unnest full(x) */ max(c1) from t1 x where x.c1 = t1.c1 ) ; @stat @capture_off -- but sometimes filter is efficient as following @capture_on select /*+ gather_plan_statistics */ count(c2) from t1 where c1 = (select /*+ no_unnest */ max(c1) from t1 x where x.c3 = t1.c3 ) ; @stat @capture_off -- even more efficent @capture_on select /*+ gather_plan_statistics */ count(c2) from t1 where c1 = (select /*+ no_unnest */ max(c1) from t1 x where x.c2 = t1.c2 ) ; @stat @capture_off -- why filter is this efficient? -- nested loop semi join @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select t2.c1 from t2) and t1.c1 = 1 ; @stat @capture_off -- subquery unnesting for "not in"(anti join) -- check this on 11g(null aware anti join) @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 not in (select t2.c1 from t2) ; @stat @capture_off -- apply "not null" @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 not in (select t2.c1 from t2 where t2.c1 is not null) and t1.c1 is not null ; @stat @capture_off -- subquery unnested but "not" semi/anti join -- note that subquery unnesting goes with view!!! @capture_on select count(*) from ( select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select rownum from t2) ) ; @stat @capture_off -- another examples of unnesting @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select max(c1) from t2) ; @stat @capture_off -- another example @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select c1 from t2 union all select c1 from t3) ; @stat @capture_off -- and another example -- see the benefit of converting subquery to inline view? @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select c1 from t2 union all select c1 from t3) and t1.c1 between 1 and 100 ; @stat @capture_off ------------------------------------------------ -- "non" unnestable subquery -- or. solution = ? @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in (select c1 from t2) or t1.c1 between 1 and 100 ; @stat @capture_off -- solution for non-unnestable "or" query -- rewrite query @capture_on select /*+ gather_plan_statistics */ t1.c1, t1.c2 from t1 where t1.c1 in ( select c1 from t2 where t2.c1 not between 1 and 100) union all select t1.c1, t1.c2 from t1 where t1.c1 between 1 and 100 ; @stat @capture_off ----------------------------------------- -- aggregate subquery elimination -- what happened to this query? @capture_on select /*+ gather_plan_statistics */ t1.c1, t2.c2 from t1, t2 where t1.c1 = t2.c1 and t2.c2 = ( select max(c2) from t2 s where s.c1 = t1.c1) ; @stat @capture_off -- how about this query? @capture_on select /*+ gather_plan_statistics opt_param('_remove_aggr_subquery','false') */ t1.c1, t2.c2 from t1, t2 where t1.c1 = t2.c1 and t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1) ; @stat @capture_off -- check column projection @capture_on select /*+ gather_plan_statistics opt_param('_remove_aggr_subquery','true') */ t1.c1, t2.c2 from t1, t2 where t1.c1 = t2.c1 and t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1) ; @stat_all @capture_off |
Performance In Depth > CBO In Depth >