OR Expansion·조건 이행·Join Elimination: 분기·파생 조건·불필요 조인 제거

핵심 요약

이 이론은 결과 동등성을 유지하면서 Optimizer의 탐색 공간을 넓히거나 불필요한 Row Source를 제거하는 세 가지 Transformation을 다룹니다.

OR Expansion
→ Query Block의 최상위 OR 조건을 둘 이상의 UNION ALL Branch로 분리
→ Branch마다 다른 Index·Join Order·Join Method 후보를 검토
→ 중복 행과 NULL의 3값 논리를 보존하는 배타 조건 필요

조건 이행(Transitive Predicate)
→ 등식 관계와 상수·Bind 조건에서 반대편 Predicate를 논리적으로 도출
→ Index Access·Partition Pruning·Cardinality 추정 기회 확대

Join Elimination
→ Join이 결과를 Filter하지도 Multiply하지도 않는다고 증명되면 Table Access 제거
→ Unique·PK, FK, NOT NULL, Join Type, Column 사용 여부, Constraint 신뢰 상태가 핵심

세 변환은 SQL Text를 임의로 단순화하는 기능이 아닙니다. Optimizer는 결과가 같다는 Validity를 먼저 만족해야 하며, 유효한 후보 가운데 Cost가 유리한 실행계획을 선택합니다. Hint를 사용하더라도 실제 Cursor의 Query Block·Predicate·Row Source·Runtime 통계를 확인해야 합니다.

학습 목표

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

1. OR Expansion이 최상위 OR 조건을 UNION ALL Branch로 분리하는 원리를 설명한다.
2. Branch별로 서로 다른 Index·Join Order·Join Method를 선택할 수 있는 이유를 이해한다.
3. 단순 UNION ALL 재작성에서 중복 행이 발생하는 이유를 설명한다.
4. LNNVL이 FALSE와 UNKNOWN을 TRUE로 처리해 중복과 NULL 의미를 보존하는 원리를 이해한다.
5. INLIST ITERATOR와 OR Expansion을 실행계획 구조로 구분한다.
6. USE_CONCAT과 NO_EXPAND의 공식 의미와 비교 실험 시 주의점을 설명한다.
7. 등치 Join Predicate와 상수 조건에서 파생 Predicate를 도출한다.
8. 함수·형변환·NULL·Outer Join이 조건 이행을 제한할 수 있는 이유를 설명한다.
9. Inner Join과 Left Outer Join에서 Join Elimination의 논리 조건을 구분한다.
10. VALIDATE·NOVALIDATE와 QUERY_REWRITE_INTEGRITY가 제약 신뢰에 미칠 수 있는 영향을 이해한다.
11. 실제 실행계획에서 VW_ORE_..., UNION-ALL, LNNVL, 반대편 Access Predicate, 제거된 Table을 검증한다.

1. OR Expansion의 정확한 정의

Oracle의 OR Expansion은 Query Block에 있는 최상위 Disjunction, 즉 논리식의 상위 수준에 놓인 OR 조건을 둘 이상의 UNION ALL Branch로 변환하는 Query Transformation입니다.

SELECT e.employee_id,
       e.email,
       d.department_name
FROM   employees e
JOIN   departments d
  ON   d.department_id = e.department_id
WHERE  e.email = :email
   OR  d.department_name = :department_name;

OR 조건을 하나의 Predicate 단위로 유지하면 다음 두 Access 후보를 한 Join Plan에서 동시에 활용하기 어려울 수 있습니다.

E.EMAIL = :EMAIL
→ EMPLOYEES의 EMAIL Index 후보

D.DEPARTMENT_NAME = :DEPARTMENT_NAME
→ DEPARTMENTS의 DEPARTMENT_NAME Index 후보

OR Expansion이 적용되면 개념적으로 다음처럼 Branch가 분리됩니다.

VIEW VW_ORE_...
└─ UNION-ALL
   ├─ Branch 1: EMAIL 조건 중심 Plan
   └─ Branch 2: DEPARTMENT_NAME 조건 중심 Plan

각 Branch는 별도의 Query Block처럼 최적화될 수 있으므로 서로 다른 Driving Table, Index, Join Order, Join Method를 선택할 수 있습니다.

2. OR Expansion은 비용 기반 선택이다

OR Expansion은 OR이 있다는 이유만으로 항상 적용되지 않습니다. Optimizer는 원래 형태와 Branch 분리 형태의 Cost를 비교합니다.

유리할 수 있는 상황

• OR의 각 조건이 서로 다른 선택적 Index를 사용할 수 있음
• 각 Branch에서 유리한 Driving Table이 다름
• 하나의 OR 조건 때문에 Full Scan 또는 비효율적인 Join이 발생함
• Branch별 Cardinality가 작아 반복 Join 비용이 제한적임

불리할 수 있는 상황

• OR 항목이 많아 Branch 수가 크게 증가함
• 각 Branch에서 동일한 큰 Table이나 Join을 반복함
• 대부분의 조건이 선택적이지 않아 Branch 분리 이익이 작음
• Hard Parse와 Costing 복잡도가 증가함
• SQL Plan이 지나치게 커져 진단과 관리가 어려움

Oracle 12.2 이후의 비용 기반 OR Expansion은 실행계획에 일반적으로 UNION-ALL Row Source를 사용합니다. 이전 방식에서 볼 수 있었던 CONCATENATION Operation과 구분해야 합니다.

3. 단순 UNION ALL 재작성과 중복 문제

원래 OR 조건은 같은 원본 행이 두 조건을 모두 만족해도 그 행을 한 번만 반환합니다.

조건 A = TRUE
조건 B = TRUE
A OR B = TRUE
→ 원본 행 한 번 반환

그러나 다음처럼 배타 조건 없이 두 Query를 UNION ALL로 연결하면 같은 행이 두 Branch에서 반환될 수 있습니다.

SELECT ... WHERE condition_a
UNION ALL
SELECT ... WHERE condition_b;

Branch 1에서 반환
Branch 2에서도 반환
→ 동일 원본 행이 두 번 나타남

UNION으로 바꾸면 중복 제거 Sort 또는 Hash 비용이 추가되고, 원본 SQL이 원래부터 생성하던 서로 다른 중복 행까지 제거할 수 있어 의미가 달라질 수 있습니다. 따라서 OR Expansion은 UNION ALL을 사용하면서 Branch 사이에 배타 조건을 둡니다.

4. LNNVL과 NULL의 3값 논리

두 번째 Branch에 다음 조건이 추가될 수 있습니다.

WHERE condition_b
AND   LNNVL(condition_a)

LNNVL(condition)의 반환은 다음과 같습니다.

따라서 첫 번째 Branch의 조건이 TRUE였던 행은 두 번째 Branch에서 제외되고, FALSE 또는 NULL 때문에 UNKNOWN이었던 행은 두 번째 Branch의 다른 조건을 만족할 때 반환될 수 있습니다.

예를 들어 다음 구조를 생각할 수 있습니다.

SELECT ...
WHERE  e.email = :email

UNION ALL

SELECT ...
WHERE  d.department_name = :department_name
AND    LNNVL(e.email = :email);

LNNVL은 단순한 NOT의 동의어가 아닙니다. SQL의 NOT UNKNOWN은 여전히 UNKNOWN이지만, LNNVL은 FALSE와 UNKNOWN을 모두 TRUE로 처리합니다. 이 차이가 NULL이 포함된 OR 조건의 의미 보존에 중요합니다.

5. OR Expansion과 INLIST ITERATOR

다음 조건은 하나의 Column에 여러 Key 값을 전달합니다.

WHERE status IN ('READY', 'RUN', 'HOLD')

Index가 이를 구현하면 실행계획에 다음 Row Source가 나타날 수 있습니다.

INLIST ITERATOR
  TABLE ACCESS BY INDEX ROWID ...
    INDEX RANGE SCAN STATUS_IX

INLIST ITERATOR는 IN 목록의 각 값마다 다음 Operation을 반복합니다. 하나의 Access 구조에 여러 Key를 전달하는 방식입니다.

반면 다음 OR 조건은 서로 다른 Column이나 Table의 Access Path를 요구합니다.

WHERE customer_id = :customer_id
   OR order_no    = :order_no

OR Expansion
→ Query를 여러 UNION ALL Branch로 나눔
→ Branch별로 CUSTOMER_ID Index와 ORDER_NO Index를 독립적으로 선택 가능

구분 기준은 다음과 같습니다.

IN이 항상 INLIST로 처리되거나 OR이 항상 Expansion되는 것은 아닙니다. Cost, Index, Partitioning, Predicate 구조에 따라 Full Scan, Bitmap 연산, 다른 Transformation이 선택될 수 있습니다.

6. USE_CONCAT과 NO_EXPAND

OR Expansion을 비교 실험할 때 다음 Hint를 사용할 수 있습니다.

SELECT /*+ USE_CONCAT */ ...

USE_CONCAT은 결합된 OR 조건을 UNION ALL Compound Query로 변환하도록 지시하며, 공식 문서상 일반적인 비용 판단을 우회합니다. 따라서 단순히 “후보를 한 번 더 검토”하는 약한 Hint로 이해하면 안 됩니다.

SELECT /*+ NO_EXPAND */ ...

NO_EXPAND는 WHERE 절의 OR 조건이나 IN 목록에 대해 OR Expansion을 고려하지 않도록 지시합니다.

다만 Hint를 넣었다는 사실만으로 원하는 최종 Plan을 보장할 수는 없습니다. Query Block 지정 오류, 다른 Transformation, Object 상태, Version, Hint 충돌 등을 고려하고 실제 Cursor의 Outline과 Note에서 적용 여부를 확인해야 합니다.

비교 항목은 다음과 같습니다.

Branch 수와 각 Branch의 Starts
Branch별 Access Path·Join Order·Join Method
Predicate Information의 LNNVL 또는 배타 조건
총 Buffers·Reads·A-Time
Hard Parse와 Plan 복잡도
Bind가 NULL인 경우
두 OR 조건을 동시에 만족하는 경우
어느 조건도 만족하지 않는 경우
결과 행 수와 중복

7. 조건 이행: 등식에서 새 Predicate 도출

다음 Query를 살펴봅니다.

SELECT o.order_id,
       c.customer_name
FROM   orders o
JOIN   customers c
  ON   c.customer_id = o.customer_id
WHERE  o.customer_id = :customer_id;

논리적으로 다음 관계가 성립합니다.

C.CUSTOMER_ID = O.CUSTOMER_ID
O.CUSTOMER_ID = :CUSTOMER_ID
--------------------------------
C.CUSTOMER_ID = :CUSTOMER_ID

Optimizer는 결과 동등성이 안전하면 SQL Text에 직접 쓰지 않은 반대편 Predicate를 내부적으로 사용할 수 있습니다.

ORDERS
→ CUSTOMER_ID Index Access 후보

CUSTOMERS
→ PK 또는 Unique Index Access 후보

실행계획 Predicate Information에는 다음과 같은 Access Predicate가 보일 수 있습니다.

access("C"."CUSTOMER_ID"=:CUSTOMER_ID)

Partition Key가 등식 관계에 연결되어 있다면 반대편 Partition Pruning 기회도 생길 수 있습니다.

8. 조건 이행을 기계적으로 적용하면 안 되는 경우

등식의 수학적 추론만 보고 개발자가 임의로 조건을 추가하면 결과 의미가 달라질 수 있습니다.

데이터 타입과 함수

ON TO_CHAR(a.id) = b.id_char

단순 A.ID = B.ID와 같은 Column 등식이 아니며 형변환, 형식, 오류 가능성, Index 사용 조건이 다릅니다.

NULL

SQL의 비교 결과는 TRUE·FALSE·UNKNOWN입니다. NULL 허용 Column에서는 파생 Predicate가 행 보존 의미에 영향을 줄 수 있습니다.

Outer Join

SELECT ...
FROM   orders o
LEFT JOIN customers c
  ON c.customer_id = o.customer_id
WHERE  o.order_status = 'OPEN';

왼쪽 Preserved Table의 조건을 오른쪽 Optional Table에 무조건 복제하면 Match가 없는 왼쪽 행을 제거하거나 NULL 확장 의미를 바꿀 수 있습니다.

비등치와 경계

A.DATE_VALUE >= :START_DATE
A.DATE_VALUE <  :END_DATE

다른 Column에 조건을 이행하려면 데이터 타입, 시간대, 함수, 경계 포함 규칙까지 동일해야 합니다.

개발자가 파생 조건을 명시적으로 추가할 때는 다음을 확인합니다.

양쪽 표현식의 데이터 타입과 비교 규칙이 같은가?
NULL일 때 TRUE·FALSE·UNKNOWN 결과가 같은가?
Outer Join의 Preserved Row가 유지되는가?
함수가 결정적이며 동일한 변환을 보장하는가?
기간 경계와 시간대가 동일한가?

9. Join Elimination의 논리 조건

Join Elimination은 Table이 작거나 Cache에 있기 때문에 제거하는 기능이 아닙니다. 해당 Join이 최종 결과에 아무 영향도 주지 않는다는 논리적 보장이 있어야 합니다.

SELECT o.order_id
FROM   orders o
JOIN   customers c
  ON   c.customer_id = o.customer_id;

CUSTOMERS의 Column을 SELECT, Filter, Grouping, Ordering, 함수, 다른 Join에 사용하지 않는다고 가정합니다.

Join이 제거되려면 다음 두 성질을 검토해야 합니다.

Non-duplicating
→ 한 왼쪽 행에 오른쪽 Match가 최대 한 행
→ 오른쪽 Join Key의 PK 또는 Unique 보장

Lossless
→ 제거 전 Join이 왼쪽 행을 탈락시키지 않음
→ 유효한 FK와 NULL 배제 조건 등으로 Match 존재 보장

두 성질이 충족되면 CUSTOMERS를 읽지 않아도 결과가 같을 수 있습니다.

10. Inner Join의 제거 조건

Child Table인 ORDERS에서 Parent Table인 CUSTOMERS로 Inner Join한다고 가정합니다.

10.1 At most one match

CUSTOMERS.CUSTOMER_ID가 PK 또는 Unique
→ 한 주문이 둘 이상의 고객 행과 Match해 결과가 증가하지 않음

10.2 At least one match

ORDERS.CUSTOMER_ID가 유효한 FK
→ 비NULL FK 값은 Parent에 존재

ORDERS.CUSTOMER_ID가 NOT NULL
또는 Query가 O.CUSTOMER_ID IS NOT NULL을 보장
→ NULL FK 때문에 Inner Join에서 Child 행이 제거되지 않음

FK Column이 Nullable이면 FK Constraint는 NULL을 허용합니다. 원래 Inner Join은 NULL FK 행을 제거하지만 Parent Join을 없애면 그 행이 남을 수 있으므로 결과가 달라집니다.

다음처럼 Query 자체가 NULL을 배제하는 경우에는 Column 정의가 Nullable이어도 Lossless 조건을 충족할 가능성이 생깁니다.

SELECT o.order_id
FROM   orders o
JOIN   customers c
  ON   c.customer_id = o.customer_id
WHERE  o.customer_id IS NOT NULL;

실제 Elimination은 Query 형태와 Optimizer 판단에 따라 Plan으로 확인해야 합니다.

11. Left Outer Join의 제거 조건

SELECT o.order_id
FROM   orders o
LEFT JOIN customers c
  ON c.customer_id = o.customer_id;

Left Outer Join은 Match가 없어도 왼쪽 행을 보존합니다. 오른쪽 Column을 전혀 사용하지 않고 오른쪽 Join Key가 Unique라면 다음이 성립할 수 있습니다.

Match 없음
→ 왼쪽 행 한 행 유지

Match 한 행
→ 왼쪽 행 한 행 유지

Match 여러 행
→ 행 증가 발생

따라서 오른쪽에 최대 한 행만 Match한다는 PK·Unique 보장이 있으면 Match 존재를 보장하는 FK·NOT NULL의 중요성이 Inner Join보다 낮을 수 있습니다.

다만 다음 경우에는 제거할 수 없습니다.

• 오른쪽 Column을 SELECT 또는 Filter에서 사용
• 오른쪽 Table의 행 존재 여부로 결과를 구분
• Unique 보장이 없어 한 왼쪽 행이 여러 오른쪽 행과 Match 가능
• Join Predicate가 추가 조건을 포함해 결과 의미가 달라짐

12. Constraint 상태와 신뢰 위험

Optimizer는 선언된 Constraint를 Transformation의 논리 근거로 사용할 수 있습니다. 다음을 함께 확인합니다.

• PK·Unique Constraint의 Column과 상태
• FK Constraint의 Column과 참조 대상
• Child FK Column의 NOT NULL
• ENABLE VALIDATE 또는 NOVALIDATE 상태
• RELY 설정
• QUERY_REWRITE_INTEGRITY 설정
• 실제 데이터가 선언 관계를 지키는지

ENABLE VALIDATE는 기존 데이터와 신규 DML에 대해 Constraint를 검증·강제하는 가장 강한 근거입니다.

NOVALIDATE는 기존 데이터 전체가 Constraint를 만족한다고 보장하지 않습니다. Oracle 공식 문서는 Foreign Key가 NOVALIDATE이고 기존 데이터가 위반하더라도 QUERY_REWRITE_INTEGRITY가 ENFORCED가 아니면 Optimizer가 Join Elimination을 사용할 수 있다고 경고합니다.

따라서 다음 판단이 중요합니다.

NOVALIDATE이므로 Optimizer가 절대 사용하지 않는다
→ 잘못된 단정

NOVALIDATE이므로 언제나 안전하게 사용해도 된다
→ 위험한 단정

Constraint 상태·Integrity 설정·실제 데이터·실제 Plan을 함께 확인한다
→ 올바른 접근

성능을 위해 사실과 다른 PK·FK·NOT NULL·RELY 정보를 선언하면 Optimizer가 잘못된 관계를 사실로 가정해 결과 오류를 만들 수 있습니다.

13. 실행계획에서 세 변환 확인하기

13.1 OR Expansion

VIEW VW_ORE_...
  UNION-ALL
    Branch 1 Access
    Branch 2 Access

Predicate Information에서 다음을 확인합니다.

• Branch별 OR 구성 조건
• LNNVL(...) 또는 동등한 배타 Predicate
• 각 Branch의 Index Access
• Branch별 Join Order와 Join Method

13.2 INLIST ITERATOR

INLIST ITERATOR
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN

하나의 다음 Operation을 IN 목록 값마다 반복하는지 확인합니다.

13.3 조건 이행

SQL Text에 없는 반대편 조건이 다음 위치에 나타날 수 있습니다.

• INDEX RANGE SCAN 또는 INDEX UNIQUE SCAN의 Access Predicate
• Table Filter Predicate
• Partition Start·Stop Key

13.4 Join Elimination

SQL Text에는 Table이 있지만 실제 Plan에는 해당 Table Access가 없을 수 있습니다. 그러나 Table이 사라졌다는 사실만으로 Join Elimination이라고 단정하지 않습니다.

함께 확인할 항목은 다음과 같습니다.

• Alias와 Query Block
• Outline Data
• Predicate Information
• View Merging
• Subquery Unnesting
• Materialized View Rewrite
• 제거된 Column이 실제로 다른 표현식에 사용되지 않는지
• 최종 결과 행 수

14. 실제 Cursor와 Runtime 통계 확인

예상 Plan보다 실제 실행 Cursor를 우선 확인합니다.

SELECT /*+ GATHER_PLAN_STATISTICS */ ...;

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

주요 확인 항목은 다음과 같습니다.

E-Rows와 A-Rows 차이
각 Branch 또는 반복 Row Source의 Starts
총 Buffers·Reads·A-Time
A-Rows ÷ Starts
Buffers ÷ Starts
실제 사용된 Predicate와 Object Alias
Hint가 Outline에 반영됐는지

예를 들어 한 Row Source의 통계가 다음과 같다고 가정합니다.

Starts  = 2,000
A-Rows  = 2,000
Buffers = 40,000

A-Rows/Start  = 1
Buffers/Start = 20

1회당 한 행을 찾더라도 2,000번 반복되어 총 40,000 Buffer를 사용했습니다. OR Expansion 전후에는 Branch별 국소 비용뿐 아니라 전체 Branch의 합계 비용을 비교해야 합니다.

15. 비교 실험 절차

1. OR의 각 조건과 동일 행의 동시 만족 가능성을 표시한다.
2. NULL Bind·NULL Column·두 조건 동시 TRUE 데이터를 준비한다.
3. 원본 SQL의 결과 행과 중복을 기준값으로 저장한다.
4. 기본 Plan에서 VW_ORE·UNION-ALL·LNNVL·INLIST 여부를 확인한다.
5. USE_CONCAT과 NO_EXPAND를 Query Block에 정확히 적용해 비교한다.
6. Branch별 Starts·A-Rows·Buffers·A-Time과 전체 합계를 비교한다.
7. Join 등식에서 도출 가능한 반대편 Predicate를 표시한다.
8. 데이터 타입·함수·NULL·Outer Join 때문에 이행이 안전한지 검증한다.
9. 제거 후보 Table의 Column이 SELECT·Filter·Group·Order·다른 Join에 쓰이는지 확인한다.
10. PK·Unique·FK·NOT NULL·VALIDATE·RELY·Integrity 설정을 확인한다.
11. SQL Text와 실제 Cursor의 Alias·Outline·Predicate·Row Source를 대조한다.
12. 다양한 Bind 조합에서 결과와 성능이 모두 안정적인지 검증한다.

16. 혼동하기 쉬운 판단

핵심 정리

OR Expansion
→ 최상위 OR을 비용 기반 UNION ALL Branch로 분리
→ Branch별 다른 Access·Join 후보 허용
→ LNNVL 또는 배타 Predicate로 중복·NULL 의미 보존

INLIST ITERATOR
→ 같은 Access Operation을 IN 목록 값마다 반복

조건 이행
→ 등식과 상수 조건에서 반대편 Predicate 도출
→ 함수·형변환·NULL·Outer Join 의미 확인 필요

Join Elimination
→ Non-duplicating + Lossless + 제거 Table Column 미사용
→ Inner Join은 Match 존재와 NULL 배제가 중요
→ Left Outer Join은 오른쪽 최대 한 행 Match가 핵심일 수 있음

Constraint
→ VALIDATE·NOVALIDATE·RELY·QUERY_REWRITE_INTEGRITY·실제 데이터 확인

검증
→ 실제 Cursor의 UNION-ALL·LNNVL·INLIST·Predicate·Alias·Outline·Runtime 통계 확인