Oracle SQL형태별 성능비교 1
Updated:
1. 오라클 SQL형태별 성능비교
이번에는 많이 사용하는 형태인 DECODE/CASE절, Outer Join, 스칼라서브쿼리등 SQL형태에 따른 성능부하를 서로 비교해보았다. 대부분 개발자들이 DECODE/CASE절을 많이 사용하므로 약간의 발상의 전환만 하면 더 활용도가 넒어질것으로 생각합니다. 실전에서는 항상 성능이 좋은 SQL형태를 사용해야 하기 때문에 잘 기억하기 바랍니다.
가. 예제 테이블
EMP테이블 가지고 SQL형태변환을 해봅니다. EMP테이블을 만드는 스크립트는 스칼라쿼리의 통합편에서 소개해드렸다.
EMP는 11G DBMS를 설치하면 SCOTT유저라는 샘플스키마가 설치되는데 여기에서 가장 중요한 테이블입니다. 사원번호와 이름, 연봉, 소속 등의 칼럼이 있으며 EMPNO가 PK입니다. 부모테이블은 DEPT 테이블이며 부서, 위치 칼럼이 있으며 부서 DEPTNO가 PK입니다.
| Empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980/12/17 00:00:00 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 1981/02/20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981/02/22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981/04/02 00:00:00 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981/09/28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981/05/01 00:00:00 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1981/06/09 00:00:00 | 2450 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 1987/04/19 00:00:00 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 1981/11/17 00:00:00 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 1981/09/08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987/05/23 00:00:00 | 1100 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 1981/12/03 00:00:00 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 1981/12/03 00:00:00 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982/01/23 00:00:00 | 1300 | 10 |
나. 예제 SQL 문제
EMP테이블에서 JOB별로 각부서별 사원수를 구하라.
| JOB | DEPT10 | DEPT20 | DEPT30 | TOTAL |
|---|---|---|---|---|
| ANALYST | 0 | 2 | 0 | 2 |
| CLERK | 1 | 2 | 1 | 4 |
| MANAGER | 1 | 1 | 1 | 3 |
| PRESIDENT | 1 | 0 | 0 | 1 |
| SALESMAN | 0 | 0 | 4 | 4 |
다. 예제 SQL 설명
아래의 1번 SQL형태는 테이블에 한번만 접근하여 처리한 SQL입니다. 가장 좋은 성능을 발휘할수 있는 SQL입니다. 2번, 3번 SQL보다 테이블 접근빈도가 적어 인덱스와 조인방식을 적절히 선택하면 가장 최상의 성능이 나올수 있습니다. 현장 실전에서 흔히 2번, 3번 SQL형태를 자주 보는데 기억을 하였다가 1번 SQL로 변환해주길 바랍니다.
2, 3번 SQL은 동일 SQL 코드가 반복되고 있습니다. 2번 SQL은 인라인뷰안에 동일 코드가 반복되며 조회되는 값들만 틀려진다. 이런 문장은 대부분 DECODE/CASE절로 변경 가능하다. 물론 WITH절로도 변환 가능하지만 굳이 성능이 더 좋은 DECODE/CASE로 변환하는것이 좋을것 같아서 WITH 변환은 설명하지 않겠다. WITH절 정리편을 보면 변환하는것이 설명되어 있습니다.
1) DECDOE/CASE 형태
DECODE절로 만든 SQL은 JOB을 기준열을 만들고 GROUP BY JOB을 하게되면 중복된값 없이 UNIQUE한 값들로 기준열이 만들어진다. 이후 SUM(DECODE) 문장을 사용하는데 각 부서별로 해당하는 값들이 매치가 되면 1, 아니면 0 값들을 반환하게 하여 SUM을 해준다. 즉 COUNT 하는것과 동일한것이 된다. COUNT(*) 문장은 GROUP BY문장에 의해 가로줄 TOTAL을 구하게 된다.
가. DECODE절
SELECT job,
SUM(DECODE(deptno,10,1,0)) AS dept10,
SUM(DECODE(deptno,20,1,0)) AS dept20,
SUM(DECODE(deptno,30,1,0)) AS dept30,
COUNT(*) AS total
FROM emp
GROUP BY job ORDER BY 1;
나. CASE절
SELECT job,
SUM(CASE WHEN deptno=10 THEN 1 ELSE 0 END) AS dept10,
SUM(CASE WHEN deptno=20 THEN 1 ELSE 0 END) AS dept20,
SUM(CASE WHEN deptno=30 THEN 1 ELSE 0 END) AS dept30, COUNT(*) AS total
FROM emp
GROUP BY job ORDER BY 1;
2) OUTER JOIN 형태
Outer Join편에서 설명한 전형적인 SQL형태입니다. 안라인뷰에서 동일 SQL 코드가 조회조건만 바뀐 상태로 계속 사용되고 있습니다. 실전에서 많이 볼수 있는 형태로 With절로도 변경할수 있겠지만 Decode/Case절로 변경하는것이 가장 성능이 좋습니다.
SELECT job, deptno10, deptno20, deptno30,
deptno10+deptno20+deptno30 total
FROM (SELECT a.job, count(b.deptno) deptno10, count(c.deptno) deptno20,
count(d.deptno) deptno30
FROM emp a,
(select empno, deptno from emp where deptno=10) b,
(select empno, deptno from emp where deptno=20) c,
(select empno, deptno from emp where deptno=30) d
WHERE a.empno = b.empno(+)
and a.empno = c.empno(+)
and a.empno = d.empno(+)
group by a.job order by 1);
3) Scalar SubQuery 형태
인라인뷰가 Outer Join 형태로 있는것들은 Scalar SubQuery 형태로 변경할수 있습니다. Outer Join정리 편을 참고하기 바랍니다. Select절에 Sum, Count 와 같은 그룹함수가 사용되면 최악의 성능이 나올 가능성이 크다. 그러므로 이 문장은 피해야 하는 SQL형태입니다.
select job, sum(deptno10) deptno10, sum(deptno20) deptno20,
sum(deptno30) deptno30, count(*) total
from (SELECT a.job,
(select count(1) from emp B
where a.empno=b.empno and deptno=10) deptno10,
(select count(1) from emp B
where A.empno=b.empno and deptno=20) deptno20,
(select count(1) from emp B
where A.empno=b.empno and deptno=30) deptno30
FROM emp a )
group by job order by 1 ;
2. SQL형태별 성능비교
많이 사용하는 1번 DECODE/CASE형태와 2번 OUTER JOIN SQL을 100만건 예제 테이블을 생성하고 성능비교를 해보았다. 3번 SQL형태도 좋지 않은 경우이며 Outer Join 정리편에 실전 성능비교가 있으모 참고하시고, 기억을 했다가 1번 SQL 형태로 변환해서 사용하기 바랍니다.
가. DECODE/CASE 형태
RT1 테이블에 100만건의 데이타를 생성하고 실제 성능비교를 해보았다. 6개의 지사별로 해당 레코드를 카운트해보았다. 이문장은 가장 좋은 성능을 나타내며 3476블럭이 사용되어 3문장중 가장 성능이 좋습니다.
SELECT gubun,
SUM(DECODE(gubun,'A지사',1)) AS dept10,
SUM(DECODE(gubun,'B지사',1)) AS dept20,
SUM(DECODE(gubun,'C지사',1)) AS dept30,
SUM(DECODE(gubun,'D지사',1)) AS dept40,
SUM(DECODE(gubun,'E지사',1)) AS dept50,
SUM(DECODE(gubun,'F지사',1)) AS dept60,
COUNT(*) AS total
FROM scott.RT1
GROUP BY GUBUN ORDER BY 1
실행계획
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
6 SORT GROUP BY (cr=3476 pr=3473 pw=0 time=0 us cost=1014 size=36 card=6)
1000000 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=120056 us cost=969 card=1000000)
| Call | Count | CPU Time | Elapsed Time | Disk | Query | Current | Rows |
|---|---|---|---|---|---|---|---|
| Parse | 1 | 0.000 | 0.000 | 0 | 0 | 0 | 0 |
| Execute | 1 | 0.000 | 0.000 | 0 | 0 | 0 | 0 |
| Fetch | 2 | 0.484 | 0.480 | 3473 | 3476 | 0 | 6 |
| Total | 4 | 0.484 | 0.480 | 3473 | 3476 | 0 | 6 |
나. OUTER JOIN 형태
이방법은 흔히들 많이 사용하는 SQL형태로 Outer Join을 사용한 SQL입니다. 그러나 이 SQL은 RT1 테이블을 7번 접근하여야 합니다. 이 SQL은 24332 블럭을 사용하여 1번 DECDOE/CASE 의 3476블럭보다 7배이상 부하가 더 발생합니다. 즉 1번 SQL형태로만 바꿔도 성능이 비약적으로 개선된다는 이야기입니다.
SELECT gubun, dept10, dept20, dept30, dept40, dept50, dept60,
dept10+dept20+dept30+dept40+dept50+dept60 total
FROM (SELECT a.gubun, count(b.deptno) dept10, count(c.deptno) dept20,
count(d.deptno) dept30, count(e.deptno) dept40,
count(f.deptno) dept50, count(g.deptno) dept60
FROM rt1 a,
(select empno, deptno from RT1 where gubun='A지사') b,
(select empno, deptno from RT1 where gubun='B지사') c,
(select empno, deptno from RT1 where gubun='C지사') d,
(select empno, deptno from RT1 where gubun='D지사') e,
(select empno, deptno from RT1 where gubun='E지사') f,
(select empno, deptno from RT1 where gubun='F지사') g
WHERE a.empno = g.empno(+)
and a.empno = b.empno(+)
and a.empno = c.empno(+)
and a.empno = d.empno(+)
and a.empno = e.empno(+)
and a.empno = f.empno(+)
group by a.gubun order by 1);
실행계획
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
6 VIEW (cr=24332 pr=24311 pw=0 time=0 us cost=25359 size=498 card=6)
6 SORT GROUP BY (cr=24332 pr=24311 pw=0 time=0 us cost=25359 size=606 card=6)
1000000 HASH JOIN RIGHT OUTER (cr=24332 pr=24311 pw=0 time=2648403 us cost=25314 card=1000000)
166666 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=49628 us cost=971 size=2500005 card=166667)
1000000 HASH JOIN RIGHT OUTER (cr=20856 pr=20838 pw=0 time=2182386 us cost=19484 card=1000000)
166667 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=63698 us cost=971 size=2500005 card=166667)
1000000 HASH JOIN RIGHT OUTER (cr=17380 pr=17365 pw=0 time=1815434 us cost=14363 card=1000000)
166667 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=47966 us cost=971 size=2500005 card=166667)
1000000 HASH JOIN RIGHT OUTER (cr=13904 pr=13892 pw=0 time=1373734 us cost=9951 card=1000000)
166667 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=57687 us cost=971 size=2500005 card=166667)
1000000 HASH JOIN RIGHT OUTER (cr=10428 pr=10419 pw=0 time=974912 us cost=6249 card=1000000)
166667 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=81734 us cost=971 size=2500005 card=166667)
1000000 HASH JOIN RIGHT OUTER (cr=6952 pr=6946 pw=0 time=520158 us cost=3255 card=1000000)
166666 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=43744 us cost=971 card=166667)
1000000 TABLE ACCESS FULL RT1 (cr=3476 pr=3473 pw=0 time=124919 us cost=971 card=1000000)
| Call | Count | CPU Time | Elapsed Time | Disk | Query | Current | Rows |
|---|---|---|---|---|---|---|---|
| Parse | 1 | 0.000 | 0.000 | 0 | 0 | 0 | 0 |
| Execute | 1 | 0.000 | 0.000 | 0 | 0 | 0 | 0 |
| Fetch | 2 | 2.563 | 2.586 | 24311 | 24332 | 0 | 6 |
| Total | 4 | 2.563 | 2.586 | 24311 | 24332 | 0 | 6 |
3. 결언
현장에서 가장 많이 사용하는 SQL형태의 변환방법과 성능을 서로 비교해보았습니다. 향후에도 SQL형태변환과 관련된 칼럼을 계속 올릴예정이니 한번씩 살펴봐 주시길 바랍니다.