587. 스타 변환 (Star Transformation) - 팩트와 차원의 조인 최적화

⚠️ 이 문서는 데이터 웨어하우스(DW)의 중심에 있는 거대한 100억 건짜리 '팩트 테이블'과 그 주변을 감싸고 있는 여러 개의 작은 '차원 테이블'들을 엮어서 통계를 낼 때, **오라클(Oracle) 같은 DB가 멍청하게 100억 건의 테이블을 무식하게 다 뒤져서 조인(JOIN)하는 대참사를 막기 위해, 차원 테이블의 조건들로 팩트 테이블의 인덱스(Bitmap)를 먼저 싹 다 교집합(AND) 처리하여 족집게처럼 딱 필요한 소수의 데이터만 건져 올리는 극한의 옵티마이저 마법인 '스타 변환'**을 다룹니다.

핵심 인사이트 (3줄 요약)

  1. 본질: 스타 스키마(Star Schema)로 짜인 DW 환경 전용 쿼리 변환(Query Transformation) 기술이다. 메인 팩트 테이블을 읽기 전에, 주변 차원 테이블들의 조건을 서브쿼리(IN)로 변환하여 팩트 테이블의 비트맵 인덱스를 먼저 공략하도록 쿼리의 구조를 내부적으로 뜯어고친다.
  2. 가치: 100억 건의 데이터 중 "서울에 살고, 20대이며, 전자제품을 산 고객"이라는 교집합 데이터를 찾을 때, 하드디스크 풀 스캔(Full Scan) 없이 비트맵 인덱스(0과 1)들의 AND 비트 연산만으로 메모리 위에서 광속으로 타겟을 찾아내어 쿼리 속도를 1,000배 이상 끌어올린다.
  3. 기술 체계: 팩트 테이블에 반드시 차원 테이블과 연결되는 컬럼마다 **비트맵 인덱스(Bitmap Index)**가 깔려 있어야 하며, 옵티마이저가 쿼리를 WHERE 팩트.고객ID IN (SELECT 고객ID FROM 고객 WHERE 지역='서울') 형태로 서브쿼리 변환을 일으키는 것이 핵심이다.

Ⅰ. 스타 스키마 쿼리의 악몽: 무식한 해시 조인(Hash Join)

"강남에 사는 20대 여성이 산 노트북 매출액을 구해라!" 100억 건을 다 읽을 텐가?

  1. 일반적인 스타 조인(Star Join)의 한계:
    • 팩트(매출) 테이블은 100억 건, 고객(차원) 테이블 100만 건, 상품(차원) 테이블 10만 건이 있다.
    • SELECT SUM(매출.금액) FROM 매출, 고객, 상품 WHERE 매출.고객ID = 고객.ID AND 매출.상품ID = 상품.ID AND 고객.지역 = '강남' AND 상품.카테고리 = '노트북'
    • 멍청한 DB 옵티마이저는 강남 고객(10만 명)과 노트북 상품(1,000개)을 먼저 찾는다.
    • 그리고 이 조건으로 100억 건짜리 거대한 매출 테이블 전체를 스캔하며 무거운 **해시 조인(Hash Join)**을 때린다. CPU가 터지고 쿼리는 30분이 걸린다.
  2. 비트맵 인덱스(Bitmap Index)의 무용지물화:
    • DBA가 똑똑하게 매출 테이블의 고객ID, 상품ID 컬럼에 비트맵 인덱스를 다 만들어 두었다 치자.
    • 하지만 위의 쿼리처럼 여러 테이블이 마구잡이로 JOIN 되어 있으면, 옵티마이저는 "이건 너무 복잡해서 비트맵 인덱스를 못 타겠다"라며 인덱스를 포기하고 풀 스캔(Full Scan)으로 돌아버린다.

📢 섹션 요약 비유: 도서관 중앙(팩트 테이블)에 100억 권의 책이 쌓여있습니다. 손님이 "문학 코너(차원 A)에 있는 2023년(차원 B) 출판된 책 다 찾아줘!"라고 합니다. 멍청한 사서는 100억 권을 일일이 카트에 담은 뒤, 하나씩 바코드를 찍어보며(해시 조인) 1년 동안 책을 분류합니다.

Ⅱ. 스타 변환(Star Transformation)의 마법: 조인을 찢어라

조인을 서브쿼리로 바꾸면, 비트맵 인덱스가 빛의 속도로 춤을 춘다.

  1. 옵티마이저의 뇌수술 (서브쿼리 변환):
    • 오라클 옵티마이저(star_transformation_enabled = TRUE)가 이 쿼리를 낚아채어, 멍청한 JOIN 문을 수 개의 서브쿼리로 찢어버린다.
    • SELECT SUM(금액) FROM 매출 WHERE 고객ID IN (SELECT ID FROM 고객 WHERE 지역='강남') AND 상품ID IN (SELECT ID FROM 상품 WHERE 카테고리='노트북')
  2. 비트맵 인덱스 융합 (Bitmap AND 연산):
    • 쿼리가 서브쿼리(IN)로 바뀌는 순간, 매출 테이블에 깔려 있던 비트맵 인덱스들이 눈을 뜬다.
    • 1번 서브쿼리: "강남 고객의 ID는 1, 3, 5번이네!" $\rightarrow$ 매출 테이블의 고객ID 비트맵 인덱스를 켜서 1, 3, 5번 위치에 1이 켜진 맵을 뽑아낸다.
    • 2번 서브쿼리: "노트북 상품 ID는 7번이네!" $\rightarrow$ 매출 테이블의 상품ID 비트맵 인덱스를 켜서 7번에 1이 켜진 맵을 뽑아낸다.
    • 궁극의 연산: DB는 하드디스크를 읽지 않고 메모리 허공 위에서 방금 뽑은 2장의 얇은 비트맵 지도를 포개놓고 **AND 비트 연산 (0101 & 0011)**을 수행한다.
  3. 족집게 추출 (Table Access by RowID):
    • 비트 연산이 끝나면 100억 건 중 정확히 강남이면서 노트북을 산 교집합 데이터의 '정확한 주소(RowID)' 1,000개가 단 0.1초 만에 딱 떨어진다.
    • DB는 이제 하드디스크에 가서 이 1,000개의 주소만 족집게처럼 탁탁 집어와서 매출액을 더하고 끝낸다. 30분짜리 쿼리가 1초 만에 끝나는 순간이다.

📢 섹션 요약 비유: 스타 변환은 100억 권의 책을 뒤지지 않습니다. 대신 '문학 코너'라는 구멍 뚫린 OHP 투명 필름(비트맵 인덱스 1)과 '2023년 출판'이라는 투명 필름(비트맵 인덱스 2) 2장만 메모리 위로 가져옵니다. 두 필름을 햇빛에 겹쳐서 비춰보면(비트 AND 연산), 두 구멍이 공통으로 뚫린 자리에서만 빛이 새어 나옵니다(교집합 RowID). 사서는 그 빛이 뚫린 자리 번호 10개만 들고 서고로 뛰어가서 책 10권만 1초 만에 쏙 빼오는 천재적인 공간 탐색술입니다.

Ⅲ. 전제 조건과 튜닝 팁 (실무자의 함정)

아무 때나 켜지는 마법이 아니다. 철저한 판이 깔려 있어야 발동한다.

  1. 비트맵 인덱스의 절대적 강제:
    • 스타 변환은 일반적인 B-Tree 인덱스로는 작동하지 않는다. 반드시 팩트 테이블(매출)에 있는 모든 외래 키(고객ID, 상품ID, 날짜ID) 컬럼에 **비트맵 인덱스(Bitmap Index)**가 생성되어 있어야만 옵티마이저가 이 마법을 발동시킨다.
    • (참고: B-Tree 인덱스만 있어도 옵티마이저가 억지로 Bitmap으로 변환해 주는 Bitmap Conversion From Rowids 기술이 있지만, 효율이 100% 나오지 않는다.)
  2. 비트맵 인덱스와 DML 락(Lock)의 공포:
    • "우와! 그럼 모든 테이블에 비트맵 인덱스를 깔면 되겠네요?" $\rightarrow$ 미친 짓이다.
    • 비트맵 인덱스는 데이터 1건을 UPDATE 할 때, 그 데이터가 속한 수십만 건의 블록 전체에 락(Lock)을 걸어버리는 끔찍한 단점이 있다.
    • 그래서 1초에 1,000번씩 주문이 들어오는 **운영 DB(OLTP)**에 비트맵 인덱스(스타 변환)를 걸면 DB가 락 지옥에 빠져 죽어버린다. 오직 전날 밤 배치가 끝나고 데이터가 절대 변하지 않는 **조회 전용 데이터 웨어하우스(DW)**에서만 꺼내 들어야 하는 필살기다.
  3. 히든 파라미터 강제 발동:
    • 간혹 오라클이 똑똑한 척하며 스타 변환을 안 할 때가 있다.
    • 쿼리 맨 앞에 /*+ STAR_TRANSFORMATION */ 이라는 힌트(Hint)를 때려 넣어 옵티마이저의 멱살을 잡고 강제로 변환시킬 수 있다.

📢 섹션 요약 비유: 스타 변환(투명 필름 겹치기)은 완벽한 기술이지만, 이 필름(비트맵 인덱스)은 종이(데이터)에 글자를 수정(UPDATE)할 때마다 필름 전체를 불태우고 새로 깎아야 하는 치명적 약점이 있습니다. 만약 은행 창구(운영 DB)처럼 매초 잔액이 바뀌는 곳에 이 필름을 깔아두면 직원이 필름 깎느라 업무가 마비됩니다. 그래서 이 기술은 오직 한 달에 한 번씩 책이 들어오고 아무도 책 내용을 고치지 않는 정적인 '국립 기록 보관소(데이터 웨어하우스)'에서만 맘 편히 쓸 수 있는 전용 검색 툴입니다.