Oracle SQL 형태변환 정리

Updated: April 04, 2020

1. 개요

지금 연재를 하고 있는 내용들이 지난 10년간 SK(주)에서 고급 SQL활용(Advanced SQL Part 2)라는 이름으로 4일간 강의하던 내용들을 좀 더 쉽게 만든 내용입니다. 고급 SQL활용은 다른 개발자들이 복잡하게 만들어 놓은 SQL이나 PL/SQL문장을 최고의 성능을 보장하는 SQL형태로 만들어낼수 있는 능력을 갖추는데 목적이 있는 강의입니다.

그러다보니 내용이 좀 어려워 숙련된 개발자들(5년차이상)을 대상으로 강의한 과목입니다. 아직 1/3 정도만 포스팅했는데 향후 가능한 전부 할수 있도록 노력하겠습니다. 그동안 홈페이지를 하나 만들어 내용을 올릴까 많이 망설였는데 이번 코로나사태와 회사에서 홈페이지를 만든다고 해서 겸사겸사 그동안 강의한 내용을 정의해서 개발자들이 반드시 알아야할 데이타처리 로직 위주로 내용을 만들고 있습니다. 개발하실때 좀 도움이 되었으면 좋겠습니다.

이번에는 난이도를 좀 쉽게 해서 인라인뷰, 서브쿼리, 스칼라서브쿼리, WITH절등 여러분들의 SQL형태에 대한 고착화된 관념을 좀 흔들기 위해 만들어보았습니다.

가. 업무 요구사항

EMP 테이블에서 각 부서별 최고연봉을 받는 인력의 이름과 연봉을 구하시오.
위의 결과값을 다양하게 추출해봅니다.

EMPNO	ENAME	SAL	DEPTNO
7839	KING	5000	10
7902	FORD	3000	20
7698	BLAKE	2850	30

나. EMP테이블

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

2. 서브쿼리 사용

개발자들이 가장 많이 사용하는 형태중 하나로 서브쿼리를 이용하여 부서별 최고연봉을 구하고 바깥단에서 필요한 칼럼들을 조인해서 가져오는 형태입니다. 이 SQL이 DB 옵티마이져에게 전달되면 서브쿼리는 그 복잡도에 따라 Complex View, Simple View로 나눠지는데 Complex view 는 최소 인라인뷰까지 변환하고, Simple View는 완전 조인형태로 풀어헤쳐져서 SQL이 다시 만들어집니다. 이렇게하는 이유는 복잡한 SQL형태를 최대한 간단하게 재조합하여 옵티마이져가 최선의 실행계획을 만드는데 도움이 되기 위함이라 합니다.

가번 SQL에서 나번 SQL로 변환가능하다. 우리가 학교때 배운 X = Y 이면 0 = Y - X 으로 변경 가능 하다는것은 누구나 알고있는 공식입니다. 그래서 나번처럼 SQL을 바꿀수 있습니다. 그러나 나번처럼 형태가 바뀌면 조인처리가 아니라 FILTER 처리가 발생하여 다량의 데이타처리시 성능상 문제가 발생 할수있습니다. 다번 SQL은 NOT Exists로, 라번은 >=ALL 형태로 바꾼 모양입니다. 그래도 동일한 결과값을 조회합니다.

가. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A
WHERE A.SAL = (SELECT MAX(B.SAL)
               FROM EMP B
               WHERE B.DEPTNO = A.DEPTNO)
ORDER BY A.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=6 Bytes=216)
   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=6 Card=6 Bytes=216)
   3    2       HASH JOIN (Cost=5 Card=71 Bytes=2K)
   4    3         INDEX (FULL SCAN) OF 'IX_DEPTNO' (INDEX) (Cost=1 Card=14 Bytes=98)
   5    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=406)

나. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A
WHERE  0 = (SELECT MAX(B.SAL)  –  A.SAL
            FROM EMP B
            WHERE B.DEPTNO = A.DEPTNO)
ORDER BY A.DEPTNO;

Execution Plan
----------------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=17)
   1    0   SORT (ORDER BY) (Cost=11 Card=1 Bytes=17)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=238)
   4    2       SORT (AGGREGATE) (Card=1 Bytes=7)
   5    4         FIRST ROW (Cost=1 Card=1 Bytes=7)
   6    5           INDEX (FULL SCAN (MIN/MAX)) OF 'IX_DEPTNO' (INDEX) (Cost=1 Card=1 Bytes=7)

다. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A
WHERE  NOT EXISTS (SELECT  1
                   FROM EMP B
                   WHERE  B.SAL > A.SAL
                      AND B.DEPTNO = A.DEPTNO)
ORDER BY A.DEPTNO;

Execution Plan
-----------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=3 Bytes=72)
   1    0   SORT (ORDER BY) (Cost=6 Card=3 Bytes=72)
   2    1     HASH JOIN (ANTI) (Cost=5 Card=3 Bytes=72)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=238)
   4    2       INDEX (FULL SCAN) OF 'IX_DEPTNO' (INDEX) (Cost=1 Card=14 Bytes=98)

라. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A
WHERE  A.SAL >=ALL (SELECT  B.SAL
                    FROM EMP B
                    WHERE  B.DEPTNO = A.DEPTNO)
ORDER BY A.DEPTNO;

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=14 Bytes=238)
   1    0   SORT (ORDER BY) (Cost=11 Card=14 Bytes=238)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=238)
   4    2       INDEX (FULL SCAN) OF 'IX_DEPTNO' (INDEX) (Cost=1 Card=2 Bytes=14)

3. Multi Colunm Non-Correlated Subquery 사용

IN 절을 사용한 스칼라 서브쿼리 형태로도 만들수 있습니다. 이때 주의점은 2개이상의 멀티 칼럼을 넣어, 가번 처럼 만들어야 합니다. 나번처럼 만들면 의미가 틀리므로 Wrong Results가 발생합니다. 나번 의미는 DEPTNO에 상관없이 IN절에 있는 값을 추출하라는 뜻으로 가번 의미와 틀리다. 가번 의미은 각 부서별로 최고연봉을 조회하라는 뜻입니다.

가. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A
WHERE  (A.DEPTNO, A.SAL)  IN  (SELECT  B.DEPTNO, MAX(B.SAL)
                               FROM EMP B
                               GROUP BY B.DEPTNO)
ORDER BY A.DEPTNO;

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=6 Bytes=216)
   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=6 Card=6 Bytes=216)
   3    2       HASH JOIN (Cost=5 Card=71 Bytes=2K)
   4    3         INDEX (FULL SCAN) OF 'IX_DEPTNO' (INDEX) (Cost=1 Card=14 Bytes=98)
   5    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=406)

나. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A
WHERE  A.SAL  IN  (SELECT  MAX(B.SAL)
                   FROM EMP B
                   GROUP BY B.DEPTNO)
ORDER BY A.DEPTNO

4. INLINE VIEW 사용

가번 SQL INLINE VIEW 형태도 많이 사용되는 형태입니다. 가번 SQL은 서브쿼리형태로 만들어진 SQL이 DB옵티마이져가 내부적으로 인라인뷰로 바꾼 형태와 동일합니다. 2번 가항의 SQL을 만들면 내부적으로 INLINE View 혹은 조인형태처럼 바뀝니다. 2번 가항은 Comples View이므로 Inline View까지 변환된다. SQL을 보면 Inline View에서 각 부서별 최고 SQL을 추출하여 바깥단에서 부서별로 최고 SAL을 받는 인력들의 정보를 조회한다. 서브쿼리를 인라인뷰로 수정할때의 주의점은 서브쿼리는 중복값들이 DISTINCT하게 나옵니다. 그러나 인라인뷰는 그렇지 않아 중복된값이 있는 SQL일경우 반드시 DISTINCT , UNIQUE 함수를 넣어줘야 한다. 아래의 인라인뷰는 값들이 UNIQUE하게 나오므로 문제가 없지만 실전에서는 중복되어 나올수 있으므로 잘 살펴봐야 합니다.

가. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A, 
     (SELECT  B.DEPTNO, MAX(B.SAL)  MAX_SAL
     FROM EMP B
     GROUP BY B.DEPTNO) B
WHERE A.DEPTNO = B.DEPTNO
 AND A.SAL = B.MAX_SAL 
ORDER BY A.DEPTNO;

Execution Plan
----------------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=6 Bytes=216)
   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=6 Card=6 Bytes=216)
   3    2       HASH JOIN (Cost=5 Card=71 Bytes=2K)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=406)
   5    3         INDEX (FULL SCAN) OF 'IX_DEPTNO' (INDEX) (Cost=1 Card=14 Bytes=98)

5. WITH 사용

Inline View나 스칼라서브쿼리, 서브쿼리등은 WITH절로 변환할수 있습니다. 오라클 DBMS에서는 WITH절의 쿼리를 메모리에 임시테이블로 선언해서 재활용 가능한 실헹계획을 만들수 있습니다. WITH절 정리편을 보기 바랍니다.

가. SQL
WITH B AS  (SELECT  B.DEPTNO, MAX(B.SAL)  MAX_SAL
                 FROM EMP B
                 GROUP BY B.DEPTNO) 
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A, B
WHERE  A.DEPTNO = B.DEPTNO
   AND A.SAL = B.MAX_SAL 
ORDER BY A.DEPTNO


Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=33)
   1    0   SORT (ORDER BY) (Cost=7 Card=1 Bytes=33)
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS (Cost=7 Card=1 Bytes=33)
   4    3         VIEW (Cost=4 Card=3 Bytes=48)
   5    4           HASH (GROUP BY) (Cost=4 Card=3 Bytes=21)
   6    5             TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=98)
   7    3         INDEX (RANGE SCAN) OF 'IX_DEPTNO' (INDEX) (Cost=0 Card=1)
   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=17)

6. OUTER JOIN with IS NULL check 사용

2번 다항의 NOT EXISTS 문장은 OUTER JOIN with IS NULL check 형태로 변경 가능합니다.

가. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A , EMP B
WHERE  A.SAL < B.SAL(+)
     AND A.DEPTNO = B.DEPTNO(+)
     AND B.EMPNO  IS  NULL 
ORDER BY A.DEPTNO

Execution Plan
--------------------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=28)
   1    0   SORT (ORDER BY) (Cost=7 Card=1 Bytes=28)
   2    1     FILTER
   3    2       HASH JOIN (OUTER) (Cost=6 Card=1 Bytes=28)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=238)
   5    3         VIEW OF 'index$_join$_002' (VIEW) (Cost=3 Card=14 Bytes=154)
   6    5           HASH JOIN
   7    6             INDEX (FAST FULL SCAN) OF 'IX_DEPTNO' (INDEX) (Cost=1 Card=14 Bytes=154)
   8    6             INDEX (FAST FULL SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=1 Card=14 Bytes=154)


나. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM EMP A
WHERE  NOT EXISTS (SELECT  1
                   FROM EMP B
                   WHERE  B.SAL > A.SAL
                   AND B.DEPTNO = A.DEPTNO)
ORDER BY A.DEPTNO

7. Analytic Function 사용

ANSI SQL 99표준인 RANK함수를 사용하여 똑같이 결과값을 주출하였습니다. 이방법도 최근에 많이 사용하는것 같습니다.

가. SQL
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
FROM (SELECT EMPNO, ENAME, SAL, DEPTNO,
             RANK() OVER(PARTITION BY deptno ORDER BY sal desc) rnum
      FROM EMP) A
WHERE a.rnum = 1 
ORDER BY A.DEPTNO

Execution Plan
--------------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=826)
   1    0   VIEW (Cost=4 Card=14 Bytes=826)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=4 Card=14 Bytes=238)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=238)

8. 결언

이번에는 간단한 업무요구사항을 중심으로 여러가지 SQL형태로 변형해보았습니다. 서브쿼리, 인라인뷰, WITH절, NOT EXISTS, Analytic Functio등으로 바꿔보면서 SQL에 대한 인식을 좀 더 넓혀봤습니다. 어떤 SQL형태를 사용하는게 가장 좋을까요? 그것이 이 칼럼들의 목표입니다. 사용자들의 업무 요구사항이나, 빈번도, 처리 데이타량, 하드웨어/소프트웨어 스펙, DBMS 엔진특성, DB ERD, SQL튜닝 가능여부등 여러가지 고려요소에 대한 경험이 있어야 정확하게 알수가 있는 법입니다. 이 칼럼들은 이런것들을 목표로 하고 있습니다. 업무 요구사항에 따라 어떤 SQL형태를 사용하는것이 좋은가요? 가장 속도 좋고, 시스템에 악영향을 최소화 하면서 개발자들의 업무요구 사항을 한번에 처리 가능해야 합니다. 이 이야기를 계속 하도록 하겠습니다.

Twitter Facebook LinkedIn

Mooyoung2020

Oracle SQL 형태변환 정리

1. 개요

가. 업무 요구사항

나. EMP테이블

2. 서브쿼리 사용

3. Multi Colunm Non-Correlated Subquery 사용

4. INLINE VIEW 사용

5. WITH 사용

6. OUTER JOIN with IS NULL check 사용

7. Analytic Function 사용

8. 결언

You May Also Enjoy

World Quality Report(2022-23) 소개 (2)

World Quality Report(2022-23) 소개 (1)

Performance CoE 개요

7개 Project대상 LoadRunner Cloud적용을 통한 VUH사용 관련 Lessons Learned