Scalar Subquery

게시 2026/04/30

By 반지현

읽는 시간 9분

Scalar Subquery

Scalar Subquery 는 같은 입력 값에 대한 결과를 PGA 에 캐시합니다. 후행 테이블의 distinct 입력 값 종류가 적을수록 캐시 hit 가 늘어 후행 access 횟수가 줄어듭니다. 본 글은 NL OUTER JOIN 의 1,370 회 access 가 Scalar Subquery 변환으로 736 회로 줄어드는 사례입니다.

핵심 정리

Scalar Subquery 도 캐싱 효과를 가집니다. 일반 서브쿼리 캐싱과 같은 메커니즘으로, 한 쿼리 실행 단위 안에서 같은 입력 값에 대한 결과를 PGA 의 hash table 에 보관합니다.
입력 값의 distinct 종류가 적을수록 캐싱 효과가 좋습니다. 본 글 시나리오에서 1,370 건의 ORDERS 가 736 명의 직원과 매칭되어 distinct EMPLOYEE_ID 가 736 개. 약 46% 의 access 가 절감됩니다.
건수 적은 코드성 테이블이 이상적인 변환 대상. 직원 / 부서 / 상태 코드 / 통화 같은 마스터 테이블은 NL JOIN 보다 Scalar Subquery 가 압도적으로 유리합니다.

시나리오

ORDERS (1 일치 1,370 건) 를 조회하면서 EMPLOYEES 에서 사원명을 가져오는 일반적인 패턴입니다.

인덱스는 다음과 같습니다.

테이블	인덱스	컬럼
`ORDERS`	`IX_ORDERS_N1`	`ORDER_DATE`
`EMPLOYEES`	`IX_EMPLOYEES_PK`	`EMPLOYEE_ID` (PK)

ORDERS 1,370 건이 모두 다른 직원에게 속하지는 않습니다. 같은 직원이 여러 주문을 처리한 경우가 많아 distinct EMPLOYEE_ID 는 1,370 보다 작습니다. 본 케이스에서는 736 명. 이 distinct 카디널리티 차이가 캐싱 효과의 본질입니다.

1) 원본 — NL OUTER JOIN (1,370 회 후행 access)

SELECT /*+ LEADING(A B) USE_NL(B) INDEX(A IX_ORDERS_N1) */
       A.ORDER_ID, A.ORDER_DATE, B.LAST_NAME, A.ORDER_TOTAL
  FROM ORDERS A, EMPLOYEES B
 WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID(+)
   AND A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND A.ORDER_DATE <  TO_DATE('20120102', 'YYYYMMDD');

옵티마이저는 LEADING(A B) USE_NL(B) 힌트를 따라 ORDERS 의 1,370 건마다 EMPLOYEES PK 인덱스를 한 번씩 두드립니다. 같은 직원이 여러 번 등장해도 매번 access 합니다.

-------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                 |      1 |   1370 |    3905 |
|  1 |  NESTED LOOPS OUTER            |                 |      1 |   1370 |    3905 |
|  2 |   TABLE ACCESS BY INDEX ROWID  | ORDERS          |      1 |   1370 |    1252 |
|* 3 |    INDEX RANGE SCAN            | IX_ORDERS_N1    |      1 |   1370 |      22 |
|  4 |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEES       |   1370 |   1370 |    2653 |
|* 5 |    INDEX UNIQUE SCAN           | IX_EMPLOYEES_PK |   1370 |   1370 |    1283 |
-------------------------------------------------------------------------------------

Id 5 의 Starts = 1370 이 본질적인 비효율입니다. 후행 EMPLOYEES PK 인덱스를 1,370 번 access 하면서 Buffers 2,653 을 소비합니다.

2) 수정 — Scalar Subquery 변환 (736 회 access)

SELECT /*+ INDEX(A IX_ORDERS_N1) */
       A.ORDER_ID, A.ORDER_DATE,
       (SELECT B.LAST_NAME FROM EMPLOYEES B
         WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID) EMPLOYEE_NAME,
       A.ORDER_TOTAL
  FROM ORDERS A
 WHERE A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND A.ORDER_DATE <  TO_DATE('20120102', 'YYYYMMDD');

후행 EMPLOYEES 를 Scalar Subquery 로 옮깁니다. Oracle 은 같은 입력 값 (EMPLOYEE_ID) 으로 호출되는 Scalar Subquery 의 결과를 PGA 에 캐시하여, 두 번째 호출부터는 인덱스 access 없이 캐시에서 바로 반환합니다.

-------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                 |      1 |   1370 |    2699 |
|  1 |  TABLE ACCESS BY INDEX ROWID   | EMPLOYEES       |    736 |    736 |    1447 |  ← 1,370 → 736 회로 감소 (캐싱 효과)
|* 2 |   INDEX UNIQUE SCAN            | IX_EMPLOYEES_PK |    736 |    736 |     711 |
|  3 |  TABLE ACCESS BY INDEX ROWID   | ORDERS          |      1 |   1370 |    1252 |  ← 메인 테이블은 동일
|* 4 |   INDEX RANGE SCAN             | IX_ORDERS_N1    |      1 |   1370 |      22 |
-------------------------------------------------------------------------------------

핵심 신호:

Id 1 의 Starts = 736. 1,370 회가 아닌 distinct EMPLOYEE_ID 만큼 만 access 합니다.
Buffers 2,653 → 1,447 으로 EMPLOYEES access 가 약 45% 절감됩니다.
ORDERS access 는 1,252 그대로이며 메인 테이블에는 변화가 없습니다.

분석

캐싱 메커니즘

Oracle 의 Scalar Subquery 는 실행 시 PGA 안에 작은 hash table 을 만들어 (input value, output) 매핑으로 같은 입력에 대한 결과를 재사용합니다. 캐시는 한 쿼리 실행 단위 안에서만 유효하며, 쿼리가 끝나면 사라집니다.

캐시 크기는 옵티마이저가 자동 결정 (보통 256 buckets 정도) 하며, distinct 입력이 많아져 캐시 capacity 를 초과하면 LRU eviction 이 일어나 일부 재계산이 발생합니다.

Distinct 카디널리티와 캐싱 효과

후행 테이블 access 횟수는 다음 식에 가깝습니다.

access_count ≈ min(distinct_input_count, total_input_count)

본 케이스: min(736, 1370) = 736 으로 캐싱 효과가 큽니다.
모든 ORDERS 가 다른 직원이라면 min(1370, 1370) = 1370 으로 캐싱 효과가 없습니다.
입력 값이 100 명 정도라면 min(100, 1370) = 100 으로 캐싱 효과가 매우 큽니다.

따라서 선행 테이블의 row 수가 후행 테이블의 distinct 매칭 값 종류보다 훨씬 많을수록 캐싱 효과가 극대화됩니다. 코드성 테이블 / 마스터 테이블이 Scalar Subquery 의 이상적 대상인 이유입니다.

언제 NL JOIN 을 쓰고 언제 Scalar Subquery 를 쓰는가

상황	권장
후행 테이블이 크고 distinct 매칭이 1:1 또는 1:매우 드문 hit	NL JOIN
후행 테이블이 작거나 (코드성 / 마스터) distinct 입력이 적음	Scalar Subquery (캐싱 활용)
결과 row 수가 변할 수 있는 1:M	NL JOIN 또는 집계 Scalar Subquery

Scalar Subquery 의 캐싱 효과를 극대화하려면 코드성 테이블 / 마스터 테이블 을 후행으로 둬야 합니다. 또한 직원, 부서, 상태 코드, 통화 같은 distinct 카디널리티가 작은 테이블이 가장 큰 이득을 봅니다.

Buffers 비교

단계	원본	수정	차이
ORDERS access	1,252	1,252	동일
EMPLOYEES access	2,653	1,447	약 45% 절감 (1,370 회 → 736 회)
합계	3,905	2,699	약 31% 절감

정리

Scalar Subquery 는 PGA 내장 캐시로 같은 입력 값에 대한 결과를 재사용합니다. 후행 access 횟수가 distinct 입력 값 종류만큼으로 줄어듭니다.
변환 효과는 distinct 카디널리티에 정비례합니다. 코드성 / 마스터 테이블처럼 distinct 종류가 적을수록 효과가 큽니다.
변환 적용 조건: 1:1 또는 1:M (집계 적용 필수) 관계, 후행에서 가져올 컬럼이 단일 (Scalar 결과 컬럼이라), LEFT OUTER JOIN 의미가 자연스럽게 보존되어야 합니다.

SQL Tuning, 케이스 스터디