Star Transformation은 주로 DW 시스템에서 사용되는 쿼리 변환이다. 활용도는 그리 높지 않지만 관련 내용을 간단히 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자. t1이 fact 테이블, t2, t3가 dimension 테이블이다. t1_f1, t1_f2 인덱스는 비트맵 인덱스로 생성했다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2, ROWNUM AS c3 FROM XMLTABLE ('1 to 1000000');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE UNIQUE INDEX t1_pk ON t1 (c1);
CREATE UNIQUE INDEX t2_pk ON t2 (c1);
CREATE UNIQUE INDEX t3_pk ON t3 (c1);
CREATE BITMAP INDEX t1_f1 ON t1 (c2);
CREATE BITMAP INDEX t1_f2 ON t1 (c3);
CREATE INDEX t2_x1 ON t2 (c2);
CREATE INDEX t3_x1 ON t3 (c2);
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1);
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (c1);
ALTER TABLE t3 ADD CONSTRAINT t3_pk PRIMARY KEY (c1);
ALTER TABLE t2 ADD CONSTRAINT t1_f2 FOREIGN KEY (c2) REFERENCES t2 (c1);
ALTER TABLE t3 ADD CONSTRAINT t1_f3 FOREIGN KEY (c3) REFERENCES t3 (c1);
아래 쿼리는 STAR_TRANSFORMATION 힌트를 통해 Star Transformation 쿼리 변환을 수행했다. 아래와 같은 과정으로 쿼리가 수행된다. dimension 테이블(t2, t3)의 조건에 해당하는 fact 테이블(t1)의 ROWID를 생성하여, fact 테이블에 대한 Random 액세스를 최소화하는 원리다.
- t1을 조회하여 비트맵을 생성 (6 -> 5 -> 4)
- t2와 t1을 조인하여 비트맵을 병합 (10 -> 9 -> 11 -> 8 -> 7)
- t3와 t1을 조인하여 비트맵을 병합 (15 -> 14 -> 16 -> 13 -> 12)
- 1, 2, 3번 과정에서 생성된 비트맵을 AND 연산한 결과로 ROWID를 생성 (3 -> 2)
- 생성된 ROWID로 t1을 조회 (1)
-- 2
SELECT /*+ STAR_TRANSFORMATION */
a.*
FROM t1 a, t2 b, t3 c
WHERE a.c1 > 0
AND b.c1 = a.c2
AND b.c2 BETWEEN 1 AND 10
AND c.c1 = a.c3
AND c.c2 BETWEEN 1 AND 10;
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 |
| 2 | BITMAP CONVERSION TO ROWIDS | |
| 3 | BITMAP AND | |
| 4 | BITMAP CONVERSION FROM ROWIDS| |
| 5 | SORT ORDER BY | |
|* 6 | INDEX RANGE SCAN | T1_PK |
| 7 | BITMAP MERGE | |
| 8 | BITMAP KEY ITERATION | |
| 9 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 10 | INDEX RANGE SCAN | T2_X1 |
|* 11 | BITMAP INDEX RANGE SCAN | T1_F1 |
| 12 | BITMAP MERGE | |
| 13 | BITMAP KEY ITERATION | |
| 14 | TABLE ACCESS BY INDEX ROWID| T3 |
|* 15 | INDEX RANGE SCAN | T3_X1 |
|* 16 | BITMAP INDEX RANGE SCAN | T1_F2 |
--------------------------------------------------
아래와 같이 쿼리를 작성하면 유사한 동작을 유도할 수 있지만, 쿼리가 길어지고 성능이 저하될 수 있다.
-- 3
SELECT /*+ ORDERED USE_NL(A) ROWID(A) */
*
FROM t1 a
WHERE a.ROWID IN (SELECT /*+ UNNEST */
a.ROWID
FROM t1 a
WHERE a.c1 > 0
INTERSECT
SELECT /*+ LEADING(B) USE_NL(A) */
a.ROWID
FROM t2 b, t1 a
WHERE b.c2 BETWEEN 1 AND 10
AND b.c1 = a.c2
INTERSECT
SELECT /*+ LEADING(C) USE_NL(A) */
a.ROWID
FROM t3 c, t1 a
WHERE c.c2 BETWEEN 1 AND 10
AND c.c1 = a.c3);
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | VIEW | VW_NSO_1 |
| 3 | INTERSECTION | |
| 4 | INTERSECTION | |
| 5 | SORT UNIQUE | |
|* 6 | INDEX RANGE SCAN | T1_PK |
| 7 | SORT UNIQUE | |
| 8 | NESTED LOOPS | |
| 9 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 10 | INDEX RANGE SCAN | T2_X1 |
| 11 | BITMAP CONVERSION TO ROWIDS| |
|* 12 | BITMAP INDEX SINGLE VALUE | T1_F1 |
| 13 | SORT UNIQUE | |
| 14 | NESTED LOOPS | |
| 15 | TABLE ACCESS BY INDEX ROWID | T3 |
|* 16 | INDEX RANGE SCAN | T3_X1 |
| 17 | BITMAP CONVERSION TO ROWIDS | |
|* 18 | BITMAP INDEX SINGLE VALUE | T1_F2 |
| 19 | TABLE ACCESS BY USER ROWID | T1 |
------------------------------------------------------
아래 쿼리는 t3 테이블의 열을 조회했다. 열을 조회하기 위해 t3 테이블을 다시 조인하는 단계(3 -> 2)가 추가되었다. 비트맵 생성을 위해 t3 테이블을 조회한 단계(18 -> 17)와 중복된다.
-- 4
SELECT /*+ STAR_TRANSFORMATION */
a.*, c.*
FROM t1 a
, t2 b
, t3 c
WHERE a.c1 > 0
AND b.c1 = a.c2
AND b.c2 BETWEEN 1 AND 10
AND c.c1 = a.c3
AND c.c2 BETWEEN 1 AND 10;
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS BY INDEX ROWID | T3 | -- !
|* 3 | INDEX RANGE SCAN | T3_X1 | -- !
| 4 | TABLE ACCESS BY INDEX ROWID | T1 |
| 5 | BITMAP CONVERSION TO ROWIDS | |
| 6 | BITMAP AND | |
| 7 | BITMAP CONVERSION FROM ROWIDS| |
| 8 | SORT ORDER BY | |
|* 9 | INDEX RANGE SCAN | T1_PK |
| 10 | BITMAP MERGE | |
| 11 | BITMAP KEY ITERATION | |
| 12 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 13 | INDEX RANGE SCAN | T2_X1 |
|* 14 | BITMAP INDEX RANGE SCAN | T1_F1 |
| 15 | BITMAP MERGE | |
| 16 | BITMAP KEY ITERATION | |
| 17 | TABLE ACCESS BY INDEX ROWID| T3 | -- !
|* 18 | INDEX RANGE SCAN | T3_X1 | -- !
|* 19 | BITMAP INDEX RANGE SCAN | T1_F2 |
---------------------------------------------------
아래 쿼리는 STAR_TRANSFORMATION 힌트에 쿼리 변환의 동작을 지정했다. TEMP_TABLE에 지정한 테이블은 서브 쿼리 팩토링으로 처리된다.
-- 5
SELECT /*+ STAR_TRANSFORMATION(A SUBQUERIES((B) TEMP_TABLE(C))) SWAP_JOIN_INPUTS(C) */
a.*, c.*
FROM t1 a
, t2 b
, t3 c
WHERE a.c1 > 0
AND b.c1 = a.c2
AND b.c2 BETWEEN 1 AND 10
AND c.c1 = a.c3
AND c.c2 BETWEEN 1 AND 10;
-------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TEMP TABLE TRANSFORMATION | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6942_ADF921B |
| 3 | TABLE ACCESS BY INDEX ROWID | T3 |
|* 4 | INDEX RANGE SCAN | T3_X1 |
|* 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6942_ADF921B | -- !
| 7 | TABLE ACCESS BY INDEX ROWID | T1 |
| 8 | BITMAP CONVERSION TO ROWIDS | |
| 9 | BITMAP AND | |
| 10 | BITMAP CONVERSION FROM ROWIDS| |
| 11 | SORT ORDER BY | |
|* 12 | INDEX RANGE SCAN | T1_PK |
| 13 | BITMAP MERGE | |
| 14 | BITMAP KEY ITERATION | |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6942_ADF921B | -- !
|* 16 | BITMAP INDEX RANGE SCAN | T1_F2 |
| 17 | BITMAP MERGE | |
| 18 | BITMAP KEY ITERATION | |
| 19 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 20 | INDEX RANGE SCAN | T2_X1 |
|* 21 | BITMAP INDEX RANGE SCAN | T1_F1 |
-------------------------------------------------------------------------
아래와 같이 비트맵 조인 인덱스를 생성하면 Star Transformation 쿼리 변환없이 유사한 동작을 유도할 수 있다. dimension 테이블과 조인을 수행하지 않으므로 조회 성능이 개선된다.1
-- 6-1
DROP INDEX t1_f1;
DROP INDEX t1_f2;
CREATE BITMAP INDEX t1_f1 ON t1 (b.c2) FROM t1 a, t2 b WHERE b.c1 = a.c2;
CREATE BITMAP INDEX t1_f2 ON t1 (c.c2) FROM t1 a, t3 c WHERE c.c1 = a.c3;
-- 6-2
SELECT a.*
FROM t1 a
, t2 b
, t3 c
WHERE a.c1 > 0
AND b.c1 = a.c2
AND b.c2 BETWEEN 1 AND 10
AND c.c1 = a.c3
AND c.c2 BETWEEN 1 AND 10;
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID | T1 |
| 2 | BITMAP CONVERSION TO ROWIDS| |
| 3 | BITMAP AND | |
| 4 | BITMAP MERGE | |
|* 5 | BITMAP INDEX RANGE SCAN | T1_F1 |
| 6 | BITMAP MERGE | |
|* 7 | BITMAP INDEX RANGE SCAN | T1_F2 |
----------------------------------------------
Star Transformation 쿼리 변환은 star_transformation_enabled 파라미터와 관련이 있다. 해당 파라미터는 기본적으로 비활성화되어 있다. 파라미터 값을 TRUE로 설정하면 비용 기반으로 Star Transformation 쿼리 변환을 수행하고, TEMP_DISABLE로 설정하면 비용 기반으로 쿼리 변환을 수행하되 서브 쿼리 팩토링을 고려하지 않는다.
-- 7-1
SELECT name, default_value, description
FROM parameter
WHERE name = 'star_transformation_enabled';
NAME DEFAULT_VALUE DESCRIPTION
--------------------------- ------------- -------------------------------------
star_transformation_enabled FALSE enable the use of star transformation
1개의 행이 선택되었습니다.
-- 7-2
SELECT ordinal, value, isdefault
FROM v$parameter_valid_values
WHERE name = 'star_transformation_enabled';
ORDINAL VALUE ISDEFAULT
------- ------------ ---------
1 FALSE TRUE
2 TRUE FALSE
3 TEMP_DISABLE FALSE
3 행이 선택되었습니다.
- 반대로 비트맵 조인 인덱스에 의한 DML 성능 저하가 발생한다. [본문으로]
'Oracle > Tuning' 카테고리의 다른 글
| 트레이스 파일 병합 (0) | 2018.10.27 |
|---|---|
| PL/SQL 코드 성능 측정 (0) | 2018.10.17 |
| Partition-Wise 조인 (0) | 2018.10.14 |
| IOT Secondary 인덱스 (0) | 2018.10.08 |
| MBRC에 따른 physical read 성능 (0) | 2018.09.29 |
