스칼라 서브쿼리 기본 원리: 0행·1행·다중행과 반복 실행

상관 Row별 Lookup
서브쿼리 결과 재사용
Subquery Unnesting
Outer Join·Aggregate Join 변환
다른 동등한 Row Source 구조

SELECT e.empno,
       e.ename,
       (SELECT d.dname
        FROM   dept d
        WHERE  d.deptno = e.deptno) AS dname
FROM   emp e;

사원 한 행
→ 사원의 DEPTNO를 이용해 부서 조회
→ 부서명 한 값을 바깥 행에 붙임

SELECT order_id
FROM   orders
WHERE  order_amount >
       (SELECT AVG(order_amount)
        FROM   orders);

UPDATE employee e
SET    grade_name =
       (SELECT g.grade_name
        FROM   salary_grade g
        WHERE  e.salary BETWEEN g.low_salary AND g.high_salary);

-- 스칼라 서브쿼리 표현식
SELECT (SELECT MAX(salary) FROM emp)
FROM   dual;

-- 단일행 비교 연산자와 서브쿼리
SELECT employee_id
FROM   emp
WHERE  salary = (SELECT MAX(salary) FROM emp);

= (subquery)
→ 최대 한 행

IN (subquery)
→ 여러 행 가능

EXISTS (subquery)
→ 행 존재 여부

SELECT 목록
→ 하나의 값만 반환

실행 결과
→ 최대 한 행만 반환

SELECT e.empno,
       (SELECT d.dname, d.loc
        FROM   dept d
        WHERE  d.deptno = e.deptno)
FROM   emp e;

SELECT c.customer_id,
       (SELECT o.order_date
        FROM   orders o
        WHERE  o.customer_id = c.customer_id) AS order_date
FROM   customer c;

DEPT
DEPTNO  DNAME
10      ACCOUNTING
20      RESEARCH
20      DEVELOPMENT

SELECT (SELECT dname
        FROM   dept
        WHERE  deptno = 30) AS dname
FROM   dual;

DNAME
-----
NULL

SELECT (SELECT dname
        FROM   dept
        WHERE  deptno = 10) AS dname
FROM   dual;

SELECT (SELECT dname
        FROM   dept
        WHERE  deptno = 20) AS dname
FROM   dual;

SELECT (
    SELECT order_date
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    AND    ROWNUM = 1
)
FROM customer c;

SELECT (
    SELECT order_date
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    ORDER BY order_date DESC, order_id DESC
    FETCH FIRST 1 ROW ONLY
)
FROM customer c;

ORA-01427 제거
≠
업무상 올바른 대표 행 선택

SELECT c.customer_id,
       (SELECT SUM(o.order_amount)
        FROM   orders o
        WHERE  o.customer_id = c.customer_id) AS total_amount
FROM   customer c;

SELECT (SELECT SUM(amount)
        FROM   orders
        WHERE  customer_id = -1) AS sum_amount,
       (SELECT COUNT(*)
        FROM   orders
        WHERE  customer_id = -1) AS order_count
FROM   dual;

SUM_AMOUNT  ORDER_COUNT
----------  -----------
NULL        0

SELECT c.customer_id,
       (SELECT SUM(o.order_amount)
        FROM   orders o
        WHERE  o.customer_id = c.customer_id
        GROUP BY o.order_status) AS total_amount
FROM   customer c;

GROUP BY 없음
→ Aggregate 결과 Row 1건
→ SUM 등은 NULL, COUNT는 0

GROUP BY 있음
→ 생성할 Group이 없음
→ 서브쿼리 결과 0행
→ 스칼라 표현식 값 NULL

SELECT e.empno,
       e.sal,
       (SELECT AVG(sal) FROM emp) AS company_avg_sal
FROM   emp e;

SELECT e.empno,
       e.sal,
       (SELECT AVG(x.sal)
        FROM   emp x
        WHERE  x.deptno = e.deptno) AS dept_avg_sal
FROM   emp e;

바깥 EMP 행 읽기
→ 현재 DEPTNO 전달
→ 해당 부서 평균 계산
→ 결과 한 값을 바깥 행에 결합

바깥 행마다 반복 실행
반복된 상관 Key 결과를 실행 중 재사용
서브쿼리를 Join·집계 형태로 변환

SELECT e.employee_id,
       (SELECT AVG(salary) FROM emp) AS company_avg
FROM   emp e;

비상관
→ 바깥 Row 값과 독립
→ 같은 결과값

상관
→ 바깥 Row Key에 의존
→ Key별 결과 가능

전체 비용
≈ 바깥 Row Source 생성 비용
 + 바깥 행 수 × 서브쿼리 1회 처리 비용

Outer A-Rows = 100,000
Subquery Starts = 100,000
Inner Buffers ≈ 300,000

Cache가 있다고 가정해 SQL을 설계
→ 위험

실제 Starts·Buffers가 감소했는지 확인
→ 올바른 진단

SELECT STATEMENT
  TABLE ACCESS FULL EMP
  SCALAR SUBQUERY
    TABLE ACCESS BY INDEX ROWID DEPT
      INDEX UNIQUE SCAN DEPT_PK

SELECT c.customer_id,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS last_order_date,
       (SELECT SUM(o.order_amount)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS total_amount,
       (SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS order_count
FROM customer c;

고객별 사전 집계 한 번
→ MAX·SUM·COUNT 동시 계산
→ Customer와 LEFT JOIN

Outer 첫 행
→ Index Lookup
→ 첫 결과 반환

상세 Table 전체 처리
→ GROUP BY
→ Join
→ 결과 반환

SELECT e.empno,
       (SELECT d.dname
        FROM   dept d
        WHERE  d.deptno = e.deptno) AS dname
FROM   emp e;

SELECT e.empno,
       d.dname
FROM   emp e
LEFT JOIN dept d
       ON d.deptno = e.deptno;

SELECT e.empno,
       (SELECT d.dname
        FROM   dept d
        WHERE  d.deptno = e.deptno
        AND    d.active_yn = 'Y') AS dname
FROM emp e;

SELECT e.empno,
       d.dname
FROM emp e
LEFT JOIN dept d
  ON d.deptno = e.deptno
 AND d.active_yn = 'Y';

Scalar Subquery
→ 2행 이상이면 ORA-01427

LEFT JOIN
→ Match 수만큼 바깥 행이 증가

SELECT c.customer_id,
       (SELECT SUM(o.order_amount)
        FROM   orders o
        WHERE  o.customer_id = c.customer_id) AS total_amount
FROM   customer c;

SELECT c.customer_id,
       a.total_amount
FROM   customer c
LEFT JOIN (
    SELECT customer_id,
           SUM(order_amount) AS total_amount
    FROM   orders
    GROUP BY customer_id
) a
ON a.customer_id = c.customer_id;

한 행 = 고객 한 명의 집계 결과

NVL(a.total_amount, 0)

-- 원래 의미가 COUNT 0이면
NVL(a.order_count, 0)

SELECT /*+ GATHER_PLAN_STATISTICS */
       e.empno,
       (SELECT d.dname
        FROM   dept d
        WHERE  d.deptno = e.deptno) AS dname
FROM   emp e;

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

1. 바깥 Row Source의 A-Rows를 확인한다.
2. Scalar Subquery 또는 변환된 Join의 실제 구조를 확인한다.
3. Subquery Row Source의 Starts를 확인한다.
4. Starts당 A-Rows·Buffers를 계산한다.
5. 상관 Key의 NDV와 편중을 확인한다.
6. 전체 Fetch와 일부 Fetch를 구분한다.
7. Join·사전 집계 대안과 결과 행 수·NULL을 비교한다.
8. 여러 Scalar Subquery가 같은 Table을 반복 조회하는지 확인한다.
9. 첫 행 시간과 전체 Fetch 시간, Buffer·Memory·TEMP를 함께 비교한다.

결과가 한 값이어야 하는가?
→ 단일행 비교인지 IN·EXISTS 같은 다중행 연산인지 확인
→ 0행·1행·다중행 규칙은 무엇인가?
→ 대표 한 행이면 결정적 ORDER BY가 있는가?
→ 상관 Key와 바깥 A-Rows·NDV는 얼마인가?
→ 실제 Starts·A-Rows·Buffers는 얼마인가?
→ 같은 Table의 Scalar Lookup이 여러 번 반복되는가?
→ 첫 행 응답과 전체 처리량 중 어느 목표인가?
→ Join·사전 집계로 바꿔도 NULL·0·중복·Grain·조건 위치가 같은가?