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 || '%'

)

; 

*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'이라고 한다.