02. subquery_unnesting

-------------------------------------------------------------
-- @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


Comments