Online Contents

06. controlling_cardinality

-------------------------------------------------------------
-- @name: controlling cardinality
-- @author: dion cho
-- @description: demo for controlling cardinality using hint
-------------------------------------------------------------

@version

-- create objects
drop table t1 purge;
drop table t2 purge;

create table t1(c1 int, c2 varchar2(10), c3 varchar2(10));
create table t2(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
create index t2_n1 on t2(c1);

-- create correlated columns
insert into t1
select level, 'A', 'a'
from dual
connect by level <= 10000
;


insert into t1
select level+10000, 'B', 'b'
from dual
connect by level <= 1000
;

insert into t2
select level, level
from dual
connect by level <= 11000
;

commit;

@gather t1
@gather t2


-- this is bad. because of the correlation of t1.c2 and t1.c3
@capture_on

explain plan for
select *
from t2,
    (select c1, c2
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;

@plan

@capture_off


-- solution1: cardinality hint
@capture_on

explain plan for
select *
from t2,
    (select /*+ cardinality(t1 1) */ c1, c2
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;

@plan

@capture_off


-- solution2: opt_estimate hint
@capture_on

explain plan for
select *
from t2,
    (select /*+ opt_estimate(table, t1, scale_rows=0.000363636) */
        c1, c2
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;

@plan

@capture_off


-- or. not good. :)
@capture_on

explain plan for
select /*+ opt_estimate(join, (t1, t2), scale_rows=0.000363636) */
  *
from t2,
    (select c1, c2
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;

@plan

@capture_off



-- solution3: dynamic_sampling hint
-- The most powerful one!!!
@capture_on

explain plan for
select
  *
from t2,
    (select /*+ dynamic_sampling(t1 4) */
        c1, c2
    from t1
    where t1.c2 = 'A' and
        t1.c3 = 'b'
    ) v
where t2.c1 = v.c1
;

@plan

@capture_off



Comments