SEMI JOIN 시 Driving 테이블로 변경 (Driving Semi Join)
일반적으로 Semi Join 의 driving 집합은 메인 쿼리 쪽으로 고정되지만,
SEMIJOIN_DRIVER힌트를 쓰면 서브쿼리 쪽을 driving 으로 둘 수 있습니다. 이 변환의 plan 은 Bitmap Key Iteration 으로 풀리는데, 이는 join 을IN-list형태로 변환하여 서브쿼리 결과를 인덱스 access 의 키로 사용하는 방식입니다.
핵심 정리
SEMIJOIN_DRIVER(@subquery_block)힌트로 서브쿼리를 driving 집합으로 강제할 수 있습니다. 일반 Semi Join 에서는 옵티마이저가 메인 쪽을 driving 으로 잡지만, 서브쿼리 결과가 매우 작을 때는 driver 를 뒤집는 것이 유리합니다.- Driving Semi Join 은 Hash Join 과 NL Join 의 장점을 결합합니다. 서브쿼리 결과를 모은 뒤 (Hash 처럼) 메인 인덱스를 inline-list 로 lookup (NL 처럼) 합니다.
- 수행 방식은 Bitmap Key Iteration. 서브쿼리 결과를 bitmap 으로 만들어 메인 인덱스의 RANGE SCAN 키로 iterate 합니다. 결국 join 이
WHERE column IN (...)형태로 풀려나옵니다.
시나리오
sales_t (대형 fact 테이블) 와 products_t (소형 마스터) 를 prod_id 로 매칭하는 Semi Join 입니다. 카테고리가 ‘Software/Other’ 인 상품 26 개에 해당하는 매출만 집계.
| 테이블 | 건수 | 인덱스 |
|---|---|---|
sales_t | 약 92 만 (전체) → 405K (필터 후) | SALES_T_PROD_IX (prod_id) B-tree |
products_t | 26 (필터 후) | full scan |
옵티마이저가 일반 Hash Semi Join 을 선택하면 SALES_T 를 풀 스캔합니다. 26 건짜리 작은 서브쿼리 결과를 driving 으로 두면 인덱스 lookup 만으로 충분한데도 풀 스캔이 발생하는 비효율이 있습니다.
1) 원본 — 일반 Hash Semi Join
1
2
3
4
5
6
7
8
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
s.prod_id, s.channel_id, SUM(quantity_sold) AS qs, SUM(amount_sold) AS amt
FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
p.prod_id
FROM products_t p
WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id, s.channel_id;
옵티마이저는 PRODUCTS_T 26 건을 build 로 hash table 을 만들고, SALES_T 918K 건을 풀 스캔하면서 probe 합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
-----------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 82 | 00:00:01.52 | 4444 |
|* 2 | HASH JOIN | | 405K | 00:00:00.87 | 4444 |
|* 3 | TABLE ACCESS FULL | PRODUCTS_T | 26 | 00:00:00.01 | 4 |
| 4 | TABLE ACCESS FULL | SALES_T | 918K | 00:00:02.81 | 4440 |
-----------------------------------------------------------------------------
Predicate Information:
----------------------
2 - access("S"."PROD_ID"="P"."PROD_ID")
3 - filter("P"."PROD_CATEGORY_DESC"='Software/Other')
Id 4 의 SALES_T 풀 스캔이 4,440 buffer 의 99% 를 차지합니다. SALES_T_PROD_IX 인덱스가 있는데도 활용 안 됩니다. 26 개의 prod_id 만 매칭하면 되는데 전체 테이블을 스캔하는 비효율이 발생합니다.
2) 수정 — SEMIJOIN_DRIVER 로 Bitmap Key Iteration 유도
1
2
3
4
5
6
7
8
SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
s.prod_id, s.channel_id, SUM(quantity_sold) AS qs, SUM(amount_sold) AS amt
FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
p.prod_id
FROM products_t p
WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id, s.channel_id;
SEMIJOIN_DRIVER(@SUB) 힌트가 추가되면서 서브쿼리 (SUB block) 가 driving 집합이 됩니다. 옵티마이저는 이를 Bitmap Key Iteration 으로 풀어냅니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 82 | 3864 |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES_T | 1 | 405K | 3864 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 1 | 405K | 877 | ← B-tree → Bitmap 변환
| 4 | BITMAP MERGE | | 1 | 405K | 877 |
| 5 | BITMAP KEY ITERATION | | 1 | 5 | 877 | ← IN-list 형태로 iterate
|* 6 | TABLE ACCESS FULL | PRODUCTS_T | 1 | 26 | 4 |
| 7 | BITMAP CONVERSION FROM ROWIDS | | 26 | 26 | 873 | ← Bitmap → B-tree 변환
|* 8 | INDEX RANGE SCAN | SALES_T_PROD_IX | 26 | 405K | 873 |
------------------------------------------------------------------------------------------
Predicate Information:
----------------------
6 - filter("P"."PROD_CATEGORY_DESC"='Software/Other')
8 - access("S"."PROD_ID"="P"."PROD_ID")
핵심 신호:
Id 5의BITMAP KEY ITERATION이 서브쿼리 결과 26 개를 키로 iterate 한다는 signature 입니다.WHERE prod_id IN (1, 2, 3, ..., 26)처럼 풀어냅니다.Id 8의Starts = 26으로SALES_T_PROD_IX인덱스를 26 회 RANGE SCAN 합니다.Id 3/Id 7의BITMAP CONVERSION두 단계는 B-tree 인덱스를 일시적으로 bitmap 으로 바꿔 merge 한 뒤 다시 rowid 로 변환하는 단계입니다.Buffers 4,444 → 3,864(약 13% 절감). 더 큰 의미는SALES_T풀 스캔이 사라지고 인덱스 lookup 으로 풀린 점.
분석
Driving Semi Join 의 본질
일반 Semi Join 은 메인 쪽이 driving 이라 메인 테이블을 풀 스캔하면서 서브쿼리 결과로 매칭을 거릅니다. Driving Semi Join 은 이 방향을 뒤집어 작은 서브쿼리 결과를 driving 으로 두고 메인 인덱스를 lookup 합니다.
비유하면:
- Hash Semi Join: build = 작은 집합 (서브쿼리), probe = 큰 집합 (메인 풀 스캔)
- NL Semi Join: outer = 메인 (각 row 마다 서브쿼리 lookup)
- Driving Semi Join: outer = 서브쿼리 결과 (한 번에 모은 뒤), inner = 메인 인덱스 (IN-list lookup)
Driving Semi Join 은 서브쿼리 결과가 매우 작고 메인에 인덱스가 있을 때 압도적으로 유리합니다.
Bitmap Key Iteration 메커니즘
원리는 IN-list iterator 와 동일합니다.
1
WHERE prod_id IN (101, 205, 308, ..., 999) -- 26 개
이 형태의 쿼리는 옵티마이저가 인덱스를 26 번 RANGE SCAN 하여 결과를 union 합니다. Driving Semi Join 의 BITMAP KEY ITERATION 도 동일한 방식으로, 서브쿼리 결과 26 개를 IN-list 처럼 펼쳐서 인덱스를 26 번 두드립니다. 차이점은 옵티마이저가 결과를 bitmap 으로 모았다가 한 번에 rowid 로 변환하는 후처리가 추가된다는 점입니다.
적용 조건
Driving Semi Join 이 동작하려면 메인 테이블의 join 컬럼에 bitmap index 가 있어야 합니다. B-tree 인덱스만 있다면 _b_tree_bitmap_plans 히든 파라미터가 켜져 있어야 옵티마이저가 B-tree 를 일시적으로 bitmap 으로 변환합니다 (BITMAP CONVERSION TO ROWIDS / FROM ROWIDS 단계).
1
2
3
4
5
-- 11g 이상에서 hidden parameter 확인
SELECT * FROM v$parameter WHERE name = '_b_tree_bitmap_plans';
-- 세션에서만 켜기
ALTER SESSION SET "_b_tree_bitmap_plans" = TRUE;
대부분의 OLTP 환경에서는 이 파라미터가 기본 OFF 입니다. 본 케이스 plan 에 BITMAP CONVERSION 이 등장한다는 것은 이 파라미터가 켜져 있다는 신호.
Driving Semi Join 은 서브쿼리 결과 카디널리티가 작고 메인 join 컬럼에 인덱스가 있을 때 위력을 발휘합니다.
IN-list로 변환되는 본질을 이해하면 hint 결정이 쉬워집니다. 단BITMAP CONVERSION자체에도 비용이 있으므로, 서브쿼리 결과가 충분히 작은지 (수십 건 이하) 검증이 필요합니다.
Buffers 비교
| 단계 | 원본 | 수정 |
|---|---|---|
| PRODUCTS_T access | 4 | 4 |
| SALES_T access | 4,440 (풀 스캔) | 3,860 (인덱스 + ROWID lookup) |
| 합계 | 4,444 | 3,864 |
본 케이스의 절감은 약 13% 이지만, 시나리오에 따라 차이는 더 커질 수 있습니다. 메인 테이블이 매우 크고 서브쿼리 결과가 매우 작을수록 효과가 극대화됩니다.
정리
SEMIJOIN_DRIVER(@subquery_block)힌트로 서브쿼리를 driving 으로 강제할 수 있습니다. 일반 Hash / NL Semi Join 으로 풀리지 않을 때 시도할 옵션.- Driving Semi Join 은 Bitmap Key Iteration 으로 수행되며, 본질적으로
WHERE column IN (...)IN-list 와 같은 access 패턴입니다. - 적용 조건: 메인 join 컬럼에 bitmap index 또는 B-tree +
_b_tree_bitmap_plans파라미터. 서브쿼리 결과가 작고 메인이 클 때 가장 효과가 큽니다.