EXISTS 로 JOIN 조건을 대체 (Filter)
결과에 컬럼이 없는 1:1 PK Join 이 단순 필터 용도로 끼어 있다면 — JOIN 을
EXISTS서브쿼리로 빼고/*+ NO_UNNEST */로 FILTER 처리를 강제해 서브쿼리 캐시 효과로 I/O 를 줄일 수 있습니다.
핵심 정리
- FILTER 로 빠지는 서브쿼리는 인풋 키의 distinct 가 적을수록 캐시 적중률이 높아져 I/O 가 감소합니다 — 외부 row 마다 호출되지만 같은 키는 캐시에서 재사용되어 자연스레 중복이 제거됩니다.
- 그래서 서브쿼리 안의 테이블이 distinct 값이 적은 코드성 테이블이라면 캐시 효과로 인한 I/O 감소가 극대화됩니다.
- JOIN 을 FILTER 서브쿼리로 바꿀 때에는 세 조건이 갖춰져야 합니다 — (1) SELECT 절에 해당 테이블 컬럼이 사용되지 않을 것, (2) 서브쿼리 내부에
/*+ NO_UNNEST */힌트 명시, (3) PK Join 등 전체 건수에 영향을 주지 않는 1:1 Join 일 것.
시나리오
ORDERS A × CUSTOMERS B (outer) × EMPLOYEES C — 4일치 주문 조회 쿼리입니다. 결과 컬럼은 ORDERS 와 CUSTOMERS 의 것만 필요한데, EMPLOYEES 와의 PK Join 은 단순 필터링(HIRE_DATE > 2007) 용도로만 끼어 있습니다.
- 1쪽:
ORDERS A(ORDER_DATE인덱스IX_ORDERS_N1) - outer:
CUSTOMERS B(PK =CUSTOMER_ID,IX_CUSTOMERS_PK) - 필터링용:
EMPLOYEES C(PK =EMPLOYEE_ID,IX_EMPLOYEES_PK) - 결과 컬럼:
A.ORDER_ID, A.ORDER_DATE, B.CUST_FIRST_NAME, A.ORDER_TOTAL—EMPLOYEES컬럼은 없음 A.EMPLOYEE_ID = C.EMPLOYEE_ID는 1:1 PK Join — 매칭되는 한 결과 건수에 영향 없음
이 두 가지(SELECT 절 미사용 + 1:1 PK Join) 가 FILTER 변환의 핵심 전제 조건입니다.
1) 원본 — JOIN 방식
세 테이블을 LEADING(A B C) 로 펼쳐 NL 로 조인합니다. EMPLOYEES C 는 결과에 컬럼을 기여하지 않지만 입사일 필터 통과 여부를 확인하기 위해 외부 5,445 row 마다 PK 인덱스 + 테이블 액세스를 한 번씩 수행합니다.
1
2
3
4
5
6
7
8
9
SELECT /*+ LEADING(A B C) USE_NL(B C) */
A.ORDER_ID, A.ORDER_DATE,
B.CUST_FIRST_NAME, A.ORDER_TOTAL
FROM ORDERS A, CUSTOMERS B, EMPLOYEES C
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID(+)
AND A.EMPLOYEE_ID = C.EMPLOYEE_ID
AND A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
AND A.ORDER_DATE < TO_DATE('20120105', 'YYYYMMDD')
AND C.HIRE_DATE > TO_DATE('20070101', 'YYYYMMDD');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3729 | 26420 |
| 1 | NESTED LOOPS | | 1 | 3729 | 26420 |
| 2 | NESTED LOOPS | | 1 | 5445 | 20975 |
| 3 | NESTED LOOPS OUTER | | 1 | 5445 | 15833 |
| 4 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 5445 | 4903 |
|* 5 | INDEX RANGE SCAN | IX_ORDERS_N1 | 1 | 5445 | 56 |
| 6 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 5445 | 5445 | 10930 |
|* 7 | INDEX UNIQUE SCAN | IX_CUSTOMERS_PK | 5445 | 5445 | 5485 |
|* 8 | INDEX UNIQUE SCAN | IX_EMPLOYEES_PK | 5445 | 5445 | 5142 |
|* 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 5445 | 3729 | 5445 |
--------------------------------------------------------------------------------------
Id 8 / Id 9 의 EMPLOYEES PK 인덱스 + 테이블 액세스가 외부 5,445 row 마다 반복되어 Buffers 합계의 40 % 에 가까운 10,587 (5,142 + 5,445) 블록 을 소모합니다. 결과에 기여도 없는 단순 필터 검증을 위해서입니다.
2) 수정 — EXISTS + NO_UNNEST 방식
EMPLOYEES C 를 EXISTS 서브쿼리로 빼고 /*+ NO_UNNEST */ 힌트로 FILTER 처리를 강제했습니다. 옵티마이저는 외부 row 마다 서브쿼리를 호출하되, 같은 EMPLOYEE_ID 키에 대해서는 결과를 캐시에서 재사용합니다.
1
2
3
4
5
6
7
8
9
10
11
SELECT /*+ LEADING(A B C) USE_NL(B C) */
A.ORDER_ID, A.ORDER_DATE,
B.CUST_FIRST_NAME, A.ORDER_TOTAL
FROM ORDERS A, CUSTOMERS B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID(+)
AND A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
AND A.ORDER_DATE < TO_DATE('20120105', 'YYYYMMDD')
AND EXISTS (SELECT /*+ NO_UNNEST */ 1
FROM EMPLOYEES C
WHERE A.EMPLOYEE_ID = C.EMPLOYEE_ID
AND C.HIRE_DATE > TO_DATE('20070101', 'YYYYMMDD'));
1
2
3
4
5
6
7
8
9
10
11
12
13
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3729 | 20980 |
| 1 | FILTER | | 1 | 3729 | 20980 |
| 2 | NESTED LOOPS OUTER | | 1 | 5445 | 15833 |
| 3 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 5445 | 4903 |
|* 4 | INDEX RANGE SCAN | IX_ORDERS_N1 | 1 | 5445 | 56 |
| 5 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 5445 | 5445 | 10930 |
|* 6 | INDEX UNIQUE SCAN | IX_CUSTOMERS_PK | 5445 | 5445 | 5485 |
|* 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1786 | 1245 | 5147 | ← 서브쿼리 캐시 효과로 Starts 가 5445 → 1786 (중복 제거)
|* 8 | INDEX UNIQUE SCAN | IX_EMPLOYEES_PK | 1786 | 1786 | 3361 | ← 서브쿼리 캐시 효과로 Starts 가 5445 → 1786 (중복 제거)
--------------------------------------------------------------------------------------
Id 7 / Id 8 의 Starts 가 5,445 → 1,786 으로 67 % 감소 했습니다. 같은 EMPLOYEE_ID 로 들어오는 외부 row 가 많아 서브쿼리 캐시가 잘 적중한 결과입니다. Buffers 도 26,420 → 20,980 으로 5,440 블록 (약 21 %) 감소 했습니다.
분석
왜 캐시 효과가 생기나
FILTER 는 외부 row 마다 서브쿼리를 호출하는 처리 방식이지만, 옵티마이저는 같은 입력 키에 대해 직전 결과를 캐시에 보관해 재사용합니다. 이번 케이스에서는 5,445 건의 주문이 1,786 명의 직원에게 분산되어 있어 — 평균 한 직원당 약 3건의 주문 — 캐시 적중이 약 67 % 수준으로 발생했습니다.
- JOIN 방식: 외부 5,445 row 모두에 대해 PK 액세스 → 캐시 없음
- FILTER 방식: 1,786 개의 distinct 키에 대해서만 PK 액세스 → 나머지는 캐시 hit
서브쿼리 안의 테이블이 코드성 테이블 (예: 부서, 직급, 상태 코드처럼 distinct 가 수십~수백 수준) 이라면 캐시 적중률이 더 극단적으로 올라갑니다 — 외부 row 가 수만 건이어도 서브쿼리는 distinct 만큼만 실제 실행됩니다.
변환 가능 여부 — 세 조건 체크
| 조건 | 이번 쿼리 | 비고 |
|---|---|---|
| ① SELECT 절에 inner 테이블 컬럼 미사용 | EMPLOYEES 컬럼 없음 | 컬럼이 한 개라도 SELECT 에 있으면 EXISTS 로 못 뺌 |
② 서브쿼리 안에 /*+ NO_UNNEST */ 명시 | 힌트 명시 | 안 쓰면 옵티마이저가 SEMI JOIN 으로 unnest 해 캐시 효과 사라짐 |
| ③ 1:1 / N:1 등 건수에 영향 없는 Join | PK Join | 1:N 이면 결과 row 가 부풀어 의미 자체가 달라짐 |
세 조건 중 하나라도 빠지면 옵티마이저 변환이 막히거나 결과 의미가 달라지므로 — 변환 전 반드시 체크해야 합니다.
Buffers 비교
| 항목 | 원본 (JOIN) | 수정 (FILTER) | 차이 |
|---|---|---|---|
| 결과 row | 3,729 | 3,729 | 동일 (정확성 보장) |
| 총 Buffers | 26,420 | 20,980 | −5,440 (약 −21 %) |
EMPLOYEES 액세스 Starts | 5,445 | 1,786 | −3,659 (약 −67 %) |
EMPLOYEES PK + Table Buffers | 10,587 | 8,508 | −2,079 |
결과 컬럼에 기여하지 않는 1:1 PK Join 은 옵티마이저에게 “단순 존재 검증” 으로 명시할 때 — 즉
EXISTS + NO_UNNEST로 표현할 때 — 캐시 효과로 비용이 줄어듭니다.
정리
세 줄로 압축하면:
- 결과에 컬럼이 안 쓰이는 1:1 PK Join 이 보이면 —
EXISTS + NO_UNNEST로 빼서 FILTER 변환 검토. - 변환 전 세 조건(SELECT 미사용 /
NO_UNNEST힌트 / 건수 영향 없음) 을 반드시 체크. - 진단 시그널은 실행계획의
Starts— JOIN 방식의 inner 테이블Starts가 outer row 수와 같다면 캐시 여지가 있다는 뜻입니다.