본문 바로가기

Study/class note

sql중급 / with절

109 WITH절 사용하기 1(WITH ~ AS)

--시간이 오래 걸리는 무거운 쿼리문이 하나의 쿼리문에서 반복 사용된다면?

하나의 sql에서 비슷한 select 문장이 반복되어서 나타나는 경우 with절로 작성하면 성능을 개선할 수 있음.

-- (튜닝전) with절을 사용하지 않은 무거운 SQL
select e1.*, e2.*
from ( select deptno, sum(sal) as sumsal
from emp
where deptno != 20
group by deptno) e1,
( select deptno, sum(sal) as sumsal
from emp
where deptno != 30
group by deptno) e2
where e1.deptno = e2.deptno ;
-- (튜닝후)
with emp500 as (select deptno as 부서번호, sum(sal) as 토탈월급
from emp
group by deptno )
select e1.*, e2.*
from emp500 e1, emp500 e2
where e1.부서번호 = e2.부서번호
and e1.부서번호 != 20
and e2.부서번호 != 30;

튜닝후 sql을 살펴보면,

1. select deptno as 부서번호, sum(sal) as 토탈월급
                  from emp
                  group by deptno 

의 결과를 출력하는 임시테이블 emp500로 설정

2. emp500을 가지고 select절이 수행됨

3. 임시테이블 emp500은 with절이 끝날때까지만 유지됨

 

문제600. 위의 with절의 실행계획을 확인하시오

 

ㅇwith절 작성 시 주의사항

여러 개의 with절을 동시에 수행하면 다같이 느려짐.

 

-- with절이 길어져서 이를 다시 튜닝해야할 때는 "힌트" 활용
with emp500 as (select /*+ inline */ deptno as 부서번호, sum(sal) as 토탈월급
from emp
group by deptno )
select e1.*, e2.*
from emp500 e1, emp500 e2
where e1.부서번호 = e2.부서번호
and e1.부서번호 != 20
and e2.부서번호 != 30;

with절에 inline 힌트를 줘서 튜닝

ㅇwith절 사용히 중요 힌트 2가지

1. inlin : temp table 구성 안하겠다

2. materialize : temp table 구성 하겠다.

 

with절 여러개가 동시에 수행되면 다같이 느려지기 때문에 코디네이트 하는 사람이 있어야 함.

 

문제601. 아래의 sql을 with절로 변경하시오

select ' 2 x ' || empno || ' = ' || empno *2

 from (

          select level as empno

              from dual

              connect by level <= 10 );

with emp200 as ( select level as empno
from dual
connect by level <= 10 )
select ' 2 x ' || e.empno || ' = ' || e.empno *2
from emp200 e

임시테이블을 생성해달라고 dba에게 요청하지 않아도 내가 짠 SQL에서 with절로 임시테이블을 생성할 수 있는 장점이 있음.

 

문제602. 1부터 10까지의 숫자 중에 짝수만 출력하시오

with emp10 as ( select level as num
from dual
connect by level <= 10)
select num
from emp10
where mod(num,2)= 0;

문제603. 1부터 10까지의 합을 출력하시오

with emp10 as ( select level as num
from dual
connect by level <= 10)
select sum(num)
from emp10;

문제604. (수학식을 SQL로 구현하기) 아래의 수학식을 SQL로 구현하시오

select ln(10)
from dual;

= 밑수가 자연상수(e), 진수가 10인 로그함수

 

문제605. 아래의 수학식을 SQL로 구현하시오

select exp(10)
from dual;

= 자연상수e의 10승

 

문제606. 1부터 10까지의 곱을 출력하시오

with emp10 as ( select level as num
from dual
connect by level <= 10)
select exp(sum(ln(num)))
from emp10;

문제607. 1부터 6까지의 숫자중에 하나를 랜덤으로 출력하시오

           ( = 주사위를 한번 던지세요)

select dbms_random.value(0.5, 6.5)
from dual;

0.5 ~ 6.5 사이의 난수가 출력됨

select round(dbms_random.value(0.5, 6.5))
from dual;

round를 둘러주면 1에서 6사이의 난수가 출력됨

문제608. 주사위를 10번 던져서 10개의 숫자를 출력하시오

/* with game as ( select level as num
from dual
connect by level <= 10)
select round(dbms_random.value(0.5, 6.5) )
from game;
game테이블 행의 갯수만큼 주사위가 반복됨 = 주사위를 10개 던진 것 */
-- 주사위 1개를 10번 던진 것
with game as ( select round(dbms_random.value(0.5, 6.5 ) ) as num
from dual )
select num
from game
connect by level <= 10;

문제609. 주사위를 100번 던져서 숫자 3이 나올 확률은 어떻게 되는가

with game as ( select round(dbms_random.value(0.5, 6.5 ) ) as num
from dual )
select count(*) / 100
from (select num
from game
connect by level <= 100)
where num = 3;

문제610. 주사위를 던져서 주사위의 눈이 홀수가 나올 확률을 출력하시오

with game as ( select round(dbms_random.value(0.5, 6.5 ) ) as num
from dual )
select count(*) / 100000
from (select num
from game
connect by level <= 100000)
where mod(num,2) = 1
-- where num in (1,3,5) = 주사위라서 숫자가 별로 없기때문에 나열 가능

문제611. (확률문제) 두개의 주사위가 있다. 두개의 주사위를 동시에 던져서 하나는 짝수가 나오고 다른 하나는 홀수가 나올 확률을 구하시오(주사위를 십만번 던지세요)

with dice1 as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1
from dual ),
dice2 as (select round(dbms_random.value(0.5, 6.5 ) ) as num2
from dual )
select count(*) / 100000
from (select num1, num2
from dice1 d1, dice2 d2
connect by level <= 100000 )
where num1 in (1,3,5) and num2 in (2,4,6);
with dice_table as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1,
round(dbms_random.value(0.5, 6.5 ) ) as num2
from dual )
select count(*) / 100000
from (select num1, num2
from dice_table
connect by level <= 100000 )
where (num1 in (1,3,5) and num2 in (2,4,6) ) or
(num2 in (1,3,5) and num1 in (2,4,6) ) ;

문제612. 주사위 3개를 동시에 던져서 주사위 3개의 합이 10이 되는 확률?

with dice1 as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1
from dual ),
dice2 as (select round(dbms_random.value(0.5, 6.5 ) ) as num2
from dual ),
dice3 as (select round(dbms_random.value(0.5, 6.5 ) ) as num3
from dual )
select count(*) / 100000
from (select num1 + num2 + num3 as sumnum
from dice1 d1, dice2 d2, dice3 d3
connect by level <= 100000 )
where sumnum = 10;

문제613. (오늘의 마지막 문제) 주사위 1개와 동전 1개를 동시에 던져서 주사위의 눈이 5가 나오고 동전이 앞면이 나오는 확률을 구하시오.

with dice as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1
from dual ),
coin as (select round(dbms_random.value(0.5, 2.5 ) ) as num2
from dual )
select count(*) / 100000
from (select num1, num2
from dice d, coin c
connect by level <= 100000 )
where num1 = 5 and num2 = 1 ;
반응형