Query Transformation 기초: Query Block·동등 변환·Plan 탐색

핵심 요약

Query Transformation은 Optimizer가 SQL의 결과 의미를 보존하면서 논리적 형태를 바꾸는 과정입니다. 저장된 SQL Text를 보기 좋게 수정하는 기능이 아니라, 원래 구조에서 제한되어 있던 Join Order·Join Method·Access Path·Predicate 적용 위치·집계 위치 등의 후보를 넓히기 위한 Optimizer 내부 작업입니다.

SQL Parse
→ Query Block 식별
→ 의미적으로 동등한 변환 후보 검토(Validity)
→ Selectivity·Cardinality·Cost 추정
→ Access Path·Join Order·Join Method 후보 비교
→ 최종 실행계획 선택

가장 중요한 구분은 다음 두 가지입니다.

• Transformation: Subquery Unnesting, View Merging, Predicate Pushing, OR Expansion처럼 Query의 논리적 모양을 바꾸는 단계
• Execution Operation: HASH JOIN SEMI, NESTED LOOPS, INDEX RANGE SCAN, UNION-ALL처럼 실제로 행을 처리하는 물리 Operation

따라서 HASH JOIN SEMI가 보인다는 사실과 Subquery Unnesting이라는 개념은 관련될 수 있지만 같은 용어는 아닙니다. 또한 변환이 문법적으로 가능하더라도 결과 동등성을 증명할 수 없으면 적용할 수 없고, 동등성이 보장되더라도 Cost가 불리하면 최종 후보로 선택되지 않을 수 있습니다.

학습 목표

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

1. Query Transformer·Estimator·Plan Generator의 역할을 구분한다.
2. Query Block이 변환과 Hint 적용의 핵심 단위인 이유를 설명한다.
3. Validity와 Cost 판단을 분리한다.
4. Subquery Unnesting·View Merging·Predicate Pushing·OR Expansion의 목적을 구분한다.
5. Join Elimination·Materialized View Rewrite·Join Factorization의 기본 목적을 설명한다.
6. 논리 변환과 실행계획 Operation을 구분한다.
7. QB_NAME과 Query Block 지정 Hint를 사용해 적용 대상을 식별한다.
8. 실제 Cursor의 Alias·Outline·Predicate·Note·Runtime Statistics로 결과를 검증한다.
9. Starts, A-Rows, Buffers를 누적값과 1회당 작업량으로 나누어 해석한다.
10. Hint 실험 전에 결과 동등성과 Cardinality 문제를 먼저 점검한다.

1. Optimizer의 세 가지 판단 역할

Oracle Optimizer의 전체 흐름은 다음 세 역할로 나누어 이해할 수 있습니다.

예를 들어 다음 SQL을 생각해 봅니다.

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.grade = 'VIP'
       );

Query Transformer는 EXISTS Subquery를 Semi Join 형태로 Unnesting할 수 있는지 검토합니다. Validity를 통과하면 CUSTOMERS가 Main Query의 Join 후보로 들어올 수 있습니다. Estimator는 VIP 고객 수와 주문 Match 수를 추정하고, Plan Generator는 NESTED LOOPS SEMI, HASH JOIN SEMI 등의 물리 후보를 비교합니다.

EXISTS Subquery
→ Unnesting Validity 검토
→ Semi Join 논리 후보 생성
→ Cardinality·Cost 추정
→ Join Order·Join Method 비교
→ 물리 Operation 선택

2. Query Block: 변환과 Hint의 적용 단위

구문 분석 단계에서 SQL은 일반적으로 Main Query, Nested Subquery, Inline View 또는 View 정의에 대응하는 Query Block들로 표현됩니다. Oracle은 Nested Subquery나 Merge되지 않은 View를 별도 Query Block으로 최적화하며, View Merging이나 Subquery Unnesting이 발생하면 기존 Block 경계가 제거되거나 재구성될 수 있습니다.

SELECT /*+ QB_NAME(main_qb) */
       o.order_id
FROM   orders o
WHERE  EXISTS (
           SELECT /*+ QB_NAME(vip_qb) */
                  1
           FROM   customers c
           WHERE  c.customer_id = o.customer_id
           AND    c.grade = 'VIP'
       );

변환 전의 개념 구조는 다음과 같습니다.

MAIN_QB
└─ VIP_QB

QB_NAME은 Query Block에 사용자가 정한 이름을 부여합니다. 이 이름은 변환을 강제하지 않습니다. 대신 다음과 같은 용도로 사용합니다.

• UNNEST(@vip_qb), NO_UNNEST(@vip_qb)처럼 Hint 적용 대상을 명확히 지정
• 같은 Table이 여러 Block에 반복될 때 Alias와 Block을 구분
• 실행계획의 Alias·Outline Data에서 변환 전후 연결 관계 추적
• Hint가 잘못된 Block이나 잘못된 Alias를 참조해 무시되는 문제 진단

Query Block 이름이 중복되거나 한 Block에 서로 다른 이름을 중복 지정하면 해당 이름과 관련 Hint가 무시될 수 있으므로 Block 이름은 고유하게 관리합니다.

3. Validity: 결과 동등성이 첫 번째 조건

Transformation은 변환 전후 결과가 의미적으로 같아야 합니다. Cost가 아무리 낮아도 결과가 달라지면 유효한 후보가 아닙니다.

검증할 항목은 다음과 같습니다.

행 수와 결과 Grain
중복 보존 여부
NULL의 3값 논리
Inner·Outer·Semi·Anti Join의 보존 규칙
Aggregate와 GROUP BY의 그룹 단위
Top-N의 정렬 범위와 동점 처리
Set Operator의 중복 제거 여부

예를 들어 다음 EXISTS는 고객별 존재 여부만 확인하므로 고객 한 행을 최대 한 번 반환합니다.

SELECT c.customer_id
FROM   customers c
WHERE  EXISTS (
           SELECT 1
           FROM   orders o
           WHERE  o.customer_id = c.customer_id
       );

이를 단순 Inner Join으로 수동 변환하면 고객의 주문 건수만큼 고객 행이 반복될 수 있습니다.

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

Optimizer가 Semi Join으로 변환하는 경우에는 존재 의미를 유지할 수 있습니다. 핵심은 “Subquery를 Join으로 바꾼다”가 아니라 원래의 존재·중복·NULL 의미를 보존하는 Join 형태로 바꿀 수 있는가입니다.

또한 제약 조건은 변환의 안전성을 증명하는 근거가 될 수 있습니다. 예를 들어 Join Elimination은 제거 대상 Table이 결과를 필터링하거나 행을 증가시키지 않는다는 사실을 신뢰할 수 있는 Key·Constraint 관계로 증명할 수 있어야 합니다.

4. Cost: 유효한 후보 중 선택

Validity를 통과한 뒤에 Cost를 비교합니다.

Validity
→ 결과 동등성을 보장하며 변환할 수 있는가?

Cost
→ 변환한 형태와 원래 형태 중 예상 자원 사용량이 어느 쪽이 낮은가?

따라서 다음 두 문장은 서로 다릅니다.

• 변환 가능: 의미적으로 안전한 후보를 만들 수 있음
• 변환 선택: Optimizer가 그 후보의 Cost가 더 유리하다고 판단함

일부 단순 View Merging처럼 Heuristic하게 적용되는 경우도 있지만, 많은 변환은 Cost-Based Framework에서 변환 전후 후보를 비교합니다. 통계가 부정확하면 Cardinality가 왜곡되고, 그 결과 유효한 변환 후보의 Cost 순위도 잘못될 수 있습니다.

5. 대표 Query Transformation 지도

이 표는 “해당 구조가 있으면 항상 변환된다”는 규칙이 아닙니다. 각 변환은 서로 다른 Validity 조건과 Cost 조건을 갖습니다.

6. Subquery Unnesting

Subquery Unnesting은 Nested Subquery를 동등한 Join 형태로 바꾸고, Subquery Table을 전체 Join 탐색에 포함시키는 Transformation입니다.

SELECT e.employee_id
FROM   employees e
WHERE  EXISTS (
           SELECT 1
           FROM   departments d
           WHERE  d.department_id = e.department_id
           AND    d.location_id = :location_id
       );

변환되지 않은 개념 구조는 다음과 같을 수 있습니다.

FILTER
  EMPLOYEES Row Source
  DEPARTMENTS Correlated Subquery

Unnesting 후에는 다음과 같은 Semi Join 후보를 만들 수 있습니다.

HASH JOIN SEMI
  EMPLOYEES
  DEPARTMENTS

이때 열리는 후보는 다음과 같습니다.

• DEPARTMENTS를 먼저 읽는 Join Order
• LOCATION_ID를 이용하는 Index Access
• 작은 입력을 Build Side로 하는 Hash Semi Join
• 선택적인 Outer를 기준으로 한 Nested Loops Semi Join

그러나 Unnesting 자체가 항상 빠르다는 뜻은 아닙니다. Outer Row가 매우 적고 Subquery Index Probe가 저렴하면 Filter 방식이 더 유리할 수도 있습니다.

UNNEST Hint는 유효한 Subquery에 대해 Heuristic·Cost 검사를 우회해 Unnesting을 시험하도록 지시할 수 있지만, 의미적으로 유효하지 않은 변환을 가능하게 만들지는 못합니다. NO_UNNEST는 해당 Unnesting을 제한하는 비교 실험에 사용합니다.

7. View Merging: 단순 Merging과 복합 Merging

View Merging은 View를 나타내는 Query Block을 이를 포함하는 바깥 Query Block과 통합합니다. View 경계가 사라지면 안쪽 Table과 바깥 Table을 한 Join Graph에서 비교할 수 있어 Join Order와 Access Path가 늘어날 수 있습니다.

SELECT e.employee_id,
       v.department_name
FROM   employees e
JOIN   (
           SELECT d.department_id,
                  d.department_name
           FROM   departments d
           WHERE  d.location_id = :location_id
       ) v
  ON   v.department_id = e.department_id;

Merging 전에는 Inline View가 별도 Block으로 최적화될 수 있습니다. Merging 후에는 EMPLOYEES와 DEPARTMENTS가 한 Block에 놓여 더 많은 Join Order를 검토할 수 있습니다.

7.1 단순 View Merging

Select-Project-Join 형태의 단순 View가 주된 대상입니다. GROUP BY, DISTINCT, Set Operator, Aggregate 같은 구조는 단순 View Merging의 대상에서 제외될 수 있습니다.

7.2 복합 View Merging

GROUP BY나 DISTINCT가 있다는 이유만으로 모든 View Merging이 절대 불가능한 것은 아닙니다. Oracle은 조건을 만족하면 복합 View Merging을 검토할 수 있으며, Join 전후에 Aggregate 또는 Distinct를 수행하는 Cost를 비교합니다.

따라서 “집계 View는 절대 Merge되지 않는다”라고 암기하면 안 됩니다. 정확한 판단은 다음과 같습니다.

단순 Merging 조건을 만족하는가?
→ 아니면 복합 Merging 후보가 될 수 있는가?
→ Validity와 Cost를 통과했는가?
→ 실제 Cursor의 Query Block·Outline에서 확인했는가?

MERGE와 NO_MERGE Hint는 Merging 비교 실험에 사용하지만, Hint가 Validity 제한을 없애지는 못합니다.

8. Predicate Pushing

Predicate Pushing은 바깥 Query Block의 조건을 Merge되지 않은 View Query Block 안으로 이동시키는 Transformation입니다. View를 Merge하지 못하더라도 안쪽에서 일찍 행을 줄이거나 Index를 사용할 수 있게 합니다.

SELECT v.employee_id,
       v.last_name
FROM   (
           SELECT employee_id, last_name, department_id
           FROM   employees
           UNION ALL
           SELECT employee_id, last_name, department_id
           FROM   contract_workers
       ) v
WHERE  v.department_id = 50;

Predicate가 각 UNION ALL Branch 안으로 Push되면 두 Table 모두 DEPARTMENT_ID = 50 조건을 Access 또는 Filter 단계에서 더 일찍 적용할 수 있습니다.

실행계획에서는 Predicate Information을 확인해 조건이 어느 Operation의 access 또는 filter Predicate로 배치됐는지 봅니다. 단, Predicate 위치가 바뀌어도 Outer Join 보존 행이나 Set 연산의 의미가 달라져서는 안 됩니다.

9. OR Expansion

OR Expansion은 Query Block의 상위 OR 조건을 여러 UNION ALL Branch로 나누는 Transformation입니다.

SELECT *
FROM   employees e
WHERE  e.email = :email
OR     e.department_id = :department_id;

개념적으로 다음과 같은 Branch를 만들 수 있습니다.

Branch 1: EMAIL 조건에 적합한 Access Path
UNION ALL
Branch 2: DEPARTMENT_ID 조건에 적합한 Access Path
          + Branch 1과 중복되는 행을 제거하는 보정 조건

장점은 하나의 복합 OR 조건 때문에 단일 Access Path만 선택해야 하는 제약을 줄이고, 각 Branch에 서로 다른 Index·Join Method를 적용할 수 있다는 점입니다. Oracle 12.2 이후의 OR Expansion 실행계획에서는 일반적으로 UNION-ALL Operation이 나타날 수 있습니다.

USE_CONCAT은 OR Expansion을 비용 판단과 별개로 시험하도록 지시하고, NO_EXPAND는 OR Expansion을 고려하지 않도록 제한합니다. 실제 결과에서는 Branch 중복 방지 Predicate와 전체 Logical I/O를 함께 확인해야 합니다.

10. Join Elimination·MV Rewrite·Join Factorization

10.1 Join Elimination

Join한 Table의 Column이 결과에 사용되지 않고, 해당 Join이 행을 필터링하거나 증가시키지 않는다는 사실을 Constraint로 증명할 수 있으면 Optimizer가 불필요한 Table을 제거할 수 있습니다.

제거 대상 Table Column은 Join Predicate 외에 사용되지 않음
+ Join이 결과 행을 누락시키지 않음
+ Join이 결과 행을 중복시키지 않음
→ Join Elimination 후보

10.2 Materialized View Rewrite

사용자 Query와 호환되는 Materialized View가 있으면 Detail Table을 다시 Join·Aggregate하지 않고 미리 계산된 결과로 Query를 Rewrite할 수 있습니다. 기본적으로 Optimizer는 호환성과 정확성 조건을 확인하고 Cost를 비교합니다.

• REWRITE: 적격 MV가 있으면 Cost와 관계없이 Rewrite를 시험하도록 지시
• NO_REWRITE: 해당 Query Block에서 Query Rewrite를 비활성화

10.3 Join Factorization

UNION ALL의 여러 Branch가 같은 큰 Table을 반복해서 Scan·Join하는 경우 공통 작업을 Branch 밖으로 꺼내 공유할 수 있습니다. 반복 작업은 줄지만 일부 Join Order 제약이 새로 생길 수 있으므로 Cost-Based 비교가 필요합니다.

11. Transformation과 물리 Operation을 구분하는 법

다음은 논리 Transformation입니다.

Subquery Unnesting
View Merging
Predicate Pushing
OR Expansion
Join Elimination
Join Factorization

다음은 실행계획의 물리 Operation입니다.

TABLE ACCESS FULL
INDEX RANGE SCAN
NESTED LOOPS SEMI
HASH JOIN ANTI
FILTER
VIEW
SORT GROUP BY
UNION-ALL

관계는 다음과 같습니다.

논리 변환 후보 생성
→ 새로운 물리 Plan 탐색 공간 개방
→ Cardinality·Cost 비교
→ 물리 Operation 조합 선택

예를 들어 UNION-ALL이 보이면 OR Expansion일 가능성이 있지만, 원래 SQL에 이미 UNION ALL이 있었거나 다른 Transformation에서 생성됐을 수도 있습니다. 따라서 Operation 한 줄만 보고 변환 이력을 단정하지 않고 Query Text·Query Block·Outline·Predicate를 함께 확인합니다.

12. 실제 Cursor에서 검증하는 방법

실제 실행 통계를 수집할 SQL에는 다음과 같이 GATHER_PLAN_STATISTICS를 사용할 수 있습니다.

SELECT /*+ GATHER_PLAN_STATISTICS
           QB_NAME(main_qb) */
       e.employee_id
FROM   employees e
WHERE  EXISTS (
           SELECT /*+ QB_NAME(dept_qb) */
                  1
           FROM   departments d
           WHERE  d.department_id = e.department_id
           AND    d.location_id = :location_id
       );

실행 후 실제 Cursor Plan을 확인합니다.

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

EXPLAIN PLAN은 실행하지 않은 환경의 예상 Plan이므로 실제 Bind, Session 환경, Adaptive 동작, Runtime Statistics가 반영된 Cursor Plan과 다를 수 있습니다. 튜닝 판단에는 실제 Cursor를 우선합니다.

13. Runtime Statistics를 1회당 작업량으로 읽기

Starts, A-Rows, Buffers는 Operation 전체의 마지막 실행 통계입니다. 반복 수행되는 Operation은 누적값만 보면 비용을 오해할 수 있으므로 1회당 값을 계산합니다.

1회당 출력 행 ≈ A-Rows ÷ Starts
1회당 Buffer 작업량 ≈ Buffers ÷ Starts

예를 들어 Subquery Row Source가 Starts = 10,000, A-Rows = 1,000, Buffers = 40,000이라면 다음과 같이 해석할 수 있습니다.

평균 출력 행 = 1,000 ÷ 10,000 = 0.1행/회
평균 Buffer = 40,000 ÷ 10,000 = 4 Buffers/회

한 번의 Probe는 작아 보여도 10,000번 반복되면 총 40,000 Buffers가 됩니다. 반대로 Transformation 후 Hash Join이 한 번 시작되더라도 Build·Probe 전체 Buffers가 더 클 수 있으므로 총량과 1회당 값을 함께 비교합니다.

Starts = 0인 비활성 Adaptive Branch는 나눗셈 대상이 아니며, 실행 통계를 수집하지 않았다면 A-Rows나 Buffers가 나타나지 않을 수 있습니다.

14. Transformation Hint 실험 원칙

Hint 실험 순서는 다음과 같습니다.

1. 원본과 비교 SQL의 결과가 같은지 검증
2. Query Block·Alias·결과 Grain 표시
3. 통계·Cardinality 오차 점검
4. 실제 Cursor의 기본 Plan과 작업량 측정
5. 한 번에 하나의 Transformation만 Hint로 비교
6. Outline·Alias·Hint Report로 적용 여부 확인
7. 여러 Bind·NULL·중복·대량 데이터에서 회귀 검증

Hint는 SQL 의미를 안전하게 바꾸는 도구가 아닙니다. UNNEST, MERGE, USE_CONCAT, REWRITE는 각각 비용·Heuristic 판단에 영향을 줄 수 있지만, 해당 Transformation의 의미적 Validity나 적격성 조건까지 없애지는 못합니다.

15. 혼동하기 쉬운 판단

실전 진단 절차

1. SQL의 Main·Subquery·View Query Block을 표시한다.
2. 결과 Grain, 중복, NULL, Outer 보존, Aggregate 단위를 적는다.
3. 후보 Transformation을 분류한다.
4. Validity와 Cost 판단을 분리한다.
5. 실제 Cursor에서 Operation·Alias·Predicate·Outline을 확인한다.
6. E-Rows와 A-Rows 차이로 Cardinality 문제를 찾는다.
7. Starts·A-Rows·Buffers의 총량과 1회당 값을 계산한다.
8. Hint는 한 번에 하나만 바꾸어 전후 결과와 작업량을 비교한다.
9. 여러 Bind와 데이터 분포에서 회귀 검증한다.

핵심 정리

Query Transformation
→ 결과 의미를 유지하는 다른 논리 형태를 생성

Query Block
→ 변환·최적화·Hint 적용 대상을 식별하는 단위

Validity
→ 행 수·중복·NULL·Join 보존·Aggregate 의미가 같은지 검증

Cost
→ 유효한 원형·변환형 후보의 예상 자원 사용량 비교

검증
→ 실제 Cursor의 Operation·Alias·Predicate·Outline·Note·Runtime Statistics 확인