상관 서브쿼리 FILTER 실행: Starts·조기 종료·Semi/Anti Join

핵심 요약

상관 서브쿼리는 안쪽 Query가 바깥 Row의 Column을 참조하는 서브쿼리입니다. Optimizer가 이를 Join으로 Unnesting하지 않으면, 바깥 Row를 읽으면서 서브쿼리를 평가하는 FILTER 형태로 실행될 수 있습니다.

Outer Row 생성
→ 상관 Key를 Subquery에 전달
→ Inner Row Source 탐색
→ TRUE·FALSE·UNKNOWN 판단
→ Outer Row 반환 또는 제거

반복 비용은 단순히 Outer Table 전체 행 수가 아니라 다음 요소로 결정됩니다.

Outer 조건 통과 행 수
× Subquery가 실제로 시작된 횟수
× 1회 Inner 접근 비용

동일한 상관 Key가 반복되면 실행 중 결과 재사용이나 다른 Transformation으로 Subquery Starts가 Outer 행 수보다 작아질 수 있습니다. 다만 재사용의 크기와 충돌 방식은 구현과 실행 조건에 따라 달라질 수 있으므로 고정된 Cache 크기를 전제로 설계하지 않고 실제 Starts, A-Rows, Buffers로 효과를 확인합니다.

상관 서브쿼리의 SQL 의미
→ Outer Row의 값에 따라 Inner 결과 결정

실제 실행 방식
→ FILTER 반복 Probe
→ Scalar 결과 재사용
→ Semi·Anti Join
→ View·Join Transformation

이 이론의 범위

SQLP SQL 고급 활용 및 튜닝 → 서브쿼리와 조인 변환 범위에서 상관 Subquery, FILTER, 반복 Probe, EXISTS·NOT EXISTS, Semi·Anti Join, Unnesting과 실행 통계 분석을 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. 비상관 서브쿼리와 상관 서브쿼리의 차이를 구분한다.
2. FILTER Operation이 Outer 행을 평가하는 구조를 이해한다.
3. FILTER가 항상 상관 서브쿼리만 의미하지는 않는다는 점을 설명한다.
4. Outer A-Rows, 상관 Key NDV, Subquery Starts의 관계를 분석한다.
5. EXISTS와 NOT EXISTS의 조기 종료 원리를 이해한다.
6. Inner Access Predicate와 Table Filter가 반복 비용에 미치는 영향을 찾는다.
7. FILTER 방식과 Semi·Anti Join 방식의 비용 구조를 비교한다.
8. Starts가 Row Source 시작 횟수이며 Cache Hit Counter와 다르다는 점을 설명한다.
9. NULL 상관 Key와 EXISTS·NOT EXISTS 결과를 판단한다.
10. Semi·Anti Join에서 Inner 중복이 Outer Cardinality에 미치는 영향을 설명한다.
11. 첫 행 응답과 전체 결과 처리시간을 분리해 평가한다.

1. 비상관 서브쿼리와 상관 서브쿼리

1.1 비상관 서브쿼리

안쪽 Query가 바깥 Row를 참조하지 않습니다.

SELECT e.employee_id,
       e.salary
FROM   employees e
WHERE  e.salary > (
           SELECT AVG(salary)
           FROM   employees
       );

평균 급여는 바깥 사원마다 다른 값이 필요하지 않습니다. Optimizer는 Subquery를 한 번 계산하거나 다른 형태로 변환할 수 있습니다.

비상관 Subquery도 실행계획에서 FILTER와 별도 Row Source로 나타날 수 있으므로 FILTER가 보인다는 사실만으로 상관 실행이라고 판단하지 않습니다.

1.2 상관 서브쿼리

안쪽 Query가 현재 바깥 Row의 값을 참조합니다.

SELECT e.employee_id,
       e.department_id,
       e.salary
FROM   employees e
WHERE  e.salary > (
           SELECT AVG(x.salary)
           FROM   employees x
           WHERE  x.department_id = e.department_id
       );

안쪽 평균은 e.department_id에 따라 달라집니다. 논리적으로는 현재 사원의 부서번호를 전달해 부서별 평균을 계산합니다.

사원 1의 DEPARTMENT_ID → Subquery 평가
사원 2의 DEPARTMENT_ID → Subquery 평가
...

동일한 부서번호가 반복되면 같은 입력 Key에 대한 결과가 반복될 수 있습니다. Oracle 공식 설명상 상관 Subquery는 개념적으로 Parent Row마다 평가되지만 Optimizer는 Join 또는 다른 의미적으로 동등한 방식으로 Rewrite할 수 있습니다.

Conceptual Evaluation
→ Parent Row마다 상관값 전달

Physical Execution
→ 반드시 Parent Row 수만큼 완전 실행된다는 보장은 없음

실제 실행에서 어느 정도 반복·재사용·변환됐는지는 Plan 통계로 확인합니다.

2. FILTER Operation의 의미

다음 존재 조건을 사용합니다.

SELECT o.order_id,
       o.customer_id
FROM   orders o
WHERE  EXISTS (
           SELECT 1
           FROM   customers c
           WHERE  c.customer_id = o.customer_id
           AND    c.status = 'ACTIVE'
       );

Unnesting되지 않은 개념 실행계획은 다음과 같을 수 있습니다.

FILTER
  TABLE ACCESS ... ORDERS
  TABLE ACCESS BY INDEX ROWID CUSTOMERS
    INDEX UNIQUE SCAN CUSTOMERS_PK

ORDERS에서 행을 만들고, 그 행의 CUSTOMER_ID로 CUSTOMERS를 탐색해 ACTIVE 고객인지 확인합니다.

FILTER는 일반적인 조건 평가 Operation이므로 실행계획에 보인다고 항상 상관 서브쿼리 또는 Cache가 존재한다고 단정하지 않습니다.

FILTER는 다음 상황에도 나타날 수 있습니다.

• 일반 Boolean Predicate 평가
• 비상관 Subquery 조건
• Null-aware Anti Join 보조 Predicate
• 실행 중 한 번만 평가되는 조건

다음을 함께 확인합니다.

• FILTER 아래에 별도 Subquery Row Source가 있는가?
• Predicate Information에 어떤 조건이 남아 있는가?
• Subquery Root의 Starts는 얼마인가?
• Outer Row Source의 A-Rows와 어떤 관계인가?
• Query Block Name·Object Alias로 어느 Subquery인지 확인되는가?
• Note에 Unnesting·Transformation 관련 정보가 있는가?

Starts는 해당 Operation이 시작된 횟수입니다. 내부 Cache Hit·Miss 횟수나 PL/SQL 함수 호출 횟수와 동일한 지표라고 단정하지 않습니다.

3. 반복 비용 계산

FILTER 방식의 기본 비용은 다음처럼 생각할 수 있습니다.

전체 비용
≈ Outer Row Source 생성 비용
 + 실제 Subquery 반복 작업량

실제 Subquery 반복 작업량
≈ Subquery Starts × Starts당 Inner 평균 작업량

Outer Row 수를 다시 곱하지 않습니다. Starts 자체가 실제 반복 시작 횟수를 이미 나타냅니다.

예를 들어 실행 통계가 다음과 같습니다.

ORDERS TABLE ACCESS       A-Rows = 1,000,000
CUSTOMER Subquery Root    Starts =   950,000
CUSTOMER Index Scan       Buffers = 2,850,000

Subquery가 거의 Outer 행 수만큼 시작됐고, Index Scan에서 약 285만 Buffer를 사용했습니다.

1회 평균 Buffer
≈ 2,850,000 ÷ 950,000
≈ 3 Blocks

한 번의 Probe가 3 Buffer로 작아 보여도 95만 번 반복되면 전체 부하는 큽니다.

A-Rows와 Buffers는 Plan Row Source에서 누적값으로 표시될 수 있으므로 다음 평균을 함께 계산합니다.

Starts당 Inner 반환 Row
= Inner A-Rows ÷ Subquery Starts

Starts당 Inner Buffer
= Inner Buffers ÷ Subquery Starts

다만 A-Rows=0이어도 Index Block을 여러 개 읽을 수 있으므로 반환 Row 수만으로 Probe 비용을 판단하지 않습니다.

반대로 Outer 1,000행, Subquery Starts 20, Inner Buffers 60이라면 반복 Key 재사용이나 다른 최적화로 실제 Subquery 작업량이 매우 작을 수 있습니다.

4. 상관 Key NDV와 재사용 효과

다음 세 값을 비교합니다.

Outer A-Rows
상관 Key의 NDV
Subquery Starts

예를 들어 주문 100만 건이 100개 고객에 집중되어 있습니다.

Outer A-Rows       = 1,000,000
CUSTOMER_ID NDV    =       100
Subquery Starts    =       120

동일 Key가 많이 반복되므로 Subquery 결과 재사용 효과가 큰 실행일 수 있습니다. 반대로 고객번호가 거의 모두 다르면 다음처럼 나타날 수 있습니다.

Outer A-Rows       = 1,000,000
CUSTOMER_ID NDV    =   990,000
Subquery Starts    =   980,000

이 경우 Cache나 재사용만으로 반복 비용을 줄이기 어렵습니다.

다음 점을 함께 고려합니다.

• Cache 크기와 Hash 충돌은 고정된 SQL 설계 규칙으로 가정하지 않음
• 동일 Key가 반복되어도 Optimizer Transformation에 따라 Plan 구조가 달라질 수 있음
• Outer 입력 순서를 바꾸는 추가 Sort는 재사용을 늘릴 수 있어도 Sort 비용을 새로 만듦
• 일부 행만 Fetch한 통계와 전체 Fetch 통계를 구분

4.1 상관 Key가 NULL인 경우

다음 Equality 상관 조건을 봅니다.

WHERE i.customer_id = o.customer_id

Outer o.customer_id가 NULL이면 조건은 다음처럼 평가됩니다.

i.customer_id = NULL
→ TRUE가 아니라 UNKNOWN

따라서 NULL끼리도 일반 Equality로 Match하지 않습니다.

EXISTS
→ Equality Match Row가 없으면 FALSE

NOT EXISTS
→ Equality Match Row가 없으면 TRUE

업무에서 NULL을 같은 그룹으로 취급해야 한다면 Null-safe 조건을 명시해야 합니다.

WHERE i.customer_id = o.customer_id
   OR (i.customer_id IS NULL AND o.customer_id IS NULL)

Null-safe 조건은 Index Access와 Unnesting 가능성에 영향을 줄 수 있으므로 정확성과 Plan을 함께 확인합니다.

4.2 NDV와 Starts가 같지 않을 수 있는 이유

NDV
→ 입력에서 관찰한 서로 다른 상관 Key 수

Starts
→ 실행계획 Operation이 실제 시작된 횟수

둘은 다음 이유로 다를 수 있습니다.

• 일부 Outer Row가 앞선 Predicate에서 제거됨
• 동일 Key 재사용·Cache 충돌
• NULL·데이터 분포
• Subquery Unnesting·Transformation
• 부분 Fetch
• Parallel Execution과 Row Source 분배

따라서 Starts = NDV를 기대값으로 고정하지 않습니다.

5. EXISTS와 NOT EXISTS의 조기 종료

EXISTS

EXISTS는 조건을 만족하는 안쪽 행을 하나 찾으면 존재 여부가 결정됩니다.

첫 Match 발견
→ TRUE 결정
→ 해당 Outer Row 반환 가능
→ 남은 Inner 후보를 계속 찾을 필요 없음

효율적인 Index가 있다면 한 번의 Probe가 매우 작을 수 있습니다. EXISTS의 SELECT 목록은 존재 판정에 영향을 주지 않습니다.

EXISTS (SELECT 1 ...)
EXISTS (SELECT NULL ...)
EXISTS (SELECT expensive_column ...)

논리적으로는 조건을 만족하는 Row가 있는지만 판단합니다. 불필요한 표현식이 실제로 평가될지는 Transformation과 Optimizer에 맡기므로 일반적으로 SELECT 1처럼 의도를 명확히 표현합니다.

CREATE INDEX customers_status_ix
    ON customers(customer_id, status);

NOT EXISTS

NOT EXISTS는 조건을 만족하는 안쪽 행을 하나 찾으면 해당 Outer Row가 탈락합니다.

첫 Match 발견
→ NOT EXISTS는 FALSE
→ 해당 Outer Row 제거
→ 남은 Inner 후보 탐색 중단

Match가 없는 Outer Key에서는 Inner 탐색 범위를 끝까지 확인해야 할 수 있습니다. 따라서 존재하는 Key와 존재하지 않는 Key의 비율도 비용에 영향을 줍니다.

EXISTS
Match가 빠르게 존재
→ 조기 성공

NOT EXISTS
Match가 빠르게 존재
→ 조기 실패

NOT EXISTS
끝까지 Match 없음
→ Outer Row 반환, Inner 범위 확인 비용 가능

6. Inner Access Path가 반복 비용을 결정한다

다음 Query를 봅니다.

SELECT o.order_id
FROM   orders o
WHERE  EXISTS (
           SELECT 1
           FROM   order_items i
           WHERE  i.order_id = o.order_id
           AND    i.product_id = :product_id
       );

Index가 (ORDER_ID, PRODUCT_ID)라면 두 조건을 함께 Access 범위로 사용할 수 있습니다.

Outer ORDER_ID
+ Bind PRODUCT_ID
→ 좁은 Index Probe

Index가 (ORDER_ID)만 있다면 PRODUCT_ID는 Table Filter로 평가될 수 있습니다.

ORDER_ID Index Probe
→ 해당 주문의 모든 Item ROWID
→ Table 방문
→ PRODUCT_ID Filter

Outer 행마다 이 과정이 반복되면 불필요한 ROWID Table Access가 커집니다.

존재 여부에 필요한 Column이 모두 Index에 있으면 Table 방문 없이 Index만으로 판정할 수 있습니다.

Index (ORDER_ID, PRODUCT_ID)
→ 두 Equality를 Access
→ 첫 Index Entry Match
→ EXISTS TRUE
→ Table Access 불필요 가능

반대로 STATUS, DELETE_YN 등 추가 조건이 Index에 없으면 Table Filter와 반복 Random Access가 남을 수 있습니다. 다음을 구분합니다.

7. FILTER와 Semi Join 비교

같은 존재 Query가 Unnesting되면 Semi Join 후보가 될 수 있습니다.

FILTER 방식
Outer Row마다 Subquery Probe

SEMI JOIN 방식
두 입력을 하나의 Join Graph에서 최적화

두 방식은 결과 의미가 같아도 비용 구조가 다릅니다.

Semi Join은 Inner에 Match Row가 여러 개 있어도 Outer Row를 한 번만 반환합니다.

Outer 주문 1건
Inner Item Match 10건
→ SEMI JOIN 결과 Outer 주문 1건

일반 Inner Join은 별도 DISTINCT가 없으면 Outer Row가 Match 수만큼 증식할 수 있으므로 EXISTS를 단순 Join으로 수동 변경할 때 결과 Cardinality를 검증합니다.

Anti Join도 Match가 하나라도 발견되면 Outer Row를 제거하고 Inner 중복 수만큼 결과를 늘리지 않습니다.

NO_UNNEST와 기본 Plan을 제한적으로 비교해 FILTER 비용을 관찰할 수 있지만, Hint는 결과 의미를 정의하지 않으며 최종 판단은 Hint 없는 정상 Plan과 실제 업무 조건을 기준으로 합니다.

7.1 Subquery Unnesting의 가능성과 제한

Subquery Unnesting은 Nested Subquery를 Join Graph에 병합해 Access Path, Join Order, Join Method를 함께 최적화하게 합니다.

Oracle은 의미적으로 동등한 결과가 보장될 때만 Transformation을 수행합니다. 다음 구조는 Unnesting을 제한하거나 복잡하게 만들 수 있습니다.

• ROWNUM
• Set Operator
• Nested Aggregate
• Hierarchical Query
• Immediate Parent가 아닌 Query Block과의 Correlation
• Aggregate·GROUP BY가 있는 Correlated Subquery
• 비결정적 함수·부작용
• 복잡한 OR Branch

Unnesting 불가
≠
Query가 잘못됨

Unnesting 가능
≠
항상 Join Plan이 더 빠름

실제 Query Block·Hint Report·Note와 실행 통계로 확인합니다.

8. NULL과 존재 조건

EXISTS는 SELECT 목록의 값이 아니라 조건을 만족하는 행의 존재를 검사합니다.

WHERE EXISTS (
    SELECT NULL
    FROM   customers c
    WHERE  c.customer_id = o.customer_id
)

SELECT NULL이어도 조건을 만족하는 행이 있으면 EXISTS는 TRUE입니다.

반면 IN, NOT IN은 값 비교와 3값 논리의 영향을 받습니다. 특히 NOT IN은 Subquery 결과에 NULL이 하나라도 있거나 Outer 값이 NULL이면 조건 전체가 UNKNOWN이 될 수 있습니다.

NOT EXISTS
→ 상관 Predicate에 실제 Match가 없으면 TRUE
→ Inner의 unrelated NULL이 결과를 오염시키지 않음

NOT IN
→ 비교 집합에 NULL이 있으면 UNKNOWN 가능

Optimizer는 Null-aware Anti Join을 사용할 수 있지만 SQL 결과 의미 자체는 유지됩니다. 문법을 바꾸기 전에 NULL 가능성을 검증합니다.

9. 실제 실행 통계 확인

SELECT /*+ GATHER_PLAN_STATISTICS */
       o.order_id
FROM   orders o
WHERE  EXISTS (
           SELECT 1
           FROM   order_items i
           WHERE  i.order_id = o.order_id
           AND    i.product_id = :product_id
       );

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
    )
);

확인 순서는 다음과 같습니다.

1. 실제 Plan이 FILTER인지 SEMI JOIN인지 확인
2. Outer A-Rows 확인
3. Subquery Root Starts 확인
4. Inner A-Rows ÷ Starts 계산
5. Inner Buffers ÷ Starts 계산
6. Access와 Filter Predicate 위치 확인
7. 전체 Fetch 여부 확인

실행이 첫 20행만 Fetch된 상태라면 Outer와 Subquery Operation도 전체 결과를 처리하지 않았을 수 있습니다. 부분범위 응답과 전체 처리량을 각각 측정합니다.

ALLSTATS LAST는 Plan Statistics가 수집된 마지막 실행의 I/O·Memory 통계를 표시합니다. GATHER_PLAN_STATISTICS Hint 또는 적절한 Statistics 설정이 없으면 실제 통계가 비어 있을 수 있습니다.

첫 행·초기 Fetch
→ 화면 응답·Interactive 목표

전체 Fetch
→ Batch·보고서·전체 처리량 목표

10. 혼동하기 쉬운 판단

실전 적용 순서

1. 상관 Column과 Outer 결과 행 수를 확인한다.
2. 상관 Key NDV와 반복 분포를 확인한다.
3. 실제 Plan이 FILTER인지 Join인지 확인한다.
4. Subquery Starts와 Starts당 Inner A-Rows·Buffers를 계산한다.
5. Starts가 Cache Counter가 아니라 Operation 시작 횟수임을 확인한다.
6. Inner Access·Index Filter·Table Filter와 Index-only 가능성을 분리한다.
7. NULL 상관 Key와 Null-safe Equality의 업무 의미를 검증한다.
8. Match·No-Match 비율과 조기 종료 효과를 확인한다.
9. Unnesting 제한과 Semi·Anti Join 대안의 결과 Cardinality를 비교한다.
10. 첫 행 응답과 전체 Fetch 성능을 각각 검증한다.

핵심 정리

상관 서브쿼리
→ 안쪽 Query가 바깥 Row의 값을 참조

FILTER 실행
→ Outer Row마다 Subquery 조건 평가 가능

반복 비용
→ Subquery Starts × Inner 1회 평균 비용

재사용 효과
→ Outer Key 반복도와 실제 Starts로 확인

튜닝
→ Outer 행 감소, Inner Access 개선, Unnesting·Semi Join 비교