5월 큐브리드 정기교육 2일차 개발자 쿼리 튜닝 교육 문제입니다.

--준비

drop table if exists t1;

create table t1 (col1 int, col2 int, col3 int, col4 int, col5 varchar(100), col6 date);

insert into t1 select rownum, 1,mod(rownum,5), mod(rownum,1000), rownum, sysdate - mod(rownum,100000)

                  from table({0,1,2,3,4,5,6,7,8,9}) a, table({0,1,2,3,4,5,6,7,8,9}) b, table({0,1,2,3,4,5,6,7,8,9}) c, table({0,1,2,3,4,5,6,7,8,9}) d, table({0,1,2,3,4,5,6,7,8,9}) e, table({0,1,2,3,4,5,6,7,8,9}) f 

                  limit 1000000;

insert into t1 select rownum, 2,mod(rownum,5), mod(rownum,1000), rownum, sysdate - mod(rownum,100000)

                  from table({0,1,2,3,4,5,6,7,8,9}) a, table({0,1,2,3,4,5,6,7,8,9}) b, table({0,1,2,3,4,5,6,7,8,9}) c, table({0,1,2,3,4,5,6,7,8,9}) d, table({0,1,2,3,4,5,6,7,8,9}) e, table({0,1,2,3,4,5,6,7,8,9}) f 

                  limit 1000;

create index idx1 on t1(col1);

create index idx2 on t1(col6);

create index idx3 on t1(col3,col4,col5);

create index idx4 on t1(col2);

--통계정보 갱신

update statistics on t1 with fullscan;

--실행계획 및 통계 정보확인

set trace on;

show trace;

--스키마 확인

;sc t1

-- 다음 질의를 11.2버전에서 튜닝하세요. (사용된 t1 테이블이 모두 다른 테이블이라고 가정. 동일테이블이기 때문에 재작성 가능한 경우는 고려하지 않는다.)

1. (기대 속도 : 1 sec) 

  select col1

         ,col2

         ,col4

         ,2 max_col

         ,(select col6 from t1 where col1 = a.col1 limit 1) col6

         ,decode(col2, 1, 'yes', 'no') yesno

   from t1 a

  where col4 = 1

    and exists (select 1 from t1 where col4 = 1 and col1 = a.col1)

union all

  select 

          col1

         ,col2

         ,col4

         ,(select max(col1) from t1 where col3 = a.col3) max_col

,(select col6 from t1 where col1 = a.col1 limit 1) col6

         ,decode(col2, 1, 'yes', 'no') yesno

   from t1 a

  where col2 = 1

    and exists (select 1 from t1 where col4 = 1 and col1 = a.col1);