HighLight in your life~

목적: 소트가 발생하지 않도록 SQL 작성

1. Union을 Union All로 대체

- Union을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique 연산을 수행한다.

- Union All은 중복을 허용하며 두 집합을 단순히 결합하므로 소트 연산이 불필요하다.

1차 단계

SQL> SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 10

         UNION

  SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 20

  ;

2차 단계 - 두 집합간에 중복 가능성이 전혀 없어야 한다.

             - SELECT LIST에서 EMPNO가 없다면 10번과 20번 부서에 JOB과 MGR이 같은 사원이 있을 수 있으므로

               함부로 UNION ALL로 바꿔서는 안된다.

SQL> SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 10

         UNION ALL

  SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 20

  ;

2. Distinct를 Exists 서브쿼리로 대체

- 중복 레코드를 제거하려고 DISTINCT를 사용하는 경우가 있다. 대부분 EXISTS 서브쿼리로 대체함으로써 소트연산을 제거할 수 있다.

- EXISTS 서브쿼리의 가장 큰 특징은, 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 TRUE를 반환하고 서브쿼리 수행을 마친다.

1차 단계

SQL> SELECT DISTINCT 과금연월

FROM 과금

WHERE 과금연월 <= :yyyymm

   AND 지역 LIKE :reg || '%'

   ;

2차 단계

SQL> SELECT 연월

             FROM 연월테이블 A

         WHERE 연월 <= :yyyymm

   AND EXISTS (

SELECT 'X'

FROM 과금

WHERE 과금연월 : A.연월

AND 지역 LIKE :reg || '%'

)

; 

방법- M:M 관계의 조인을 해결하거나 Full Outer Join을 대체하는 용도로 Union All을 활욜할 수 있다.

목적 - 부서별판매계획과 채널별판매실적 테이블이 있다. 이 두 테이블을 이용해 월별로 각 상품의 계획 대비 판매 실적을 집계하려고 한다.

1) 1차 단계 - DBMS와 버전에 따라 Full Outer Join을 비효율적으로 처리하기도 한다. 한 테이블을 두 번씩 액세스하기도 한다.

SELECT NVL(A.상품, B.상품) AS 상품

, NVL(A.계획연월, B.판매연월) AS 연월

, NVL(계획수량, 0) 계획수량

, NVL(판매수량, 0) 판매수량

FROM

(SELECT 상품, 계획연월, SUM(계획수량) 계획수량

FROM 부서별판매계획

WHERE 계획연월 BETWEEN '201201' AND '201203'

GROUP BY 상품, 계획연월

) A

FULL OUTER JOIN

(SELECT 상품, 판매연월, SUM(판매수량)

FROM 채널별판매실적

WHERE 판매연월 BETWEEN '201201' AND '201203'

GROUP BY 상품, 판매연월

) B

ON A.상품 = B.상품

AND A.계획연월 = B.판매연월

;

2) 2차 단계 - 조금 더 효과적인 방법이 될 수 있다.

                 - Union All을 이용하면 M:M 관계의 조인이나 Full Outer Join을 쉽게 해결할 수 있다.

                 - SQL Server에선 NVL 대신 ISNULL 함수를 사용하고, TO_NUMBER 대신 CAST 함수를 사용하기 바란다.

SELECT '계획' AS 구분, 상품, 계획연월 AS 연월, 판매부서, NULL AS 판매채널

, 계획수량, TO_NUMBER(NULL) AS 실적수량

FROM 부서별판매계획

WHERE 계획연월 BETWEEN '201201' AND '201203'

UNION ALL

SELECT '실적', 상품, 판매연월 AS 연월, NULL AS 판매부서, 판매채널

, TO_NUMBER(NULL) AS 계획수량, 판매수량

FROM 채널판매실적

WHERE 판매연월 BETWEEN '201201' AND '201203'

;

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

-- 위의 전체 집합을 상품, 연월 기준으로 GROUP BY하면서 계획수량과 실적수량을 집계해 보자.

-- 아래와 같이 월별 판매계획과 실적을 대비해 보져줄 수 있다.

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

SELECT 상품, 연월, NVL(SUM(계획수량), 0) AS 계획수량, NVL(SUM(실적수량), 0) AS 실적수량

FROM (

) A

GROUP BY 상품, 연월

;

*OR조건은 Full Table Scan된다.

1차 단계- OR조건은 Full Table Scan된다.

 SELECT * FROM EMP

  WHERE JOB = 'CLERK' OR DEPTNO = 20;

2차 단계- JOB과 DEPTNO에 각각 생성된 인덱스를 사용하고 싶다면 UNION ALL 사용

 SELECT * FROM EMP

  WHERE JOB = 'CLERK'

  UNION ALL

 SELECT * FROM EMP

  WHERE DEPTNO = 20

  AND LNNVL(JOB = 'CLERK')

 ;

JOB과 DEPTNO 칼럼을 선두로 갖는 두 인덱스가 각각 사용되었고, UNION ALL 위쪽 브랜치는 JOB = 'CLERK'인 집합을 읽고 아래쪽 브랜치는 DEPTNO = 20 인 집합만을 읽는다.

 분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, EMP 테이블 액세스가 두 번 일어난다. 따라서 중복 액세스되는 영역(DEPTNO = 20 이면서 JOB = 'CLERK')의 데이터 비중이 작을 수록 효과적이고, 그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다.

 중복 액세스되더라도 결과집합에는 중복이 없게 하려고 UNION ALL 아래쪽에 ORACLE이 내부적으로 LNNVL함수를 사용한 것을 알 수 있다. JOB <> 'CLERK' 이거나 JOB IS NULL인 집합만을 읽으려는 것이며, 이 함수는 조건식이 FALSE이거나 알 수 없는(UNKNOWN) 값일 때 TRUE를 리턴한다.

 *ORACLE에서 OR-Expansion 제어(힌트 사용)

 SELECT /*+ USE_CONCAT */ FROM EMP  -- OR Expansion 기능 유도

WHERE JOB = 'CLERK' OR DEPTNO = 20;

 SELECT /*+ NO_EXPAND */ FROM EMP  -- OR Expansion 기능 방지

WHERE JOB = 'CLERK' OR DEPTNO = 20;

P.S. 사용자 쿼리를 직접 바꿔주지 않아도 옵티마이저가 2차 단계를 대신해 주는 경우가 있는데 이를 'OR-Expansion'이라고 한다.