4-3 Database Management - Configure a schema to support a Star Transformation query

출처: http://wiki.gurubee.net/pages/viewpage.action?pageId=1966765

 

/* Star 스키마는 하나의 큰 팩트 테이블과 몇몇 차원 테이블로 구성된다. STAR_TRANSFORMATION_ENABLED 파라미터가 Star Transformation을 세션 레벨로 진행할지, 아니면 데이터베이스 레벨로 진행할 지 결정한다. default 값는 FALSE이다. */

/* Star 스키마는 OLAP 분석에 적합한 반정규화 모델이다. */

/* 두 비트맵 조인 인덱스는 팩트 테이블과 차원 테이블 조인 없이 사용된다. */

 

 

 

CREATE TABLE tbl_dimension1
(r_num	NUMBER CONSTRAINT tbl_dimension1_pk PRIMARY KEY,
 day_of_week VARCHAR2(20));

CREATE TABLE tbl_dimension2
(r_num	NUMBER CONSTRAINT tbl_dimension2_pk PRIMARY KEY,
 day_of_week VARCHAR2(50));

CREATE TABLE tbl_fact
(r_num	NUMBER, 
 r1		NUMBER CONSTRAINT fk_fact_dim1 REFERENCES tbl_dimension1,
 r2		NUMBER CONSTRAINT fk_fact_dim2 REFERENCES tbl_dimension2);


INSERT INTO tbl_dimension1
SELECT	rownum, trim(to_char(sysdate + rownum, 'day'))
FROM		dual CONNECT BY LEVEL <= 10;
INSERT INTO tbl_dimension2
SELECT	rownum, trim(to_char(sysdate + rownum, 'day'))
FROM		dual CONNECT BY LEVEL <= 10;
INSERT INTO tbl_fact
SELECT	rownum,
		round(dbms_random.value(1,10)),
		round(dbms_random.value(1,10))
FROM		dual CONNECT BY LEVEL <= 50000;
COMMIT;


CREATE BITMAP INDEX idx_b1 ON tbl_fact(r1);
CREATE BITMAP INDEX idx_b2 ON tbl_fact(r2);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'tbl_fact', CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'tbl_dimension1', CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'tbl_dimension2', CASCADE=>TRUE);


SET AUTOTRACE ON EXP;
SELECT	count(*)
FROM		tbl_fact, tbl_dimension1, tbl_dimension2
WHERE		tbl_fact.r1 = tbl_dimension1.r_num
AND		tbl_fact.r2 = tbl_dimension2.r_num
AND		tbl_dimension1.day_of_weeks = 'monday'
AND		tbl_dimension2.day_of_weeks = 'thursday';


ALTER SESSION SET STAR_TRANSFORMATION_ENABLED = TRUE;
SELECT	count(*)
FROM		tbl_fact, tbl_dimension1, tbl_dimension2
WHERE		tbl_fact.r1 = tbl_dimension1.r_num
AND		tbl_fact.r2 = tbl_dimension2.r_num
AND		tbl_dimension1.day_of_weeks = 'monday'
AND		tbl_dimension2.day_of_weeks = 'thursday';

SELECT	count(*)
FROM		tbl_fact
WHERE		r1 IN (SELECT	r_num
			 FROM		tbl_dimension1
			 WHERE	day_of_week = 'monday')
AND		r2 IN (SELECT	r_num
			 FROM		tbl_dimension2
			 WHERE	day_of_week = 'thursday');
             
 
 
 CREATE BITMAP INDEX idx_bt_join ON tbl_fact (tbl_dimension1.day_of_week)
FROM	tbl_fact, tbl_dimension1
WHERE	tbl_fact.r1 = tbl_dimension1.r_num;

CREATE BITMAP INDEX idx_bt_join2 ON tbl_fact (tbl_dimension2.day_of_week)
FROM	tbl_fact, tbl_dimension2
WHERE	tbl_fact.r2 = tbl_dimension2.r_num;


SELECT     count(*)
FROM               tbl_fact, tbl_dimension1, tbl_dimension2
WHERE              tbl_fact.r1 = tbl_dimension1.r_num
AND                tbl_fact.r2 = tbl_dimension2.r_num
AND                tbl_dimension1.day_of_week = 'monday'
AND                tbl_dimension2.day_of_week = 'thursday';