[5월 정기교육] 2일차 개발자교육

by 김아리 posted May 25, 2023
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);