Star Transformation 을 이용해 Fact 테이블과 Dimension 테이블 Join
Star Transformation 은 OLAP / 데이터 웨어하우스 환경에서 대용량 Fact 테이블과 소용량 Dimension 테이블의 join 을 Bitmap Key Iteration 기반으로 풀어내는 옵티마이저 변환입니다. Fact 테이블의 dimension FK 컬럼에 Bitmap Index 가 필수이며, 본 글의 시나리오에서 Buffers 1,725 가 593 으로 약 65% 절감됩니다.
OLAP 환경의 Star Schema
데이터 웨어하우스의 가장 일반적인 모델링 패턴입니다. 중앙의 Fact 테이블 1 개 가 주변의 여러 Dimension 테이블 과 직접 연결된 구조이며, ER 다이어그램이 별 모양이라 Star Schema 라고 부릅니다.
+ FK 들
| 항목 | 설명 |
|---|---|
| Fact 테이블 | 비즈니스 이벤트의 측정값 (매출액, 판매량, 클릭수 등) + Dimension FK 들. 수억 ~ 수십억 행 규모 |
| Dimension 테이블 | Fact 를 바라보는 관점 (Who / What / When / Where). 제품, 고객, 시간, 채널, 지역 등. 수십 ~ 수백만 행 |
| Fact PK | 보통 모든 Dimension FK 의 composite key. 별도 surrogate key 를 두기도 함 |
| OLAP query 패턴 | Dimension 으로 필터 → Fact 측정값 집계 (SUM / COUNT / AVG) |
| Snowflake Schema | Dimension 을 추가 정규화 (sub-dimension). join 단계 더 많음, 저장 공간 절약 |
| Galaxy / Constellation | Fact 여러 개 + 공통 Dimension 공유 |
OLTP 와 달리 denormalize 가 의도된 설계입니다. 빠른 집계 조회가 목표이므로 정규화 비용을 감수하고 join 단계를 최소화합니다.
핵심 정리
- Star Transformation 은 Fact ↔ Dimension join 을 Bitmap Key Iteration 으로 변환합니다. Dimension 결과를 Fact 의 bitmap index 에 IN-list 처럼 적용하고, 여러 dimension 의 결과를
BITMAP AND로 교집합하여 Fact 의 rowid 를 좁힙니다. - 필수 조건: Fact 테이블의 Dimension FK 컬럼마다 Bitmap Index 가 있어야 합니다. 본 시나리오에서
SALES_CHANNEL_BIX (channel_id),SALES_PROD_BIX (prod_id)두 개가 있으므로 변환 가능. - Trade-off: Bitmap index 는 OLAP 조회에는 강력하지만 DML 성능을 잃습니다. 행 단위가 아닌 비트맵 segment 단위 lock 이라 동시 update / insert 가 폭락. OLTP 에는 부적합하고 batch 적재 후 조회만 하는 DW 환경에 적합합니다.
시나리오
SALES (대용량 fact, 약 92 만 건) ↔ CHANNELS (dim, ‘Internet’ 1 건) + PRODUCTS (dim, ‘Photo’ 카테고리 10 건) 의 전형적 OLAP 집계 쿼리.
| 테이블 | 건수 | 인덱스 |
|---|---|---|
SALES (Fact) | 약 918K (파티셔닝) | SALES_CHANNEL_BIX (channel_id) Bitmap, SALES_PROD_BIX (prod_id) Bitmap |
CHANNELS (Dim) | 1 (필터 후) | full scan |
PRODUCTS (Dim) | 10 (필터 후) | full scan |
SALES 에 두 dimension FK 의 bitmap index 가 있다는 점이 본 변환의 적용 조건입니다.
1) 원본 — 일반 Hash Join (Star Transformation 미적용)
1
2
3
4
5
6
7
8
9
SELECT p.prod_id, c.channel_id,
SUM(quantity_sold) AS qs,
SUM(amount_sold) AS amt
FROM sales s, channels c, products p
WHERE s.channel_id = c.channel_id
AND c.channel_desc = 'Internet'
AND s.prod_id = p.prod_id
AND p.prod_category_desc = 'Photo'
GROUP BY p.prod_id, c.channel_id;
옵티마이저는 두 dimension 을 cartesian join 한 뒤 SALES 풀 스캔과 hash join 합니다. SALES 92 만 건 풀 스캔이 핵심 비효율.
1
2
3
4
5
6
7
8
9
10
11
12
-----------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | Used-Mem |
-----------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 10 | 1725 | 1625K (0) |
|* 2 | HASH JOIN | | 13223 | 1725 | 907K (0) |
| 3 | MERGE JOIN CARTESIAN | | 10 | 7 | |
|* 4 | TABLE ACCESS FULL | CHANNELS | 1 | 3 | |
| 5 | BUFFER SORT | | 10 | 4 | 2048 (0) |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 10 | 4 | |
| 7 | PARTITION RANGE ALL | | 918K | 1718 | |
| 8 | TABLE ACCESS FULL | SALES | 918K | 1718 | |
-----------------------------------------------------------------------------
Id 8 의 SALES 풀 스캔이 1,718 buffer 의 99% 를 차지합니다. dimension 필터로 13,223 행만 남는데 92 만 건을 읽는 비효율.
2) 수정 — Star Transformation 활성화 (Bitmap Key Iteration)
쿼리는 동일합니다. STAR_TRANSFORMATION_ENABLED 파라미터가 TRUE 인 환경에서 옵티마이저가 자동으로 변환을 선택합니다.
1
ALTER SESSION SET STAR_TRANSFORMATION_ENABLED = TRUE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 10 | 593 | 843K (0) |
|* 2 | HASH JOIN | | 13223 | 593 | 345K (0) |
|* 3 | TABLE ACCESS FULL | CHANNELS | 1 | 3 | |
|* 4 | HASH JOIN | | 13223 | 590 | 920K (0) |
|* 5 | TABLE ACCESS FULL | PRODUCTS | 10 | 4 | |
| 6 | PARTITION RANGE ALL | | 13223 | 586 | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 13223 | 586 | | ← Id 8 의 rowid 로 Fact access
| 8 | BITMAP CONVERSION TO ROWIDS | | 13223 | 386 | | ← rowid 만들기
| 9 | BITMAP AND | | 16 | 388 | | ← 두 dim bitmap 의 교집합
| 10 | BITMAP MERGE | | 16 | 64 | |
| 11 | BITMAP KEY ITERATION | | 20 | 64 | 8192 (0) | ← CHANNELS 결과를 SALES bitmap 에 IN-list 적용
| 12 | BUFFER SORT | | 28 | 3 | |
|*13 | TABLE ACCESS FULL | CHANNELS | 1 | 3 | |
|*14 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | 20 | 61 | |
| 15 | BITMAP MERGE | | 16 | 324 | 3072 (0) |
| 16 | BITMAP KEY ITERATION | | 132 | 324 | | ← PRODUCTS 결과를 SALES bitmap 에 IN-list 적용
| 17 | BUFFER SORT | | 160 | 4 | |
|*18 | TABLE ACCESS FULL | PRODUCTS | 10 | 4 | |
|*19 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | 132 | 320 | |
--------------------------------------------------------------------------------------------------
Id 9의BITMAP AND가 본 변환의 signature 입니다. 두 dimension 결과를 각각 Fact bitmap index 에 적용한 뒤, 두 bitmap 의 교집합을 만들어 Fact 의 rowid 를 좁힙니다.Id 11/Id 16의BITMAP KEY ITERATION은 dimension 결과를 IN-list 로 펼쳐 Fact bitmap index 를 RANGE SCAN 하는 단계입니다.Id 7의Starts = 13,223(A-Rows) 으로, Fact 테이블 access 가 92 만 건이 아닌 13,223 건만 발생합니다.Buffers 1,725 → 593(약 65% 절감).
분석
Star Transformation 의 변환 흐름
SELECT ... FROM sales s,
channels c,
products p
WHERE s.channel_id = c.channel_id
AND s.prod_id = p.prod_id
AND c.channel_desc = 'Internet'
AND p.prod_category_desc = 'Photo'
GROUP BY ...SELECT ... FROM sales s
WHERE channel_id IN
(SELECT channel_id FROM channels
WHERE channel_desc = 'Internet')
AND prod_id IN
(SELECT prod_id FROM products
WHERE prod_category_desc = 'Photo')
... 그 다음 Fact rowid 로 다시 Dim join옵티마이저는 dimension join 을 subquery 형태로 풀어 Fact 의 bitmap index 에 IN-list 로 적용합니다. 두 (또는 그 이상) dimension 의 bitmap 결과를 BITMAP AND 로 교집합한 뒤, Fact rowid 로 변환하여 좁혀진 행 집합만 access. 이후 dimension 컬럼이 필요한 경우 다시 dimension 과 hash join (Id 2, Id 4) 합니다.
적용 조건
STAR_TRANSFORMATION_ENABLED파라미터 =TRUE(기본FALSE).- Fact 테이블의 모든 dimension FK 컬럼에 Bitmap Index.
- 옵티마이저가 cost 비교 후 변환이 유리하다고 판단해야 합니다 (Dimension 필터 결과가 Fact 의 작은 비율일 때 유리).
1
2
3
4
5
6
7
8
9
-- 환경 점검
SHOW PARAMETER star_transformation_enabled;
-- 세션에서만 켜기
ALTER SESSION SET STAR_TRANSFORMATION_ENABLED = TRUE;
-- Bitmap Index 생성
CREATE BITMAP INDEX sales_channel_bix ON sales(channel_id);
CREATE BITMAP INDEX sales_prod_bix ON sales(prod_id);
Bitmap Index 의 DML Trade-off
Bitmap index 는 컬럼 값별로 비트맵을 유지합니다. 한 비트맵 segment 가 수백 ~ 수천 행을 커버하므로, 한 행을 update 해도 그 비트맵 segment 전체에 lock 이 걸립니다. OLTP 의 동시 update 환경에서는 락 경합이 폭증하여 사실상 사용 불가합니다.
따라서 Bitmap index + Star Transformation 은 다음 환경에서만 의미가 있습니다.
| 환경 | 적합성 |
|---|---|
| OLTP (실시간 거래) | DML 성능 폭락 |
| OLAP / DW (조회 위주) | 압도적 성능 |
| Batch 적재 후 조회 | 적재 동안 index disable 후 rebuild |
Star Transformation 은 OLAP / DW 의 핵심 최적화 패턴입니다. Fact 의 dimension FK 에 bitmap index 를 만들어 두면 옵티마이저가 자동으로
BITMAP AND+BITMAP KEY ITERATION으로 풀어내어 Fact 풀 스캔을 피합니다. 단 OLTP 환경이라면 절대 사용하지 말아야 합니다.
Buffers 비교
| 단계 | 원본 | 수정 |
|---|---|---|
| Dimension access (CHANNELS + PRODUCTS) | 7 | 7 |
| SALES (Fact) access | 1,718 (풀 스캔) | 586 (rowid lookup) + 384 (bitmap index) ≈ 970 |
| 합계 | 1,725 | 593 |
약 65% 절감. 시나리오에 따라 dimension 필터 후 Fact 행이 매우 작은 비율이면 90% 이상 절감도 가능합니다.
정리
- Star Transformation 은 OLAP / DW 환경의 Fact ↔ Dimension join 을 Bitmap Key Iteration + BITMAP AND 로 풀어내는 옵티마이저 변환입니다.
본 시나리오에서 Buffers 가 1,725 에서 593 으로 약 65% 절감되었습니다. - 적용 조건:
STAR_TRANSFORMATION_ENABLED = TRUE+ Fact 의 모든 dimension FK 에 Bitmap Index. 옵티마이저가 cost 기반으로 자동 선택합니다. - DML Trade-off: Bitmap index 는 OLTP 의 동시 update 에 치명적입니다. 조회 위주의 DW / OLAP 환경에서만 사용해야 합니다.