Chap04. 02 서브쿼리 Unnesting

오라클 성능고도화 2권

Chap04. 02 서브쿼리 Unnesting

Tech_Wave 2021. 8. 12. 18:21

01. 서브쿼리의 분류

Subquery

하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록을 말한다.
종류
1. Inline View
  - From 절에 나타나는 서브쿼리
2. Nested Subquery
  - 결과집합을 한정하기 위해 where 절에 사용된 서브쿼리
  - 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 '상관관계 있는 서브쿼리'라고 부른다.
3. Scalar Subquery
  - 한 레코드당 정확히 하나의 컬럼 값만을 Return하는 것이 특징이다.
  - 주로 Select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 컬럼이 올 수 있는 대부분 위칭서 사용 가능하다.

옵티마이저는 Query Block 단위로 최적의 액세스 경로와 조인 순서, 조인 방식을 선택하는 것을 목표로 최적화를 수행합니다.
그런데 Query Block 단위로 최적화했다하여 Query 전체가 최적화됐다고 할 수는 없습니다. 마치 개개인이 좋다고하여 팀 전체가 유능하다할 수 없다는 의미와 같습니다.
서브쿼리를 풀어내는 두 가지 쿼리 변환 중 '서브쿼리 Unnesting'은 중첩된 서브쿼리(Nested Subquery)와 관련 있고,
'뷰 Merging'은 인라인 뷰와 관련 있다.

02. 서브쿼리 Unnesting의 의미

Nest의 의미는 "상자 등을 차곡차곡 포개넣다" 라는 설명이 있으며, 즉 "중첩"의 의미를 갖는다.
Un의 부정의미가 붙으면서 "중첩된 상태를 풀어낸다." 뜻이 됩니다.
따라서 '서브쿼리 Unnesting'은 중첩된 서브쿼리를 풀어내는 것을 말하며, 풀어내지 않고 그대로 두는 것은 '서브쿼리 No-Unnesting' 이라고 말할 수 있습니다.

SELECT * 
FROM EMP A
WHERE EXISTS ( SELECT 'X' 
               FROM DEPT
               WHERE DEPTNO = A.DEPTNO)
AND SAL > ( SELECT AVG(SAL) 
            FROM EMP B
            WHERE EXISTS ( SELECT 'X' 
                           FROM SALGRADE
                           WHERE B.SAL BETWEEN LOSAL AND HISAL
                           AND GRADE = 4)
          );

위 쿼리의 논리적인 포함관계를 상자로 표현한 그림

중첩된 서브쿼리(Nested Subquery)는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다.
- 따라서 논리적인 관점에서 그 처리과정은 IN, EXISTS를 불문하고 필터 방식이어야 한다.
- 즉, 메인쿼리에서 읽히는 레코드마다 서브쿼리를 반복수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.
하지만 서브쿼리를 처리하는데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래 둘 중 하나를 선택한다.
1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 이를 일컬어 '서브쿼리 Unnesting' 이라고 한다.
  - 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨(flat한 구조)로 만들어 준다는 의미에서 '서브쿼리 Flattening' 이라고도 부른다.
2. 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터 오퍼레이션이 나타난다.

03. 서브쿼리 Unnesting의 이점

서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
특히 옵티마이저는 많은 조인테크닉을 가지기 때문에 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아진다.

서브쿼리 Unnesting 관련한 Hint
1. UNNEST : 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
2. NO_UNNEST : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.

04. 서브쿼리 Unnesting 기본 예시

서브쿼리 Unnesting 작동 방식을 살펴보기

NO_UNNEST 힌트 사용으로 서브쿼리를 변형하지 말고 그대로 사용해주세요라고 실행계획을 유도

-- IN 서브쿼리를 포함한 SQL
SELECT *
FROM EMP
WHERE DEPTNO IN ( SELECT /*+ NO_UNNEST */ DEPTNO
                  FROM DEPT );
                  
                  
                  
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    13 |  1131 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP     |    13 |  1131 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" 
              WHERE "DEPTNO"=:B1))
   3 - access("DEPTNO"=:B1)

Predicate 정보를 보면 필터 방식으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리된 부분(DEPTNO = :B1)이 눈에 띄는데, 이것을 통해 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화한다는 사실을 알 수 있다.
메인 쿼리도 FULL TABLE SCAN으로 서브쿼리를 제외한 상태에서 별도로 최적화가 이루어졌다.
Unnesting하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행한다.
- Unnesting 하지 않았지만 내부적으로 IN 서브쿼리를 Exists 서브쿼리로 변환한다는 사실도 Predicate 정보를 통해 알 수 있다.)

Unnest 힌트를 사용하거나 옵티마이저가 스스로 Unnesting을 선택한다면, 변환된 쿼리는 아래와 같은 조인문 형태가 된다.

SELECT *
FROM ( SELECT DEPTNO FROM DEPT ) A INNER JOIN EMP B ON (A.DEPTNO = B.DEPTNO);

그리고 이것은 다시 다음절에서 설명하는 뷰 Merging 과정을 거쳐 최종적으로 아래와 같은 형태가 된다.

SELECT *
FROM dept A INNER JOIN emp B ON (A.DEPTNO = B.DEPTNO);

아래는 서브쿼리에 UNNEST 힌트를 주고 실행계획을 확인한 결과다. 서브쿼리인데도 일반적인 Nested Loop 조인 방식으로 수행 된 것을 볼 수 있다.

SELECT *
FROM EMP
WHERE DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT

05. Unnesting 된 쿼리의 조인 순서 조정

여기서 주목할 점은, Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느 쪽이든 드라이빙 집합으로 선택될 수 있다는 사실이다.
서브쿼리 쪽 DEPT 테이블이 먼저 드라이빙되었을 때의 실행계획은 위에서 보았고, 아래는 메인 쿼리 쪽 EMP 테이블이 드라이빙된 경우다.

Unnesting된 쿼리의 조인 순서를 조정하는 방법에 대해 살펴보면, 메인 쿼리 집합을 먼저 드라이빙하는 것으로 LEADING() 힌트를 사용하면 된다.

SELECT /*+ LEADING(EMP) */
       *
FROM EMP
WHERE DEPTNO IN ( SELECT /*+ UNNEST */ DEPTNO
                  FROM DEPT );

서브쿼리에서 메인 쿼리에 있는 테이블을 참조할 수는 있지만 메인 쿼리에서 서브쿼리 쪽 테이블을 참조하지는 못하므로 아래와 같은 방식을 사용할 수 없다.

SELECT /*+ LEADING(DEPT) */
       *
FROM EMP
WHERE DEPTNO IN ( SELECT /*+ UNNEST */ DEPTNO
                  FROM DEPT );

그럴 때는 LEADING 힌트 대신 아래와 같이 ORDERED 힌트를 사용하면 서브쿼리 쪽 테이블을 직접 참조하지 않아도 되므로 원하는 대로 조인 순서를 유도할 수 있다.

SELECT /*+ ORDERED */
       *
FROM EMP
WHERE DEPTNO IN ( SELECT /*+ UNNEST */ DEPTNO
                  FROM DEPT );

10g부터는 쿼리 블록마다 이름을 지정할 수 있는 qb_name 힌트가 제공되므로 아래처럼 쉽고 정확하게 제어할 수 있습니다.

SELECT /*+ LEADING(dept@qb1) */ *
FROM emp 
WHERE deptno IN (SELECT /*+ unnest qb_name(qb1) */ deptno FROM dept);

06. 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스 일 때

여태까지 Test한 메인 쿼리의 EMP, 서브쿼리의 DEPT Table이

M:1 관계이기에 일반 조인문으로 바꾸더라도 Query 결과가 보장되었습니다.

옵티마이저가 DEPT Table의 PK제약이 설정된 것을 통해 DEPT Table이 1쪽 집합

이라는 사실을 알 수 있기 때문입니다.

만약 Subquery 쪽 Table이 Join 컬럼에 PK/Unique 제약 또는 Unique Index가 없다면,

일반 조인문처럼 처리했을 때 어떻게 될까요?

<사례1>

[SQL]

SELECT * FROM DEPT

WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);

=> DEPT Table 기준으로 EMP Table을 필터링하는 형태

=> DEPT Table이 기준 집합이므로 결과집합은 DEPT Table의 총 건수를 넘지 않아야 한다.

만약 옵티마이저가 아래와 같은 일반 조인문으로 변경시

M쪽 집합인 EMP Table 단위의 결과집합이 만들어지므로 결과내용에 오류가 생깁니다.

[SQL]

SELECT *

FROM (SELECT DEPTNO FROM EMP) A, DEPT B

WHERE B.DEPTNO = A.DEPTNO;

<사례2>

[SQL]

SELECT * FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT);

M쪽 집합을 Driving해 1쪽 집합을 Subquery로 필터링하도록 작성되었고

Join문으로 바꾸더라도 결과에 오류가 생기지는 않습니다.

하지만 DEPT Table에 PK/Unique제약 또는 Unique Index가 없다면

옵티마이저는 EMP와 DEPT간의 관계를 알수 없고, 결과를 확신할 수 없으니

일반 Join문으로의 Query 변환을 시도하지 않습니다.

이때 옵티마이저는 두 가지 방식 중 하나를 선택하는데, Unnesting 후

어느 쪽 집합이 먼저 Driving 되느냐에 따라 달라집니다.

1) 1쪽 집합임을 확신할 수 없는 Subquery 쪽 Table이 Driving된다면, 먼저 sort unique

오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인합니다.

2) 메인 Query 쪽 Table이 Driving된다면 Semi Join방식으로 Join을 합니다.

[ Sort Unique 오퍼레이션수행 ]

DEPT Table의 PK 제약을 제거,

DEPTNO 컬럼에 Nonunique Index를 생성 후

실행계획을 확인해보도록 하겠습니다.

[SQL]

ALTER TABLE DEPT DROP PRIMARY KEY;

CREATE INDEX DEPT_DEPTNO_IDX ON DEPT(DEPTNO);

SELECT * FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT);

실제로 DEPT Table은 Unique한 집합이지만 옵티마이저는 이를 확신할 수 없어

Sort Unique 오퍼레이션을 수행하고

아래처럼 Query 변환이 일어났습니다.

[SQL]

SELECT B.*

FROM ( SELECT /+ NO_MERGE / DISTINCT DEPTNO FROM DEPT ORDER BY DEPTNO ) A, EMP B

WHERE B.DEPTNO = A.DEPTNO;

결과가 잘 안나타나는데

Query를 아래처럼 변형한 후 Test

[SQL]

SELECT /+ LEADING(DEPT@QB1) USE_NL(EMP) / *

FROM EMP

WHERE DEPTNO IN (SELECT /+ UNNEST QB_NAME(QB1) / DEPTNO FROM DEPT);

[ 세미 조인 방식으로 수행 ]

[SQL]

SELECT * FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT);

NL Semi Join의 수행은

결과집합이 M쪽 집합으로 확장된느 것을 방지하는 알고리즘을 사용

Outer(=Driving) Table의 한 Row가 Inner Table의 한 Row와 Join에 성공하는 순간 진행을 멈추고

Outer Table의 다음 Row를 계속 처리하는 방식

옵티마이저가 NL Semi Join 수행 안할 때는 아래 처럼 Hint를 주어 사용 가능

[SQL]

SELECT /+ LEADING(EMP) / * FROM EMP

WHERE DEPTNO IN (SELECT /+ UNNEST NL_SJ / DEPTNO FROM DEPT);

07. 필터 오퍼레이션과 세미조인의 캐싱 효과

옵티마이저가 Query 변환을 수행하는 이유는, 더 나은 실행계획을 수립할 가능성을 높이는데 있으며

다양한 조인 방식의 선택과, 조인 순서에 있습니다.

Subquery를 Unnesting 하지 않으면 최적화 하는데 제한적이며 불리합니다.

=> 단순히 메인 쿼리를 수행하면서 건건히 Subquery Block을 매번 수행(Filter Operation)해야 하기 때문

=> 대량의 집합 기준으로 Random Access 방식으로 Subquery 집합을 필터한다면 수행속도 나쁘며

=> 유일한 Filter에 대한 최적화 기법의 한가지는 Subquery 수행 결과를 버리지 않고

내부 Cache에 저장하고 있다 같은 값이 입력되면 저장된 값을 출력

08. Anti 조인

Not Exists, Not In Subquery도 Unnesting 하지 않으면

Filter 방식으로 처리된다.

[SQL]

SELECT * FROM DEPT D

WHERE NOT EXISTS

(SELECT /+ NO_UNNEST / 'X' FROM EMP WHERE DEPTNO = D.DEPTNO);

[ EXISTS 필터 ] : Join에 성공하는 (서브) Record를 만나는 순간 결과집합에 담고

다른 (메인) Record로 이동한다.

[ NOT EXISTS 필터 ] : Join에 성공하는 (서브) Record를 만나는 순간 버리고

다음 (메인) Record로 이동한다.

Join에 성공하는 (서브) Record가 하나도 없을 때만 결과집합에 담는다.

똑같은 Query를 Unnesting 하면 아래와 같이 Anti Join 방식으로 처리

[SQL]

SELECT * FROM DEPT D

WHERE NOT EXISTS

(SELECT /+ UNNEST NL_AJ / 'X' FROM EMP WHERE DEPTNO = D.DEPTNO);

[SQL]

SELECT * FROM DEPT D

WHERE NOT EXISTS

(SELECT /+ UNNEST MERGE_AJ / 'X' FROM EMP WHERE DEPTNO = D.DEPTNO);

09. 집계 서브쿼리 제거

집계 함수를 포함하는 Subquery를 Unnesting 하고,

이를 다시 분석 함수로 대체하는 Query는 10g에서 도입되었습니다.

[SQL]

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

AND E.SAL >= (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO);

위 Query를 Unnesting하면 1차적으로 아래와 같은 Query가 만들어집니다.

[SQL]

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL

FROM (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) X, EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

AND E.DEPTNO = X.DEPTNO

AND E.SAL >= X.AVG_SAL;

옵티마이저는 한 번 더 Query 변환을 시도해 Inline View를 Merging하거나 그대로 둔 채 최적화 할 수 있습니다.

10g부터 옵티마이저가 선택할 수 있는 옵션이 한 가지 더 추가되었는데,

Subquery로부터 전환된 Inline View를 제거하고 아래와 같이 메인 Query에 분석 함수를

사용하는 형태로 변환하는 것입니다.

이 기능은 _REMOVE_AGGR_SUBQUERY 파라미터에 의해 제어되며, 비용기반으로 작동

[SQL]

SELECT DEPTNO, DNAME, EMPNO, ENAME, SAL

FROM (

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL

,(CASE WHEN E.SAL >= AVG(SAL) OVER (PARTITION BY D.DEPTNO)

THEN E.ROWID END) MAX_SAL_ROWID

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

)

WHERE MAX_SAL_ROWID IS NOT NULL;

[ 아래는 집계 Subquery 제거 기능이 작동 ] 했을 때의 실행계획입니다.

Query에선 EMP Table을 두 번 참조했지만 실행계획상으로는 한 번만 Access했고,

대신 Window Buffer Operation 단계가 추가 되었습니다.

[SQL]

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL

FROM DEPT D, EMP E

WHERE D.DEPTNO = E.DEPTNO

AND E.SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO);

[ 아래는 집계 Subquery 제거 기능이 작동하지 못하도록 파라미터를 변경했을 때 ]

[SQL]

ALTER SESSION SET "_REMOVE_AGGR_SUBQUERY" = FALSE;

SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL

FROM DEPT D, EMP E

WHERE D.DEPTNO = E.DEPTNO

AND E.SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO);

10. Pushing 서브쿼리

- Unnesting 되지 않은 Subquery는 항상 Filter 방식으로 처리되며,

대개 실행계획 상에서 맨 마지막 단계에 처리

만약 Subquery Filtering을 먼저 처리했을 때 다음 수행 단계로 넘어가는 Row 수를 크게

줄일 수 있다면 성능은 그만큼 향상된다.

[ Pushing 서브쿼리 ] : 실행계획 상 가능한 앞 단계에서 Subquery Filtering이 처리되도록 강제하는 것

이를 제어하하기 위해 사용하는 옵티마이저 힌트로는

[ PUSH_SUBQ ]

※ Pushing Subquery는 Unnesting 되지 않은 Subquery에만 작동!!

따라서 PUSH_SUBQ 힌트는 NO_UNNEST 힌트와 같이 기술하는 것이 올바른 사용법!!!

Oracle 버전에 따라 Push_subq 힌트 사용 위치가 다르다는 점도 아래를 통해 알 수 있습니다.

[9i에서 Push_subq 힌트 사용시]

SELECT /+ PUSH_SUBQ LEADING(E1) USE_NL(E2) / SUM(E1.SAL), SUM(E2.SAL)

FROM EMP1 E1, EMP2 E2

WHERE E1.NO = E2.NO

AND ~~~~~~~~

AND EXISTS (SELECT /+ NO_UNNEST / 'X' FROM DEPT

WHERE ~~~~~~~~

AND ~~~~~~~~);

[10g에서 Push_subq 힌트 사용시]

SELECT /+ LEADING(E1) USE_NL(E2) / SUM(E1.SAL), SUM(E2.SAL)

FROM EMP1 E1, EMP2 E2

WHERE E1.NO = E2.NO

AND ~~~~~~~~

AND EXISTS (SELECT /+ NO_UNNEST PUSH_SUBQ / 'X' FROM DEPT

WHERE ~~~~~~~~

AND ~~~~~~~~);

[Pushing 서브쿼리를 통해 성능 개선 효과가 나타나는지 테스트]

[Script]

CREATE TABLE EMP1 AS

SELECT * FROM EMP, (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 1000);

CREATE TABLE EMP2 AS SELECT * FROM EMP1;

ALTER TABLE EMP1 ADD CONSTRAINT EMP1_PK PRIMARY KEY(NO, EMPNO);

ALTER TABLE EMP2 ADD CONSTRAINT EMP2_PK PRIMARY KEY(NO, EMPNO);

[SQL]

SELECT /+ LEADING(E1) USE_NL(E2) / SUM(E1.SAL), SUM(E2.SAL)

FROM EMP1 E1, EMP2 E2

WHERE E1.NO = E2.NO

AND E1.EMPNO = E1.EMPNO

AND EXISTS (SELECT /+ NO_UNNEST NO_PUSH_SUBQ / 'X'

FROM DEPT WHERE DEPTNO = E1.DEPTNO

AND LOC = 'NEW YORK');

아래는 EMP1과 EMP2 Table을 Join하고 나서 Subquery Filtering을 수행할 때의 트레이스 결과

EMP2 Table과의 Join 시도 횟수가 14,000번이었지만 여기서는

Subquery를 Filtering한 결과 건수가 3,000건이므로 EMP2 Table과의 Join 횟수도 3,000번으로 줄었습니다.

읽은 Block 수도 28,103개에서 6,103개로 줄었다.

[SQL]

SELECT /+ LEADING(E1) USE_NL(E2) / SUM(E1.SAL), SUM(E2.SAL)

FROM EMP1 E1, EMP2 E2

WHERE E1.NO = E2.NO

AND E1.EMPNO = E1.EMPNO

AND EXISTS (SELECT /+ NO_UNNEST PUSH_SUBQ / 'X'

FROM DEPT WHERE DEPTNO = E1.DEPTNO

AND LOC = 'NEW YORK');

아래는 EMP1과 EMP2 Table을 Join하고 나서 Subquery Filtering을 수행할 때의 트레이스 결과

※ Pushing Subquery는 Unnesting 되지 않은 Subquery의 처리 순서를 제어하는 기능