#35_140801_TUNING_SUBQUERY TUNING

# SUBQUERY TUNING

l 서브쿼리를 쓸 수 있는 절

1.     select

- scalar subquery

2.     from

- in line view

3.     where

- subquery

4.     having

- subquery

5.     order by

- scalar subquery

문제70 이름, 월급, 부서번호, 사원 테이블 전체의 최대월급, 사원 테이블 전체의 최소월급,사원 테이블 전체의 토탈월급을 출력하시오

# 튜닝 전
SELECT ename, sal, deptno, (SELECT MAX(sal) FROM emp), (SELECT min(sal) FROM emp), (SELECT sum(sal) FROM emp) FROM EMP;	db block gets        0 consistent gets     67 physical reads        0
# 튜닝 후
SELECT ename, sal, deptno, max(sal) OVER() maxsal,    min(sal) OVER() minsal, SUM(sal) OVER() sumsal FROM EMP; - 분석함수가 성능이 더 좋다	db block gets        0 consistent gets     46 physical reads        0

문제71. 아래의 SQL의 emp 테이블을 엑세스 하는 빈도를 줄이시오

# 튜닝 전
SELECT ename, sal, deptno, (SELECT MAX(sal) FROM emp), (SELECT min(sal) FROM emp), (SELECT sum(sal) FROM emp) FROM EMP; SELECT ename, sal, deptno, (SELECT MAX(sal), MIN(sal), SUM(sal) FROM emp) FROM EMP; - 위 SQL은 too many 에러가 발생한다 - 스칼라 서브쿼리의 특징 때문에 딱 하나의 값만 결과로 리턴하게 되어있다	db block gets        0 consistent gets     67 physical reads        0
# 튜닝 후
SELECT ename, sal, deptno, SUBSTR(totalsal,1,10),                            SUBSTR(totalsal,11,10),                            SUBSTR(totalsal,21,10) FROM (SELECT ename, sal, deptno,        (SELECT RPAD(MAX(sal),10,' ')||RPAD(min(sal),10,' ')||RPAD(sum(sal),10,' ') FROM emp) AS totalsal      FROM emp);	db block gets        0 consistent gets     14 physical reads        0

문제72. 이름, 월급, 부서번호, 자기가 속한 부서번호의 평균월급을 출력하시오

# 튜닝 전
SELECT e.ename, e.sal, e.deptno, v.avgsal FROM EMP e, (SELECT deptno, AVG(sal) avgsal              FROM EMP              GROUP BY deptno) v WHERE e.deptno=v.deptno; - emp 테이블을 2번 select 하게 된다 - view는 서브쿼리 부분으로 inline view를 나타낸다 - inline view : from 절에 오는 subquery	# hash join db block gets        0 consistent gets     14 physical reads        0
SELECT /*+ leading(e v) use_nl(v) */ e.ename, e.sal, e.deptno, v.avgsal FROM EMP e, (SELECT deptno, AVG(sal) avgsal              FROM EMP              GROUP BY deptno) v WHERE e.deptno=v.deptno; - nested loop join 을 하면 분석함수를 이용한 것이 더 좋다는 것을 알 수 있다	# nested loop join db block gets        0 consistent gets    105 physical reads        0
# 튜닝 후
SELECT ename, sal, deptno, AVG(sal) over(PARTITION BY deptno) FROM EMP; - 통계결과가 튜닝전이 좋게 나오는데 이것은 대용량 테이블로 조회하면 튜닝 후가 더 좋아진다	db block gets        0 consistent gets     46 physical reads        0

문제73. 아래의 SQL을 튜닝하시오

CREATE INDEX emp_job ON EMP(job);

# 튜닝 전

select job, sum(sal) from emp group by job having job = 'ANALYST'; - group 합수로 조건을 줄 때만 having을 쓰고 다른 조건을 쓰면 인덱스 엑세스가             안된다

# 튜닝 후

select job, sum(sal) from emp WHERE job = 'ANALYST' group by job;

l with 절 사용할 때 쓰는 힌트

1.     /*+ inline */ : temp 사용안하고 서브쿼리로 수행

2.     /*+ materialize */ : temp 사용

문제75. 직업, 직업별 최대월급을 출력하는데 직업별 최대월급들에 평균값보다 더 큰것만 출력하시오

# 튜닝 전
SELECT job, MAX(sal) FROM EMP GROUP BY job HAVING MAX(sal) > (SELECT AVG(MAX(sal)) FROM EMP GROUP BY job);	db block gets        0 consistent gets   67 physical reads     0
# 튜닝 후
WITH job_max AS (SELECT job,MAX(sal) maxsal     FROM EMP GROUP BY job) SELECT job, maxsal FROM job_max WHERE maxsal > (SELECT AVG(maxsal) FROM job_max); - temp 라는 테이블 스패이스에 job_max절을 저장한다 - 결국 튜닝전 쿼리는 group 절을 2번 쓰지만 튜닝 후의 쿼리는 처음에temp 테이블에 저장해놓으면 1번 수행하는 결과를 가져온다	db block gets        0 consistent gets   14 physical reads     0
WITH job_max AS (SELECT /*+ inline */ job,MAX(sal) maxsal     FROM EMP GROUP BY job) SELECT job, maxsal FROM job_max WHERE maxsal > (SELECT AVG(maxsal) FROM job_max); - with를 남발하다보면 temp에 가득차서 전체적으로 느려질수 있다 - 그러므로 inline 힌트를 이용함으로써 서브쿼리로 사용하도록 할 수 있다