스칼라 서브쿼리 반복 제거: 사전 집계·LATERAL·구조화 반환

같은 Source를 여러 번 조회
→ 한 번의 집계로 여러 값을 계산

Outer가 많고 전체 처리가 중요
→ 사전 집계 후 Join

Outer가 작고 Key별 선택적 접근이 유리
→ LATERAL·OUTER APPLY

여러 Column을 한 번에 반환
→ LATERAL·APPLY를 우선 검토
→ Object Type은 특별한 요구가 있을 때 제한적으로 사용

다중 Scalar Lookup
→ Outer Key별 선택적 반복 처리

사전 집계 Join
→ 상세 Source를 집합으로 처리

LATERAL·APPLY
→ Outer Key를 참조하면서 여러 Column·여러 Row 반환 가능

Object Type
→ 여러 Attribute를 가진 단일 Scalar 값

SELECT c.customer_id,
       c.customer_name,
       (SELECT AVG(t.amount)
        FROM   trade t
        WHERE  t.customer_id = c.customer_id
        AND    t.trade_date >= TRUNC(SYSDATE, 'MM')) AS avg_amount,
       (SELECT MIN(t.amount)
        FROM   trade t
        WHERE  t.customer_id = c.customer_id
        AND    t.trade_date >= TRUNC(SYSDATE, 'MM')) AS min_amount,
       (SELECT MAX(t.amount)
        FROM   trade t
        WHERE  t.customer_id = c.customer_id
        AND    t.trade_date >= TRUNC(SYSDATE, 'MM')) AS max_amount
FROM   customer c
WHERE  c.signup_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1);

TRADE
WHERE customer_id = 현재 고객
AND   trade_date >= 이번 달 시작일

Outer 고객 수 = 20,000
각 Scalar Subquery = 3개
Subquery 1회 평균 Buffers = 4

예상 반복 Buffers
≈ 20,000 × 3 × 4
= 240,000

확인 대상
→ 각 Row Source의 Starts
→ 동일 Object Access의 반복
→ Subquery Unnesting·View Merging
→ Buffers·A-Time

SELECT customer_id,
       AVG(amount) AS avg_amount,
       MIN(amount) AS min_amount,
       MAX(amount) AS max_amount,
       COUNT(*)    AS trade_count
FROM   trade
WHERE  trade_date >= TRUNC(SYSDATE, 'MM')
GROUP BY customer_id;

TRADE 입력 한 번 처리
→ 고객번호별 Group 생성
→ AVG·MIN·MAX·COUNT 동시 계산

SUM(CASE WHEN trade_type = 'BUY'  THEN amount END)
SUM(CASE WHEN trade_type = 'SELL' THEN amount END)
COUNT(DISTINCT product_id)

SELECT c.customer_id,
       c.customer_name,
       a.avg_amount,
       a.min_amount,
       a.max_amount,
       a.trade_count
FROM   customer c
LEFT JOIN (
    SELECT t.customer_id,
           AVG(t.amount) AS avg_amount,
           MIN(t.amount) AS min_amount,
           MAX(t.amount) AS max_amount,
           COUNT(*)      AS trade_count
    FROM   trade t
    WHERE  t.trade_date >= TRUNC(SYSDATE, 'MM')
    GROUP BY t.customer_id
) a
ON a.customer_id = c.customer_id
WHERE c.signup_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1);

한 행 = 고객 한 명의 이번 달 거래 집계

GROUP BY customer_id

GROUP BY customer_id, trade_status

AVG·MIN·MAX → NULL
COUNT(*)    → 0

NVL(a.trade_count, 0) AS trade_count

대상 고객을 먼저 선별
→ 대상 고객의 거래만 읽음
→ 고객별 집계
→ 결과 결합

WITH target_customer AS (
    SELECT customer_id,
           customer_name
    FROM   customer
    WHERE  signup_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
),
trade_agg AS (
    SELECT t.customer_id,
           AVG(t.amount) AS avg_amount,
           MIN(t.amount) AS min_amount,
           MAX(t.amount) AS max_amount,
           COUNT(*)      AS trade_count
    FROM   trade t
    JOIN   target_customer c
           ON c.customer_id = t.customer_id
    WHERE  t.trade_date >= TRUNC(SYSDATE, 'MM')
    GROUP BY t.customer_id
)
SELECT c.customer_id,
       c.customer_name,
       a.avg_amount,
       a.min_amount,
       a.max_amount,
       NVL(a.trade_count, 0) AS trade_count
FROM   target_customer c
LEFT JOIN trade_agg a
       ON a.customer_id = c.customer_id;

왼쪽 행
→ 오른쪽 Inline View에 Key 전달
→ 오른쪽에서 한 행 또는 여러 행·여러 Column 반환

SELECT d.deptno,
       x.empno,
       x.ename,
       x.sal
FROM   dept d
CROSS APPLY (
    SELECT e.empno,
           e.ename,
           e.sal
    FROM   emp e
    WHERE  e.deptno = d.deptno
) x;

SELECT d.deptno,
       x.empno,
       x.ename,
       x.sal
FROM   dept d
OUTER APPLY (
    SELECT e.empno,
           e.ename,
           e.sal
    FROM   emp e
    WHERE  e.deptno = d.deptno
) x;

SELECT d.deptno,
       x.empno,
       x.ename,
       x.sal
FROM   dept d,
       LATERAL (
           SELECT e.empno,
                  e.ename,
                  e.sal
           FROM   emp e
           WHERE  e.deptno = d.deptno
       ) x;

SELECT c.customer_id,
       x.trade_date,
       x.amount
FROM customer c
LEFT JOIN LATERAL (
    SELECT t.trade_date,
           t.amount
    FROM   trade t
    WHERE  t.customer_id = c.customer_id
    ORDER BY t.trade_date DESC, t.trade_id DESC
    FETCH FIRST 1 ROW ONLY
) x
ON 1 = 1;

SELECT c.customer_id,
       x.avg_amount,
       x.trade_count
FROM customer c
CROSS APPLY (
    SELECT AVG(t.amount) AS avg_amount,
           COUNT(*)      AS trade_count
    FROM   trade t
    WHERE  t.customer_id = c.customer_id
) x;

AVG_AMOUNT = NULL
TRADE_COUNT = 0

CROSS APPLY (
    SELECT t.trade_status,
           COUNT(*) AS cnt
    FROM trade t
    WHERE t.customer_id = c.customer_id
    GROUP BY t.trade_status
) x

SELECT d.deptno,
       (SELECT e.ename
        FROM   emp e
        WHERE  e.deptno = d.deptno
        ORDER BY e.sal DESC, e.empno
        FETCH FIRST 1 ROW ONLY) AS ename,
       (SELECT e.sal
        FROM   emp e
        WHERE  e.deptno = d.deptno
        ORDER BY e.sal DESC, e.empno
        FETCH FIRST 1 ROW ONLY) AS sal
FROM   dept d;

SELECT d.deptno,
       x.empno,
       x.ename,
       x.sal,
       x.hiredate
FROM   dept d
OUTER APPLY (
    SELECT e.empno,
           e.ename,
           e.sal,
           e.hiredate
    FROM   emp e
    WHERE  e.deptno = d.deptno
    ORDER BY e.sal DESC,
             e.empno ASC
    FETCH FIRST 1 ROW ONLY
) x;

ORDER BY e.sal DESC

ORDER BY e.sal DESC,
         e.empno ASC

Scalar Subquery 다중행
→ ORA-01427

LATERAL·APPLY 다중행
→ 왼쪽 Row 증식

CREATE INDEX emp_dept_sal_ix
ON emp(deptno, sal DESC, empno ASC);

SELECT c.customer_id,
       c.customer_name,
       x.avg_amount,
       x.min_amount,
       x.max_amount,
       x.trade_count
FROM   customer c
OUTER APPLY (
    SELECT AVG(t.amount) AS avg_amount,
           MIN(t.amount) AS min_amount,
           MAX(t.amount) AS max_amount,
           COUNT(*)      AS trade_count
    FROM   trade t
    WHERE  t.customer_id = c.customer_id
    AND    t.trade_date >= TRUNC(SYSDATE, 'MM')
) x
WHERE  c.signup_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1);

AVG·MIN·MAX → NULL
COUNT(*)    → 0

CREATE TYPE trade_stat_t AS OBJECT (
    avg_amount NUMBER,
    min_amount NUMBER,
    max_amount NUMBER
);
/

SELECT c.customer_id,
       (SELECT trade_stat_t(
                   AVG(t.amount),
                   MIN(t.amount),
                   MAX(t.amount)
               )
        FROM   trade t
        WHERE  t.customer_id = c.customer_id) AS trade_stat
FROM   customer c;

TYPE Attribute
1. avg_amount NUMBER
2. min_amount NUMBER
3. max_amount NUMBER

Constructor
trade_stat_t(avg_value, min_value, max_value)

평균 || '|' || 최소 || '|' || 최대

Outer가 작은가, 큰가?
상관 Key의 NDV는 얼마인가?
Inner Access가 선택적인가?
같은 Source를 몇 번 읽는가?
전체 집계 입력은 얼마인가?
첫 행 응답과 전체 처리 중 무엇이 중요한가?

각 Subquery Row Source의 Starts
각 Subquery의 Buffers·A-Time
동일 Table Access가 몇 번 나타나는지
상관 Key의 NDV

집계 전 입력 A-Rows
GROUP BY의 A-Rows
OMem·1Mem·Used-Mem·Used-Tmp
Join Build·Probe 입력 크기
최종 결과 행 수

오른쪽 Row Source의 Starts
Starts당 평균 A-Rows·Buffers
Top-N STOPKEY 적용 여부
오른쪽 0·1·다중행
CROSS·OUTER의 Outer 행 보존 여부

SELECT /*+ GATHER_PLAN_STATISTICS */
       ...
FROM   ...;

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

같은 Source를 반복하는가?
→ 같은 Row Set의 Aggregate를 한 번에 계산할 수 있는가?
→ 결과 한 행의 Grain은 무엇인가?
→ Outer Key 수와 NDV는 얼마인가?
→ 전체 사전 집계와 Key별 Lookup 중 작업량이 작은 것은 무엇인가?
→ 대상 Outer Key를 먼저 제한할 수 있는가?
→ 여러 Column이 필요하면 LATERAL·APPLY가 적합한가?
→ 오른쪽은 0·1·다중행 중 무엇인가?
→ CROSS·OUTER의 왼쪽 행 보존이 원래 의미와 같은가?
→ Top-1 정렬·Tie-Breaker가 결정적인가?
→ Object·문자 포장보다 Typed Relational Column이 단순한가?
→ Grain·NULL·COUNT 0·중복·행 보존이 동일한가?
→ Starts·Buffers·Workarea·TEMP·첫 행·전체 Fetch로 실측했는가?