06장. 케이스 스터디 : 스타 변환

6장 케이스 스터디:스타 변환

 

학습 목표

 

이 장을 완료하면 다음과 같은 작업을 수행 할 수 있다.

①   스타 스키마를 정의한다.

②   변환 없는 스타 쿼리의 실행 계획을 보여 줄 수 있다.

③   스타 변환의 요구사항을 정의 할 수 있다.

④   변환 후, 스타 쿼리의 실행 계획을 보여 줄 수 있다.

 

스타 스키마 모델

그림 6.1

 

스타 스키마는 가장 단순한 DataWarehouse 스키마이다. 이 스키마의 ERD는 중앙 테이블에서부터 사방으로 꼭지점이

뻗어 있는 별 모양을 닮았기 때문에 스타 스키마라고 부른다. 스타 스키마의 중앙은 하나 이상의 팩트 테이블로 구성되고,

꼭지점은 디멘젼 테이블이다. 스타 스키마는 DataWarehouse 내의 주요 정보를 포함하는 하나 이상의 대용량 팩트

테이블과 팩트 테이블의 속성에 대한 정보를 포함하고 있는 다수의 소용량 디멘젼 테이블(또는 룩업 테이블)로 이루어져 있다.

스타 쿼리는 하나의 팩트 테이블과 다수의 디멘젼 테이블의 조인이다. 각 디멘젼 테이블은 상호 간에 조인이 불가능하다.

CBO는 스타 쿼리를 인식하고, 효율적인 실행 계획을 생성한다. 전형적인 팩트 테이블은 키와 측정 값을 포함한다. 예를

들어, Sales History 스키마에서 sales 팩트 테이블은 quantity_sold, amount, cost 측정값과 cust_id, time_id, prod_id,

channel_id, promo_id 키 값을 가지고 있다. 디멘젼 테이블들은 customers, times, products, channels, promotions이다.

예를 들어, products 디멘젼 테이블은 팩트 테이블에 나타나는 각 상품 번호에 대한 정보를 포함한다.

 

참고 : 이 모델에 하나 이상의 팩트 테이블을 포함하여 일반화하는 것은 어려운 작업이 아니다.

 

눈꽃송이(Snowflake) 스키마 모델

그림 6.2

 

눈꽃송이 스키마는 스타 스키마에 비해서 좀 더 복잡한 DataWarehouse 모델이며, 스타 스키마의 일종이다. 스키마의

ERD가 눈꽃송이 모양과 닮았기 때문에 눈꽃송이 스키마라고 부른다. 눈꽃송이 스키마는 디멘젼 테이블의 중복을 제거하기

위해 디멘젼 테이블을 정규화한다. 즉, 디멘젼 데이터는 하나의 대용량 테이블 대신 여러 테이블들로 정규화 된다. 예를

들어, 스타 스키마의 상품 디멘젼 테이블은 눈꽃송이 스키마 내에서 products, product_category, product_manufacturer

테이블로 정규화 될 수 있다. 또는, 그림과 같이 countries 테이블을 사용하여 customers 테이블을 정규화 할 수 있다. 이

모델은 저장 공간을 절약 할 수 있는 반면에 디멘젼 테이블의 개수를 증가시키고 많은 외래 키 조인을 필요로 한다. 그

결과로 쿼리는 더욱 복잡해지고 쿼리 성능은 저하된다.

 

 참고 : 눈꽃송이 스키마를 사용해야 할 명확한 사유가 없다면 스타 스키마를 사용하도록 한다.

 

 스타 쿼리 : 예제

 

그림 6.3

 

그림과 같은 스타 쿼리를 가정하자. 스타 변환이 수행되려면 Sales History 스키마의 sales

테이블은 time_id, channel_id, cust_id 컬럼에 비트맵 인덱스가 정의 되어 있어야 한다.

 

스타 변환 없는 실행 계획

그림 6.4

 

스타 변환의 장점을 살펴보기 전에 이러한 변환 없이 스타 스키마의 조인이 어떻게 처리되는지 살펴 봐야 한다.

그림에서 실행 계획의 기본적인 문제점은 언제나 SALES 테이블을 디멘젼 테이블로 조인하면서 시작된다는 것이다.

실행 계획에서 많은 수의 행들이 다른 부모 테이블과 조인에 의해서 제거된다.

 

스타 변환

 

스타 쿼리에 대해 최적의 성능을 얻으려면 다음과 같은 몇 가지 기본 지침을 따르는 것이 중요하다.

①   팩트 테이블 또는 테이블들의 각 외래 키 컬럼에 비트맵 인덱스를 정의하여야 한다.

②   STAR_TRANSFORMATION_ENABLED 파라메터는 TRUE로 설정하여야 한다. 이것은 스타 쿼리에 대한 주요 

옵티마이저 기능을 활성화한다. 역 호환성을 위해 이 파라메터는 FALSE로 설정되어 있다.

DataWarehouse가 이러한 조건들을 만족한다면, DataWarehouse에서 실행되는 스타 쿼리의 대부분은 스타

변환이라고 알려진 쿼리 실행 전략을 사용한다. 스타 변환은 스타 쿼리에서 가장 효율적인 성능을 제공한다.

 

스타 변환은 원본 스타 쿼리의 SQL을 암묵적으로 재 작성(또는 변환)하는 강력한 최적화 기술이다. 최종 사용자는 스타

변환에 대하여 구체적인 내용을 알 필요가 없다. 시스템의 CBO는 필요한 상황이 되면 자동적으로 스타 변환을 선택한다. 

오라클은 다음과 같은 두 가지 기본 단계를 이용하여 스타 쿼리를 처리한다.

①  첫 번째 단계는 팩트 테이블(결과 집합)에서 정확하게 필요한 행들을 읽어온다. 이 작업은 비트맵 인덱스를 사용하기 때문에 매우 효율적이다.

②  두 번째 단계는 이 결과 집합을 디멘젼 테이블들과 조인한다. 이 작업을 세미조인이라고 부른다.

 

참고 : 해당 쿼리에 사용된 테이블은 최소 3개 이상이어야 한다(두 개의 디멘젼 테이블과 하나의 팩트 테이블).

 

스타 변환 : 고려 사항

 

스타 변환은 다음과 같은 특징을 갖는 테이블에 대해서는 지원하지 않는다.

①   비트맵 액세스 경로와 호환되지 않는 테이블 힌트를 가진 쿼리

②   바인드 변수를 포함한 쿼리

③   비트맵 인덱스의 개수가 너무 적은 테이블들. 팩트 테이블의 컬럼에 대하여 비트맵 인덱스가

존재하여야만 옵티마이저가 이를 위한 서브쿼리를 생성 할 수 있다.

④   원격 팩트 테이블. 그러나, 생성 할 서브쿼리 내에서 원격 디멘젼 테이블은 허용된다.

⑤   안티 조인된 테이블들

⑥   서브쿼리 내에 디멘젼 테이블로 이미 사용된 테이블들

⑦   실제로 병합되지 않은 뷰. 뷰 파티션이 아님

 

스타 변환 : 재 작성 예제

그림 6.5

시스템은  두  단계를  통해  쿼리를  처리한다.  첫  번째  단계에서  시스템은  디멘젼  테이블의 기본키를 읽어 오기 위해 디멘젼
테이블에 대하여 필터를 사용한다. 시스템은 팩트 테이블로부터 필요한  행들만  식별하고  읽어오기 위해  앞서  읽은  기본키를
사용하여 팩트  테이블의 외래키 컬럼들에 정의된 비트맵 인덱스를 검증한다. 즉, 시스템은 그림과 같이 재작성된 쿼리를 이용하여
판매 테이블로부터 결과 집합을 읽어 온다.

 

참고 : 그림의 SQL은 1 단계에서 수행되는 작업을 표현하기 위한 이론적인 SQL 문장이다.

 하나의 디멘젼 테이블에서 팩트 테이블의 행을 읽어오기

 

 

그림 6.6

 

그림은 하나의 디멘젼 테이블을 사용하여 팩트 테이블의 행들을 읽어오는 것이다. 그림의 예제에서

t.calendar_quarter_desc IN (‘1999-Q1’,’1999-Q2’) 와 같은 디멘젼 테이블의 필터 조건을 기반으로 시스템은 디멘젼

테이블을 스캔하고 각 해당되는 행들을 찾아 팩트 테이블의 비트맵 인덱스를 검증하고 해당 되는 비트맵을 인출한다.

BITMAP KEY ITERATION은 좌측 입력으로부터 읽어 온 각 키를 우측 입력에 대한 인덱스를 검색하는 룩업 키로 사용하며,

해당 인덱스에 의해 인출된 모든 비트맵들을 리턴한다. 이 경우에 좌측 입력은 디멘젼 테이블과의 조인 키를 제공한다.

이 트리는 마지막 단계에서 이전 단계에서 인출한 모든 비트맵을 머지한다. 머지 작업은 하나의 비트맵을 생성하며 팩트

테이블을 디멘젼 테이블과 조인한 결과를 나타낸다.

참고. BITMAP_MERGE_AREA_SIZE는 공유 서버 모드를 사용하는 경우, 이러한 작업의 성능을 튜닝하는데 중요한 역할을

한다. 인스턴스가 공유 서버 옵션으로 구성되어 있지 않는 한, BITMAP_MERGE_AREA_SIZE 파라메터의 사용을 추천하지

않는다. 시스템은 SQL 작업 영역의 크기가 자동으로 조정되도록 PGA_AGGREGATE_TARGET파라메터를 설정하도록 권장한다.

역 호환성을 위해서 BITMAP_MERGE_AREA_SIZE를 유지 할 수도 있다.

 

모든 디멘젼 테이블에서 팩트 테이블의 행들을 읽어 오기

 

그림 6.7

 

첫 번째 단계가 진행되는 동안, 앞의 그림에서 언급한 단계들이 각 디멘젼 테이블에 대하여 반복된다. 그래서, 실행 계획의

BITMAP MERGE는 단일 디멘젼 테이블에 대한 하나의 비트맵을 생성한다. 팩트 테이블에서 모든 행들을 식별하려면,

시스템은 생성된 모든 비트맵을 AND 연산하여야 한다. 이 작업은 하나의 디멘젼 테이블과 조인된 행들은 제거하고,

모든 디멘젼 테이블들과 조인된 행들은 찾는 것이다. 이 작업은 각 디멘젼에서 생성한 모든 비트맵에 대하여 가장 효율적인

BITMAP AND 연산을 수행 함으로서 얻을 수 있다. 최종 비트맵은 모든 디멘젼 테이블의 행들과 조인된 팩트 테이블로부터

읽어야 할 행들을 가리킨다.

 

참고 : 지금까지는 오직 팩트 테이블의 비트맵 인덱스와 디멘젼 테이블만이 사용되었다. 팩트 테이블에 액세스하려면

시스템은 생성한 비트맵을 rowid 집합으로 반드시 변환하여야 한다.

 

중간 결과 집합과 디멘젼 테이블의 조인

그림 6.8

 

결과 집합이 결정되면, 시스템은 스타 변환 알고리즘의 2 단계로 진입한다. 이 단계에서 행들을 그룹화하고 쿼리의 SELECT

목록을 가져오기 위해 결과 집합에 해당하는 판매 데이터를 디멘젼 테이블 데이터와 조인 할 필요가 있다.

위 그림에서는 팩트 테이블과 디멘젼 테이블을 해시 조인하였다. 비록, 해시 조인이 스타 쿼리에서 행들을 조인하기

위해 가장 많이 사용되는 방식이기는 하지만 항상 옳은 것은 아니며, 이는 CBO에 의해서 평가된다.

 

스타 변환 실행 계획 : 예제 1

 

그림 6.9

 

위 예제는 앞서 스타 변환 없는 실행 계획에서 보여준 쿼리를 스타 변환한 실행 계획이다. 포맷팅을 위해 channels와

times 디멘젼만 표시하였다. n 디멘젼에 대한 경우를 일반화하는 것은 어렵지 않다.

 

참고 : slaes는 파티셔닝 되지 않았다고 가정하였다.

 

스타 변환 : 심화된 최적화

그림 6.10

 

이젼 실행 계획을 보면, 각 디멘젼 테이블이 두 번씩 액세스 되었음을 확인 할 수 있다. 한번은 첫 번째 단계에서 팩트

테이블로부터 필요한 행들을 결정하기 위한 것이었고, 다른 한번은 두 번째 단계에서 팩트 테이블의 행들을 각 디멘젼

테이블과 조인하기 위한 것이었다. 만약, 디멘젼 테이블이 크고, 빠른 액세스 경로가 존재하지 않는다면 성능상의 문제점이

될 수도 있다. 그러한 경우에 시스템은 두 단계에서 필요로 하는 정보를 모두 포함하는 임시 테이블을 생성 할 수도 있다.

만약, 디멘젼 테이블의 조건 절과 조인 컬럼을 만족하는 결과 집합으로 구성된 임시 테이블의 생성 비용이 디멘젼 테이블을

두 번 액세스하는 비용보다 저렴하다면 임시 테이블을 생성 할 것이다. 이전 실행 계획의 예제에서는 TIMES와 CHANNEL

테이블들이 매우 작고, 전체 테이블 스캔을 사용하여 디멘젼 테이블을 액세스하는 것이 큰 문제가 되지 않았다.

임시 테이블의 생성과 해당 데이터 저장은 실행 계획에 나타난다. 이러한 임시 테이블들의 이름은 시스템에 의해서

자동으로 명명되며 매번 변경 된다. 그림의 실행 계획에서 CUSTOMERS 테이블에 대하여 임시 테이블을 생성하여 사용하는

것을 확인 할 수 있다.

 

참고 : 또한, 임시 테이블은 다음 조건 하에서 스타 변환을 사용하지 않는다.

①   데이터베이스가 읽기 전용 모드인 경우

②   스타 쿼리가 직렬화 모드에 있는 트랜잭션의 일부인 경우

 

비트맵 조인 인덱스의 사용

그림 6.11

 

만약, 조인 인덱스가 사용되면 조인 작업이 이미 계산되었기 때문에 조인해야 할 데이터의 양은 감소 될 수 있다.

또한, 여러 개의 디멘젼 테이블을 포함하는 조인 인덱스는 기존의 비트맵 인덱스를 가진 스타 변환에 필수적인

비트별 연산을 제거 할 수도 있다. 결국, 비트맵 조인 인덱스는 구체화된 조인 뷰(MJV)와 비교할 때 저장 구조 측면에서

매우 효율적이다. 그 이유는 MJV의 경우, 팩트 테이블의 rowid를 압축하지 않기 때문이다.

그림에서는 언급한 추가 인덱스 구조를 생성했다고 가정하였다.

 

스타 변환 실행 계획 : 예제 2

 

그림 6.12

비트맵 조인 인덱스를 사용하는 동일한 스타 쿼리의 처리는 이전 예제와 유사하다. 유일한 차이점은 스타 쿼리의

첫 번째 단계에서 times 데이터에 액세스하는 단일 테이블 비트맵 인덱스 대신에 조인 인덱스를 사용한다는 점이다.

이전 실행 계획과 비교할 때의 차이점은 1 단계에서 재 작성된 쿼리에서 times 디멘젼에 대한 비트맵 인덱스 스캔의

내부 파트가 하위 SELECT를 갖지 않는다는 것이다. 이것은 times.calendar_quarter_desc에 대한 조인 조건 정보가

sales_q_bjx 비트맵 조인 인덱스를 이미 만족하고 있기 때문이다.

조인 인덱스에 대한 액세스는 두 번 발생하는데, 그 이유는 해당 쿼리의 조건 절이 t.calendar_quarter_desc IN

(‘1991-Q1’,’1999-Q2’)이기 때문이다.

 

스타 변환 힌트

①   STAR_TRANSFORMATION 힌트는 옵티마이저가 변환이 완료된 최상의 실행 계획을 사용하도록 만든다. 힌트가

없으면, 옵티마이저는 비용 기반의 결정을 내리게 되는데, 변환된 쿼리에 대해서 가장 우수한 실행 계획 대신 변환 없이

생성된 가장 우수한 실행 계획을 사용하게 된다. 만약, 힌트가 주어졌지만 변환이 발생 할 보장이 없다면, 적절할 것으로

보여지는 서브쿼리만을 생성한다. 만약, 어떠한 서브쿼리도 생성되지 않고, 변환된 쿼리도 없다면 힌트와 상관 없이,

변환되지 않은 쿼리에 대하여 가장 우수한 실행 계획이 사용된다. 

②   스타 변환에서 FACT 힌트를 사용하면 힌트에서 지정된 테이블이 팩트 테이블로 처리되고, 다른 테이블들은 용량에

상관 없이 디멘젼 테이블로 처리된다.

③   스타 변환에서 NO_FACT 힌트가 사용되면 힌트에서 지정된 테이블은 팩트 테이블로 처리되지 않는다.

 

참고 : FACT와 NO_FACT 힌트는 스타 쿼리에서 하나 이상의 팩트 테이블들이 존재하는 경우에 유용 할 수도 있다.

 

비트맵 조인 인덱스 : 조인 모델 1

 

 

그림 6.13

 

이 후의 내용에서 F는 팩트 테이블, D는 디멘젼 테이블, PK는 기본 키, FK는 외래 키이다.

 

비트맵 조인 인덱스는 조인 작업을 방지하기 위해 SELECT 문장에서 사용 될 수 있다. 구체화 된 조인 뷰와 유사하게

비트맵 조인 인덱스는 조인 작업을 미리 계산하고 데이터베이스 객체로서 저장해둔다. 두 경우의 차이점은 구체화된

조인 뷰는 조인 작업 결과를 테이블에 저장하는 반면 비트맵 조인 인덱스는 조인 작업 결과를 비트맵 인덱스에 저장한다.

 

참고 : C1은 디멘젼 테이블에서 인덱스가 정의된 컬럼이다.

 

비트맵 조인 인덱스 : 조인 모델 2

그림 6.14

 

이 모델은 모델 1을 확장한 것으로 이 모델을 표현하기 위해 결합 비트맵 조인 인덱스가 필요하다.

 

이 경우에 BJX는 다음과 같은 SELECT 문장에서 사용될 수 있다.

select sum(f.facts) from d, f where d.pk=f.fk and d.c1=1d

D.C1이 BJX의 선두 컬럼이기 때문이다.

 

비트맵 조인 인덱스 : 조인 모델 3

그림 6.15

 

이 모델도 결합 비트맵 조인 인덱스를 필요로 한다. 이 경우에 두 개의 디멘젼 테이블이 사용된다.

 

비트맵 조인 인덱스 : 조인 모델 4

그림 6.16

위 그림은 두 개 이상의 디멘젼 테이블을 포함하는 눈꽃송이 모델을 보여준다. 이 모델도 비트맵 조인 인덱스로 표현된다.

비트맵 조인 인덱스는 디멘젼 테이블에서 인덱스를 정의 할 컬럼들의 개수에 따라 단일 또는 결합 인덱스가 될 수 있다.

D1.C1에 정의된 비트맵 조인 인덱스는 그림과 같이 D1과 D2를 조인하고 D2와 F를 조인한다.