1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2011년 하반기 대용량데이터베이스 스터디
  4. 2.3.6 스타조인

2.3.6 스타조인 (by ljh1148) [2011.10.07]

2.3.6. 스타 조인

스타조인이란?

  • 어떤 의미에서 볼 때는 새로운 방식의 조인이 아니라고도 할 수 있다.
  • 개념적으로만 생각해 본다면 기존의 조인방식들로 수행하면서 단지 조인의 실행계획이 특정한 처리절차로 수행되는 것일 뿐이라고 할 수 있다.
  • 스타라는 말이 사용된 연유는 조인된 집합들 가의 관계들이 마치 별 모양처럼 생겼기 때문에 붙여진 것이다.
  • 스타 스키마

위 그림의 문제점들...

  • 1) 처리범위를 줄일 수 있는 조건들이 여러 테이블에 분산되어 있다는 점에 유의하자. ( 조인은 다이 다이 )
  • 2) 각 테이블이 보유 하고 있는 조건들의 면면을 살펴보면 어느 것을 선택하더라도 처리 범위가 별로 줄어 들지 않는다는 것을 알 수 있다. ( SALES 테이블의 처림범위를 얼마나 효과적으로 줄일수 있는냐가 관건 )
  • 3) SALES 테이블이 남의 도움을 받지 않고 수행할 수 있는 SALES_DATE는 3개월이나 되어 처리 범위가 만만치 않다. ( 스스로 힘들어 함 )
  • 4) SALES_TYPE이나 SALES_DEPT의 도움을 받더라도 그렇게 많은 처리범위가 줄어들 것으로 보이지도 않는다.
  • 5) 물론 이러한 경우를 대비해서 'SALES_TYPE + SALES_DATE' or 'SALES_DEPT + SALES_DATE, PRODUCT_CD + SALES_DATE'로 된 결합인덱스를 생성해 둔다면 얼마간 효과를 얻을 수 있다. ( 하지만 이런 인덱스 정책이라면 선택할 수 있는 해법은 아님 )
  • 6) 물론 사용자 요구 사항이 적어 인덱스 개수가 그리 많지 않고, 효과 또한 크다면 적절한 인덱스 전략을 통해 해결이 가능하다.
  • 7) 각 연결단계마다 조금씩 줄여 마지막 결과가 소량이 되었다면, 결과적으로 불필요한 처리가 많이 발생 했음을 의미한다.
  • 8) 연결작업이 수행되고 난 결과 집합은 이미 기존의 테이블이 아니므로 이제 더 이상 인덱스를 가질 수 없다.
    만약 NL 조인이라면 어쩔 수 없이 선행처리 집합이 되어서 다른 테이블을 랜덤으로 연결하게 되며, 해쉬조인이나 Sort Merge 조인이 되더라도 각 조인 단계의 결과 집합이 적지 않은 크리를 가지고 있으므로 부담이 된다.

위 문제점을 해결 할 수있는 논리지적으로 가장 이상적인 처리 방법은.?

  • 1) 소량의 데이터를 가지고 있는 집합들이 힘을 모아 상수 집합을 만든다.
  • 2) 만든 상수집합을 일거에 SALES 테이블에 제공할 수만 있다면 대량의 데이터를 가진 SALES 테이블이 한 번만에 연결작업을 수행하게 된다.
  • 3) 만약 적절한 인덱스가 존재해 이러한 상수값들로 처적의 처리범위를 줄여줄 수만 있다면 바로 소량의 결과만 추출할 수 있게 되므로 아주 이상적인처리방법이 될 것이다.

위 이상적인 방법을 실현하기 위해서는 반드시 해결해야되는 몇 가지 문제점은.?

  • 1) 디멘전테이블 가에는 릴레이쉽이 없기 때문에 이들 간의 연결을 먼저 시도할 수 없다는 점 ( 디멘전테이블들 간의 카티션곱으로 해결 할수 있음 )
  • 2) 상수 집합값에 해당하는 팩터테이블에 적절한 인덱스를 보유해야함 ( 어떤 디멘전 테이블이 사용될지 종잡을 수 없어서 인덱스 정책 수립이 쉽지만은 않다. 최적에 인덱스는 '=' )
  • 3) D1, D2, D3, D4들의 디멘전 테이블 = 15가지 경우의 수 ( 2의 4승 ), 디멘저 테이블이 10개면 2의 10승 <= 이런 인덱스 정책문제가 가장 근본적인 문제임

위 그림 설명...( 스타 조인 )

  • 1) 디멘전 테이블 D1, D2, D3를 '무조건'( 카티션곱 ) 을 연결고리로 하여 조인하면 카티션 곱으로 만들어진 우측의 CARTESIAN_T와 같은 집합이 만들어 진다.
  • 2) 쓸모없는 집합 같지만 3개의 디멘전 테이블들이 만들어 낼 수 모든 경우의 수를 가지고 있다.
  • 3) 그림의 좌측 하단에 있는 FACT_T는 데이터모델 상으로는 디멘전 테이블들과 릴레이션쉽을 맺고 있다. 그러나 논리적으로 보면 우측의 CARTESIAN_T와 릴레이션쉽을 맺고 있다는 것과 동일한 의미가 된다.
  • 4) 일반적으로 디멘전 테이블은 아주 소량으로 구성된다. 개수가 많지않다면 이들의 곱 또한 크게 걱정할 것이 없다. ( 2 * 3 * 2 = 12 )
  • 5) 문제의 초점을 좀더 명확히 하기 위해 위 그림에 있는 FACT_T를 백만 건이라고 가정해보자. 어떤 조인 방식을 도입하건 FACT_T와 D1의 조인은 100만건과 두건의 연결이 발생하고
    그 결과는 다시 백만 건이 되어 D2와 연결을 수행하여야 한다. 그 결과는 또 다시 백만건이 된다
    그 결과를 또 또 다시 D3와 연결을 해야한다.

스타 조인에 주의 할점

  • 1) 디멘저의 카티션 곱이 지나치게 많은 집합을 만들지 않을 때만 사용해야 한다.
  • 2) 지나치게 높은 카디널리티가 가진 디멘저 테이블이 존재한다면.. 스타조인에 배제하고 경우에 따라서 먼저 조인을 수행시키든지, 아니면 최종 결과와 마지막으로 조인을 하는 것이 바람직하다.
p. 606 준비 스크립트
{CODE:SQL}

SQL> SELECT * FROM V$VERSION WHERE ROWNUM <= 1;

BANNER

-
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CREATE TABLE COUNTRY_JH TABLESPACE ORA03TS02 AS
SELECT TO_CHAR( LEVEL, 'FM000009' ) COUNTRY_CD
, 'NAME_'||LEVEL AS COUNTRY_NAME
FROM DUAL
CONNECT BY LEVEL <= 500

CREATE TABLE TYPES_JH TABLESPACE ORA03TS02 AS
SELECT CHR( 64 + LEVEL ) TYPE_CD
, 'NAME_'||CHR( 64 + LEVEL ) AS TYPE_NAME
FROM DUAL
CONNECT BY LEVEL <= 26

CREATE TABLE PRODUCTS_JH TABLESPACE ORA03TS02 AS
SELECT PRODCUT_CD2||PRODCUT_CD1||TO_CHAR( LV, 'FM009') AS PRODUCT_CD
, 'NAME_'||PRODCUT_CD2||PRODCUT_CD1||TO_CHAR( LV, 'FM009') AS PRODUCT_NAME
FROM (SELECT CHR( 64 + LEVEL ) PRODCUT_CD1 FROM DUAL CONNECT BY LEVEL <= 26 ) A
, (SELECT CHR( 64 + LEVEL ) PRODCUT_CD2 FROM DUAL CONNECT BY LEVEL <= 26 ) B
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 101 ) C
--WHERE PRODCUT_CD2 = 'B'

CREATE TABLE DEPT_JH TABLESPACE ORA03TS02 AS
SELECT DISTINCT DEPT_NO, DEPT_NAME
FROM (
SELECT RPAD( LEVEL, 5, '0' ) AS DEPT_NO
, 'NAME_'|| RPAD( LEVEL, 5, '0' ) AS DEPT_NAME
FROM DUAL
CONNECT BY LEVEL <= 99999
)

CREATE TABLE SALES_JH TABLESPACE ORA03TS02 AS
SELECT LEVEL SALES_NO
, TO_CHAR( TRUNC( dbms_random.value( 1,501 ) ), 'FM000009' ) COUNTRY_CD
, CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) || CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) || TO_CHAR( TRUNC( dbms_random.value( 1,102 ) ), 'FM009' ) PRODUCT_CD
, RPAD( TRUNC( dbms_random.value( 1,99999 + 1 ) ), 5, '0' ) SALES_DEPT
, CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) AS SALES_TYPE
, TO_CHAR( ( SYSDATE - 100 ) - 1 / 1440 / ( 60 / ( 2000001 - LEVEL )), 'YYYYMMDDHH24MISS' ) SALES_DATE
, TRUNC( dbms_random.value( 1,501 ) ) * 1000 AS SALES_AMOUNT
, 'list_price' AS LIST_PRICE
FROM DUAL
CONNECT BY LEVEL <= 2000000

ORA-30009: CONNECT BY 작업에 대한 메모리가 부족합니다. ㅡㅡ^

CREATE TABLE SALES_JH TABLESPACE ORA03TS02 AS
SELECT LEVEL SALES_NO
, TO_CHAR( TRUNC( dbms_random.value( 1,501 ) ), 'FM000009' ) COUNTRY_CD
, CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) || CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) || TO_CHAR( TRUNC( dbms_random.value( 1,102 ) ), 'FM009' ) PRODUCT_CD
, RPAD( TRUNC( dbms_random.value( 1,99999 + 1 ) ), 5, '0' ) SALES_DEPT
, CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) AS SALES_TYPE
, TO_CHAR( ( SYSDATE - 100 ) - 1 / 1440 / ( 60 / ( 20000001 - LEVEL )), 'YYYYMMDDHH24MISS' ) SALES_DATE
, TRUNC( dbms_random.value( 1,501 ) ) * 1000 AS SALES_AMOUNT
, 'list_price' AS LIST_PRICE
FROM DUAL
CONNECT BY LEVEL <= 0

TRUNCATE TABLE SALES_JH;

BEGIN

FOR IDX IN 1 .. 50000000 LOOP

INSERT INTO SALES_JH
SELECT IDX SALES_NO
, TO_CHAR( TRUNC( dbms_random.value( 1,501 ) ), 'FM000009' ) COUNTRY_CD
, CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) || CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) || TO_CHAR( TRUNC( dbms_random.value( 1,102 ) ), 'FM009' ) PRODUCT_CD
, RPAD( TRUNC( dbms_random.value( 1,99999 + 1 ) ), 5, '0' ) SALES_DEPT
, CHR( TRUNC( 64+dbms_random.value( 1,27 ) ) ) AS SALES_TYPE
, TO_CHAR( ( SYSDATE - 100 ) - 1 / 1440 / ( 60 / ( 50000001 - IDX )), 'YYYYMMDDHH24MISS' ) SALES_DATE
, TRUNC( dbms_random.value( 1,501 ) ) * 1000 AS SALES_AMOUNT
, 'list_price' AS LIST_PRICE
FROM DUAL;

END LOOP;

COMMIT;

END;

CREATE UNIQUE INDEX COUNTRY_JH_PK ON COUNTRY_JH ( COUNTRY_CD ) TABLESPACE ORA03TS02

CREATE UNIQUE INDEX TYPES_JH_PK ON TYPES_JH ( TYPE_CD ) TABLESPACE ORA03TS02

CREATE UNIQUE INDEX PRODUCTS_JH_PK ON PRODUCTS_JH ( PRODUCT_CD ) TABLESPACE ORA03TS02

CREATE UNIQUE INDEX DEPT_JH_PK ON DEPT_JH ( DEPT_NO ) TABLESPACE ORA03TS02

CREATE INDEX SALES_JH_INDEX01 ON SALES_JH ( SALES_DEPT, PRODUCT_CD ) TABLESPACE ORA03TS02

-- 제약 조건 생성 후 통계정 보를 생성해야 STAR로 풀릴수 있다. ( 이거때무네.. ㅠ 4시간 삽질 )

ALTER TABLE DEPT_JH ADD (
CONSTRAINT DEPT_JH_PK
PRIMARY KEY
(DEPT_NO)
USING INDEX DEPT_JH_PK);

ALTER TABLE PRODUCTS_JH ADD (
CONSTRAINT PRODUCTS_JH_PK
PRIMARY KEY
(PRODUCT_CD)
USING INDEX PRODUCTS_JH_PK);

ALTER TABLE TYPES_JH ADD (
CONSTRAINT TYPES_JH_PK
PRIMARY KEY
(TYPE_CD)
USING INDEX TYPES_JH_PK);

ALTER TABLE COUNTRY_JH ADD (
CONSTRAINT COUNTRY_JH_PK
PRIMARY KEY
(COUNTRY_CD)
USING INDEX COUNTRY_JH_PK);

--ALTER TABLE TYPES_JH DROP CONSTRAINT SALES_JH_FK2;

ALTER TABLE SALES_JH ADD (
CONSTRAINT SALES_JH_FK4
FOREIGN KEY (SALES_DEPT) --SALES_DEPT 4 Y VARCHAR2 (20 Byte) Height Balanced 90144
REFERENCES DEPT_JH (DEPT_NO));

ALTER TABLE SALES_JH ADD (
CONSTRAINT SALES_JH_FK3
FOREIGN KEY (PRODUCT_CD) --SALES_TYPE 5 Y VARCHAR2 (4 Byte) Frequency 26
REFERENCES PRODUCTS_JH (PRODUCT_CD));

ALTER TABLE SALES_JH ADD (
CONSTRAINT SALES_JH_FK2
FOREIGN KEY (SALES_TYPE) --SALES_TYPE 5 Y VARCHAR2 (4 Byte) Frequency 26
REFERENCES TYPES_JH (TYPE_CD));

ALTER TABLE SALES_JH ADD (
CONSTRAINT SALES_JH_FK1
FOREIGN KEY (COUNTRY_CD)
REFERENCES COUNTRY_JH (COUNTRY_CD));

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'COUNTRY_JH' );

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'TYPES_JH' );

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'PRODUCTS_JH' );

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'DEPT_JH' );

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'SALES_JH' );

{CODE}
p.611 실행 스크립트 HASH JOIN
{CODE:SQL}

SQL> SELECT * FROM V$VERSION WHERE ROWNUM <= 1;

BANNER

-
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> SELECT /*+ LEADING( D P ) STAR USE_MERGE( D P ) USE_HASH( S ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND P.PRODUCT_NAME IN ( 'NAME_AT080' , 'NAME_CP001' )
10 AND T.TYPE_NAME BETWEEN 'NAME_A' AND 'NAME_Z'
11 AND D.DEPT_NO LIKE '8%'
12 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
NAME_84500 000349 NAME_AT080 NAME_C 71000
NAME_85403 000259 NAME_AT080 NAME_C 306000
NAME_89292 000017 NAME_AT080 NAME_K 49000
NAME_82550 000044 NAME_AT080 NAME_L 170000
NAME_80668 000384 NAME_AT080 NAME_Z 447000
NAME_89112 000152 NAME_AT080 NAME_G 284000
NAME_86295 000424 NAME_CP001 NAME_S 490000
NAME_85235 000397 NAME_CP001 NAME_U 448000
NAME_81543 000247 NAME_AT080 NAME_M 408000

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_85080 000389 NAME_AT080 NAME_M 463000
NAME_82755 000382 NAME_AT080 NAME_P 238000
NAME_84957 000353 NAME_AT080 NAME_Z 328000
NAME_85309 000361 NAME_AT080 NAME_Y 4000
NAME_84937 000401 NAME_AT080 NAME_H 386000
NAME_85019 000457 NAME_CP001 NAME_N 461000
NAME_89567 000035 NAME_AT080 NAME_M 335000
NAME_80957 000379 NAME_AT080 NAME_T 74000
NAME_84640 000392 NAME_CP001 NAME_U 150000
NAME_81760 000354 NAME_CP001 NAME_R 28000

21 개의 행이 선택되었습니다.

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

---

0SELECT STATEMENT12100:00:07.58427K427K
1HASH GROUP BY112100:00:07.58427K427K813K813K1353K (0)
2NESTED LOOPS12100:00:07.58427K427K
3NESTED LOOPS112100:00:07.58427K427K
  • 4
HASH JOIN112100:00:07.58427K427K1861K1192K3041K (0)

PLAN_TABLE_OUTPUT

5MERGE JOIN CARTESIAN1191342000000:00:00.054920
  • 6
TABLE ACCESS FULLDEPT_JH195671000000:00:00.022790
7BUFFER SORT1000022000000:00:00.022130204820482048 (0)
  • 8
TABLE ACCESS FULLPRODUCTS_JH12200:00:00.012130
  • 9
TABLE ACCESS FULLSALES_JH1261K288K00:00:07.42427K427K
  • 10
INDEX UNIQUE SCANTYPES_JH_PK2112100:00:00.0140
  • 11
TABLE ACCESS BY INDEX ROWIDTYPES_JH2112100:00:00.0110

---

Predicate Information (identified by operation id):

---
4 - access("P"."PRODUCT_CD"="S"."PRODUCT_CD" AND "D"."DEPT_NO"="S"."SALES_DEPT")
6 - filter("D"."DEPT_NO" LIKE '8%')
8 - filter(("P"."PRODUCT_NAME"='NAME_AT080' OR "P"."PRODUCT_NAME"='NAME_CP001'))
9 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."SALES_DEPT" LIKE '8%' AND "S"."COUNTRY_CD" IS NOT NULL))
10 - access("T"."TYPE_CD"="S"."SALES_TYPE")
11 - filter(("T"."TYPE_NAME">='NAME_A' AND "T"."TYPE_NAME"<='NAME_Z'))

{CODE}
* (a) 스타조인은 반드시 비용기준( Cost_based ) 옵티마이져 모드에서 수행되어야 한다.
또한 통계 정보를 생성해 주어야 작동 할 수 있다. ( 또한 PK AND FK 꼭필요하다 ( 주관 ) )
원하는 실행계획이 생성되지 않을 때는 /*+ STAR */힌트를 적용한다.
* (b) 디멘저 테이블들이 먼저 조인하여 카티션 곱을 만들어 내는 것을 확인할 수 있다.
이처럼 카티션 곱을 생성하는 대부분의 경우는 Sort Merge 조인 형식으로 나타난다.
* © 카티션 곱을 좀더 효율적으로 생성하기 위해 정렬을 한 집합을 버퍼에 저장해 두는 것을 보여 주고 있다.
* (d) 카티션 곱으로 생성된 집합과 팩트 테이블인 Sales 테이블이 해쉬 조이을 하고 있음을 확인할 수 있다,
그러나 이 단계가 항상 해쉬 조이이 되는 것은 아니다.
어쩌면 이 단계는이미 스타조인의 문제가 아니다. 단지 준비된 두 개의 집합이 가장 효율적인 조인형식을 선택하는 문제가
남아 있을 뿐이다.
* (d)에서 제기된 문제가 바로 앞에서 우리가 해결하기 위해 남겨 두었던 바로 그 두 번재 문제이다.
카티션 곱의 집합은 이미 인덱스를 가질 수 없고, 팩트 테이블은 디멘전 테이블들의 수많은 조합을 모두 감당할 인덱스를 미리 구성하기가 어려우므로
인덱스를 사용하지 않는 해쉬 조인이나 Sort Merge 조인으로 수행하는 것이 바람직한 방법이다.
그럼 머지로 해보져... ( 비슷하군요 )
{CODE:SQL}

SQL> SELECT /*+ LEADING( D P ) STAR USE_MERGE( D P ) USE_MERGE( S ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND P.PRODUCT_NAME IN ( 'NAME_AT080' , 'NAME_CP001' )
10 AND T.TYPE_NAME BETWEEN 'NAME_A' AND 'NAME_Z'
11 AND D.DEPT_NO LIKE '8%'
12 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
NAME_84500 000349 NAME_AT080 NAME_C 71000
NAME_85403 000259 NAME_AT080 NAME_C 306000
NAME_82550 000044 NAME_AT080 NAME_L 170000
NAME_89292 000017 NAME_AT080 NAME_K 49000
NAME_80668 000384 NAME_AT080 NAME_Z 447000
NAME_89112 000152 NAME_AT080 NAME_G 284000
NAME_85235 000397 NAME_CP001 NAME_U 448000
NAME_86295 000424 NAME_CP001 NAME_S 490000
NAME_81543 000247 NAME_AT080 NAME_M 408000

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_82755 000382 NAME_AT080 NAME_P 238000
NAME_85080 000389 NAME_AT080 NAME_M 463000
NAME_84937 000401 NAME_AT080 NAME_H 386000
NAME_84957 000353 NAME_AT080 NAME_Z 328000
NAME_85309 000361 NAME_AT080 NAME_Y 4000
NAME_80957 000379 NAME_AT080 NAME_T 74000
NAME_89567 000035 NAME_AT080 NAME_M 335000
NAME_81760 000354 NAME_CP001 NAME_R 28000
NAME_84640 000392 NAME_CP001 NAME_U 150000
NAME_85019 000457 NAME_CP001 NAME_N 461000

21 개의 행이 선택되었습니다.

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

---

0SELECT STATEMENT12100:00:07.55427K427K
1HASH GROUP BY112100:00:07.55427K427K813K813K1356K (0)
2NESTED LOOPS12100:00:07.55427K427K
3NESTED LOOPS112100:00:07.55427K427K
4MERGE JOIN112100:00:07.55427K427K
5SORT JOIN1191342000000:00:00.0749201328K587K1180K (0)
6MERGE JOIN CARTESIAN1191342000000:00:00.054920
  • 7
TABLE ACCESS FULLDEPT_JH195671000000:00:00.022790
8BUFFER SORT1000022000000:00:00.022130204820482048 (0)
  • 9
TABLE ACCESS FULLPRODUCTS_JH12200:00:00.012130
  • 10
SORT JOIN20000261K2100:00:07.48427K427K15M1472K13M (0)
  • 11
TABLE ACCESS FULLSALES_JH1261K288K00:00:07.34427K427K
  • 12
INDEX UNIQUE SCANTYPES_JH_PK2112100:00:00.0140
  • 13
TABLE ACCESS BY INDEX ROWIDTYPES_JH2112100:00:00.0110

---

Predicate Information (identified by operation id):

---
7 - filter("D"."DEPT_NO" LIKE '8%')
9 - filter(("P"."PRODUCT_NAME"='NAME_AT080' OR "P"."PRODUCT_NAME"='NAME_CP001'))
10 - access("P"."PRODUCT_CD"="S"."PRODUCT_CD" AND "D"."DEPT_NO"="S"."SALES_DEPT")
filter(("D"."DEPT_NO"="S"."SALES_DEPT" AND "P"."PRODUCT_CD"="S"."PRODUCT_CD"))
11 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."SALES_DEPT" LIKE '8%' AND "S"."COUNTRY_CD" IS NOT NULL))
12 - access("T"."TYPE_CD"="S"."SALES_TYPE")
13 - filter(("T"."TYPE_NAME">='NAME_A' AND "T"."TYPE_NAME"<='NAME_Z'))

{CODE}

우리가 매우 주의해야 할 사항이 한 가지 있다. 우선 다음의 실행계획을 살펴보기로 하자.

p.613 실행 스크립트 NL JOIN
{CODE:SQL}

SQL> SELECT /*+ LEADING( D P T ) STAR USE_MERGE( D P T ) USE_NL( S ) FULL( D ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND P.PRODUCT_NAME IN ( 'NAME_AT080' , 'NAME_CP001' )
10 AND T.TYPE_NAME BETWEEN 'NAME_A' AND 'NAME_Z'
11 AND D.DEPT_NO LIKE '8%'
12 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
NAME_85403 000259 NAME_AT080 NAME_C 306000
NAME_84500 000349 NAME_AT080 NAME_C 71000
NAME_82550 000044 NAME_AT080 NAME_L 170000
NAME_89292 000017 NAME_AT080 NAME_K 49000
NAME_80668 000384 NAME_AT080 NAME_Z 447000
NAME_89112 000152 NAME_AT080 NAME_G 284000
NAME_86295 000424 NAME_CP001 NAME_S 490000
NAME_85235 000397 NAME_CP001 NAME_U 448000
NAME_85080 000389 NAME_AT080 NAME_M 463000

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_81543 000247 NAME_AT080 NAME_M 408000
NAME_82755 000382 NAME_AT080 NAME_P 238000
NAME_84937 000401 NAME_AT080 NAME_H 386000
NAME_85309 000361 NAME_AT080 NAME_Y 4000
NAME_84957 000353 NAME_AT080 NAME_Z 328000
NAME_84640 000392 NAME_CP001 NAME_U 150000
NAME_85019 000457 NAME_CP001 NAME_N 461000
NAME_81760 000354 NAME_CP001 NAME_R 28000
NAME_80957 000379 NAME_AT080 NAME_T 74000
NAME_89567 000035 NAME_AT080 NAME_M 335000

21 개의 행이 선택되었습니다.

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

0SELECT STATEMENT12100:00:01.88129K43
1HASH GROUP BY112100:00:01.88129K43813K813K1353K (0)
2NESTED LOOPS12100:00:01.88129K43
3NESTED LOOPS11439400:00:01.87128K43
4MERGE JOIN CARTESIAN1497K520K00:00:00.404951
5MERGE JOIN CARTESIAN1191342000000:00:00.084921
  • 6
TABLE ACCESS FULLDEPT_JH195671000000:00:00.042791
7BUFFER SORT1000022000000:00:00.022130204820482048 (0)
  • 8
TABLE ACCESS FULLPRODUCTS_JH12200:00:00.012130
9BUFFER SORT2000026520K00:00:00.1630204820482048 (0)
  • 10
TABLE ACCESS FULLTYPES_JH1262600:00:00.0130
  • 11
INDEX RANGE SCANSALES_JH_INDEX01520K1439400:00:01.25128K42
  • 12
TABLE ACCESS BY INDEX ROWIDSALES_JH439412100:00:00.012190

Predicate Information (identified by operation id):

---
6 - filter("D"."DEPT_NO" LIKE '8%')
8 - filter(("P"."PRODUCT_NAME"='NAME_AT080' OR "P"."PRODUCT_NAME"='NAME_CP001'))
10 - filter(("T"."TYPE_NAME">='NAME_A' AND "T"."TYPE_NAME"<='NAME_Z'))
11 - access("D"."DEPT_NO"="S"."SALES_DEPT" AND "P"."PRODUCT_CD"="S"."PRODUCT_CD")
filter("S"."SALES_DEPT" LIKE '8%')
12 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."COUNTRY_CD" IS NOT NULL AND "T"."TYPE_CD"="S"."SALES_TYPE"))

{CODE}
* 카티션 집합과 팩트 테이블이 NL 조인으로 풀렸다.
* SALES_JH_INDEX01이 'DEPT_NO + PRODUCT_CD'( 순서는 무관 )로 구성 되어 있다면 문제가 없다.
* 만약 인덱스가 'DEPT_NO + SALES_TYPE + PRODUCT_CD'로 구성되어 있다면 상황은 달라진다.
중간에 있는 SALES_TYPE이 상수값을 제공받지 못한 상태에서 인덱스가 사용된다는 것은 결합 인덱스의 원리에 의해서 비효율이 발생하게 된다.
옵티마이젼느 이러한 인덱스를 구성일 때도 NL 조인으로 실행계획을 수립하는 경향이 많이 있다.
* 이처럼 전략적이지 못한 인덱스가 존재한다면 스타조인이 NL 형식으로 처리될 때 문제가 발생하는 경우가 많으므로
가장 우선적으로 해야할 일은 전략적인 인덱스를 구성하는 것이며,
그 다음은 옵티마이져가 수립한 실행계획을 확인할 필요가 있다는 점을 명심하길 바란다.

왜 디멘저 테이블을 FS 으로 풀었냐구요? 조건절 이행으로 인한 펙터 테이블이 선두 테이블로 되기 때문에...

조건절 이행인지 아닌지 확인 테스트
{CODE:SQL}

CREATE UNIQUE INDEX DEPT_JH_INDEX01 ON DEPT_JH ( TRIM( DEPT_NO ) ) TABLESPACE ORA03TS02

CREATE UNIQUE INDEX PRODUCTS_JH_INDEX01 ON PRODUCTS_JH ( TRIM( PRODUCT_CD ) ) TABLESPACE ORA03TS02

CREATE UNIQUE INDEX TYPES_JH_INDEX01 ON TYPES_JH ( TRIM( TYPE_CD ) ) TABLESPACE ORA03TS02

SQL> SELECT /*+ LEADING( D P T ) STAR USE_MERGE( D P T ) USE_NL( S ) INDEX( T TYPES_JH_INDEX01 ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND TRIM( P.PRODUCT_CD ) IN ( 'AT080' , 'CP001' )
10 AND TRIM( T.TYPE_CD ) BETWEEN 'A' AND 'Z'
11 AND TRIM( D.DEPT_NO) LIKE '8%'
12 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
NAME_84500 000349 NAME_AT080 NAME_C 71000
NAME_85403 000259 NAME_AT080 NAME_C 306000
NAME_82550 000044 NAME_AT080 NAME_L 170000
NAME_89292 000017 NAME_AT080 NAME_K 49000
NAME_80668 000384 NAME_AT080 NAME_Z 447000
NAME_89112 000152 NAME_AT080 NAME_G 284000
NAME_85235 000397 NAME_CP001 NAME_U 448000
NAME_86295 000424 NAME_CP001 NAME_S 490000
NAME_81543 000247 NAME_AT080 NAME_M 408000

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_82755 000382 NAME_AT080 NAME_P 238000
NAME_85080 000389 NAME_AT080 NAME_M 463000
NAME_84937 000401 NAME_AT080 NAME_H 386000
NAME_84957 000353 NAME_AT080 NAME_Z 328000
NAME_85309 000361 NAME_AT080 NAME_Y 4000
NAME_80957 000379 NAME_AT080 NAME_T 74000
NAME_81760 000354 NAME_CP001 NAME_R 28000
NAME_84640 000392 NAME_CP001 NAME_U 150000
NAME_85019 000457 NAME_CP001 NAME_N 461000
NAME_89567 000035 NAME_AT080 NAME_M 335000

21 개의 행이 선택되었습니다.

SQL> @XPLAN

– TFS 더 효과적이군요 ㅎ

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

0SELECT STATEMENT12100:00:01.14137K
1HASH GROUP BY132100:00:01.14137K813K813K1326K (0)
2NESTED LOOPS12100:00:01.14137K
3NESTED LOOPS13439400:00:01.14137K
4MERGE JOIN CARTESIAN1199K520K00:00:00.289086
5MERGE JOIN CARTESIAN13072K2000000:00:00.049084
6TABLE ACCESS BY INDEX ROWIDDEPT_JH145001000000:00:00.029078
  • 7
INDEX RANGE SCANDEPT_JH_INDEX0118101000000:00:00.0124
8BUFFER SORT100006832000000:00:00.016204820482048 (0)
9INLIST ITERATOR1200:00:00.016
10TABLE ACCESS BY INDEX ROWIDPRODUCTS_JH2683200:00:00.016
  • 11
INDEX UNIQUE SCANPRODUCTS_JH_INDEX012273200:00:00.014
12BUFFER SORT200001520K00:00:00.122204820482048 (0)
13TABLE ACCESS BY INDEX ROWIDTYPES_JH112600:00:00.012
  • 14
INDEX RANGE SCANTYPES_JH_INDEX01112600:00:00.011
  • 15
INDEX RANGE SCANSALES_JH_INDEX01520K1439400:00:00.69128K
  • 16
TABLE ACCESS BY INDEX ROWIDSALES_JH439412100:00:00.01219

---

Predicate Information (identified by operation id):

---
7 - access("D"."SYS_NC00003$" LIKE '8%')
filter("D"."SYS_NC00003$" LIKE '8%')
11 - access(("P"."SYS_NC00003$"='AT080' OR "P"."SYS_NC00003$"='CP001'))
14 - access("T"."SYS_NC00003$">='A' AND "T"."SYS_NC00003$"<='Z')
15 - access("D"."DEPT_NO"="S"."SALES_DEPT" AND "P"."PRODUCT_CD"="S"."PRODUCT_CD")
16 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."COUNTRY_CD" IS NOT NULL AND "T"."TYPE_CD"="S"."SALES_TYPE"))

{CODE}

만약 힌트가 제대로 작동하지 않는다거나 이런한 힌트를 가지고 있지 않는 DBMS를 사용하고 있다면 억지로라도 이러한 실행계획이 나타나도록 하는 방법은.?

p. 613 실행 스크립트
{CODE:SQL}

SQL> SELECT /*+ USE_HASH( D F ) */ COUNTRY_NAME, PRODUCT_NAME, SALES_TYPE --D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( F.SALES_AMOUNT )
3 FROM (SELECT ROWNUM, PRODUCT_CD, PRODUCT_NAME, COUNTRY_CD, COUNTRY_NAME
4 FROM PRODUCTS_JH, COUNTRY_JH
5 WHERE PRODUCT_CD IN ( 'AT080' , 'CP001' ) ) D, SALES_JH F
6 WHERE F.PRODUCT_CD = D.PRODUCT_CD
7 AND F.COUNTRY_CD = D.COUNTRY_CD
8 AND F.SALES_DATE LIKE '201104%'
9 GROUP BY COUNTRY_NAME, PRODUCT_NAME, SALES_TYPE ;

COUNTRY_NAME PRODUCT_NAME SALES_TY SUM(F.SALES_AMOU

--
--

NAME_99 NAME_CP001 W 334
NAME_436 NAME_AT080 D 375
...

90 개의 행이 선택되었습니다.

SQL> @XPLAN

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

--

0SELECT STATEMENT19000:00:07.89427K427K
- (a)		1HASH GROUP BY1699000:00:07.89427K427K849K849K1326K (0)
  • 2
HASH JOIN1699100:00:07.89427K427K904K904K1254K (0)
- (d)		3VIEW11000100000:00:00.03106
4COUNT1100000:00:00.03106
- ©		5MERGE JOIN CARTESIAN11000100000:00:00.03106
- (b)		6INLIST ITERATOR1200:00:00.0163
7TABLE ACCESS BY INDEX ROWIDPRODUCTS_JH22200:00:00.0163
  • 8
INDEX UNIQUE SCANPRODUCTS_JH_PK22200:00:00.0143
9BUFFER SORT2500100000:00:00.0143276482764824576 (0)
10TABLE ACCESS FULLCOUNTRY_JH150050000:00:00.0143
  • 11
TABLE ACCESS FULLSALES_JH12369K2591K00:00:06.99427K427K

--

Predicate Information (identified by operation id):

---
2 - access("F"."PRODUCT_CD"="D"."PRODUCT_CD" AND "F"."COUNTRY_CD"="D"."COUNTRY_CD")
8 - access(("PRODUCT_CD"='AT080' OR "PRODUCT_CD"='CP001'))
11 - filter("F"."SALES_DATE" LIKE '201104%')

{CODE}
* (a) SQL이 비용기준으로 정의 되어 있지 않은 것을 보여주고 있다.. ( ㅡㅡ^ )
위 실행계획은 스타조인에 의해 만드어진 것이 아님을 의미하고 있다.
* (b) 인라인뷰를 사용하여 강제로 디메전 테이블이 먼저 조인되도록 하여 카티션이 나타났다.
* © 'COUNT'는 사실 없어도 무관한 단계이기는 하지만 위의 SQL에 있는 인라인뷰의 'ROWNUM' 때문에 생겨난 것이다. ( NO_MERGE 힌트와 같은 효과 )
ROWNUM이 가시는 가상속성 때문에 병합이 방지하는 효과를 가져오게 된다.
물론 힌트만 제대로 작동된다는 보장만 있다면 'NO_MERGE'힌트를 사용하여 동일한 효과를 낼 수가 있다.
* (d) 해쉬조인 힌트를 사용하여 카티션곱과 팩트 테이블의 최적의 조인을 하도록 유도하였다.

스타 조인은 단점 2가지.?

  • 디멘전의 카티션 곱이 너무 클 때는 사용해서는 안된다
  • 최종적으로 연결해야 할 팩트 테이블과의 조인 방식에 유의해야 한다.
    해쉬 조인 : NL 조인처럼 선행집합의 처리 결과를 완벽하게 자기 집합의 처리범위를 줄일 수는 없다는데 한계가 있다.
    NL 조인 : 완벽한 결합 인덱스가 없다면 무용지밀이 된다.(이를 대비하기 위한 모든 사용형태에 대한 결합 인덱스를 만들어 줄수 없다면 아직 이러한 방법은 최적이 아니다.

2.3.7 스타 변형 조인.?

  • 스타변형 조인은 스타조인의 일부 단점을 개선한 조인이다. ( 스타조인을 완전히 대체하는 개념이 아니라는 것을 먼저 밝혀둔다 )
  • 스타조인을 필요없게 만드는 것이 아니라 스타 조인이 갖는 단점을 개선할 수 있다는 것에 의미를 두기 바란다.
  • 비트맵 인덱스의 특성을 살린 것이다. 따라서 비트맵 인덱스의 장.단점을 그대로 승계하고 있다.
  • 스타조인은 카티젼 곱을 만들지만 스타변형조인은 비트맵 인덱스를 활용한다.
  • 비트맵 인덱스는 액세스 형태에 따라 중간에 사용되지 않는 컬럼이 발생하지 않도록 다양한 형태의 결합인덱스를 구성해야하는 B-Tree 인덱스와는 달리
    각가의 독립적인 비트맵 인덱스가 액세스 형태에 따라 그때마다 머지하는 방식으로 처리되더라도 수행속다가 나빠지지 않는다.

인덱스 머지의 장점.?

  • 1) 수많은 결합 인덱스를 가지지 않도록 할 수 있다.
  • 2) 팩트 테이블을 연결하기전에 먼저 처리되어 팩트 테이블의 처리범위를 공동으로 줄여 줄 수 있다는 것이다.
p. 618 실습 스크립트
{CODE:SQL}

CREATE INDEX SALES_JH_INDEX02 ON SALES_JH ( PRODUCT_CD ) TABLESPACE ORA03TS02

SQL> SELECT /*+ INDEX_COMBINE(A A( PRODUCT_CD ) A( SALES_DEPT ) ) */ COUNT( * )
2 FROM SALES_JH A
3 WHERE PRODUCT_CD LIKE 'PA%'
4 AND SALES_DEPT LIKE '456%'
5 ;

COUNT(*)

--
70

SQL> @XPLAN

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReads

--

0SELECT STATEMENT1100:00:00.01180179
1SORT AGGREGATE11100:00:00.01180179
  • 2
INDEX RANGE SCANSALES_JH_INDEX01117000:00:00.01180179

-

Predicate Information (identified by operation id):

---

2 - access("SALES_DEPT" LIKE '456%' AND "PRODUCT_CD" LIKE 'PA%')
filter(("PRODUCT_CD" LIKE 'PA%' AND "SALES_DEPT" LIKE '456%'))

SQL> @hidden_param _b_tree_bitmap_plans
Enter value for v_par: _b_tree_bitmap_plans
old 8: AND LOWER(A.KSPPINM) LIKE '%'|| TRIM(LOWER('&v_par')) || '%'
new 8: AND LOWER(A.KSPPINM) LIKE '%'|| TRIM(LOWER('_b_tree_bitmap_plans')) || '%'

NAME


VALUE

DEF_YN
-
DESCRIPTION

_b_tree_bitmap_plans
TRUE
TRUE
enable the use of bitmap plans for tables w. only B-tree indexes

DROP INDEX SALES_JH_INDEX01

CREATE INDEX SALES_JH_INDEX01 ON SALES_JH ( SALES_DEPT ) TABLESPACE ORA03TS02

SQL> SELECT /*+ INDEX_COMBINE(A A( PRODUCT_CD ) A( SALES_DEPT ) ) */ COUNT( * )
2 FROM SALES_JH A
3 WHERE PRODUCT_CD LIKE 'PA%'
4 AND SALES_DEPT LIKE '456%'
5 ;

COUNT(*)

--
70

SQL> @XPLAN

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

--

0SELECT STATEMENT1100:00:00.16313309
1SORT AGGREGATE11100:00:00.16313309
  • 2
VIEWindex$_join$_001117000:00:00.16313309
  • 3
HASH JOIN17000:00:00.163133093755K1398K5578K (0)
  • 4
INDEX RANGE SCANSALES_JH_INDEX02117437800:00:00.05179176
  • 5
INDEX RANGE SCANSALES_JH_INDEX01115526700:00:00.02134133

--

Predicate Information (identified by operation id):

---

2 - filter(("PRODUCT_CD" LIKE 'PA%' AND "SALES_DEPT" LIKE '456%'))
3 - access(ROWID=ROWID)
4 - access("PRODUCT_CD" LIKE 'PA%')
5 - access("SALES_DEPT" LIKE '456%')

SQL> SELECT /*+ INDEX_COMBINE(A ) */ COUNT( * )
2 FROM SALES_JH A
3 WHERE PRODUCT_CD LIKE 'PA%'
4 AND SALES_DEPT LIKE '456%'
5 ;

COUNT(*)

--
70

SQL> @XPLAN

--

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

--

0SELECT STATEMENT1100:00:00.21313
1SORT AGGREGATE11100:00:00.21313
2BITMAP CONVERSION COUNT11100:00:00.21313
3BITMAP AND1100:00:00.20313<---4BITMAP CONVERSION FROM ROWIDS1700:00:00.11179
5SORT ORDER BY17437800:00:00.091792320K704K2062K (0)
  • 6
INDEX RANGE SCANSALES_JH_INDEX0217437800:00:00.04179
7BITMAP CONVERSION FROM ROWIDS1500:00:00.08134
8SORT ORDER BY15526700:00:00.071341824K650K1621K (0)
  • 9
INDEX RANGE SCANSALES_JH_INDEX0115526700:00:00.03134

--

Predicate Information (identified by operation id):

---

6 - access("PRODUCT_CD" LIKE 'PA%')
filter(("PRODUCT_CD" LIKE 'PA%' AND "PRODUCT_CD" LIKE 'PA%'))
9 - access("SALES_DEPT" LIKE '456%')
filter(("SALES_DEPT" LIKE '456%' AND "SALES_DEPT" LIKE '456%'))

{CODE}
* 각각을 서로 결합( BITMAP AND )하여 테이블을 액세스한다. 우리가 너무나 잘 알고 있는 비트맵 인덱스의 기본형이다.
* 부여 받을 상수값이 다른 테이블에 존재할 경우 이를 서브쿼리를 통해서 제공할 수 있다고 생각하라는 것이다.
모든 서브쿼리가 먼저 수행되어 그 결과를 메인 쿼리의 비트맵 인덱스에게 제공하여 각각 비트맵을 액세스한 후에 이들을 'BITMAP AND' 연산의 가장 이상적인 실행계획
{CODE:SQL}

SQL> SELECT /*+ INDEX_COMBINE( A ) STAR_TRANSFORMATION */ COUNT( * )
2 FROM SALES_JH A
3 WHERE PRODUCT_CD IN ( SELECT /*+ */PRODUCT_CD
4 FROM PRODUCTS_JH
5 WHERE TRIM( PRODUCT_CD ) LIKE 'PA%' )
6 AND SALES_DEPT IN ( SELECT /*+ */ DEPT_NO
7 FROM DEPT_JH
8 WHERE TRIM( DEPT_NO ) LIKE '456%' )
9 ;

COUNT(*)

--
70

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

0SELECT STATEMENT1100:00:00.67814135
1SORT AGGREGATE11100:00:00.67814135
2BITMAP CONVERSION COUNT1124K100:00:00.67814135
3BITMAP AND1100:00:00.67814135
4BITMAP MERGE1700:00:00.35386721024K512K
5BITMAP KEY ITERATION110100:00:00.1238672
6TABLE ACCESS BY INDEX ROWIDPRODUCTS_JH1341410100:00:00.0244
-- (a)
  • 7
INDEX RANGE SCANPRODUCTS_JH_INDEX01161410100:00:00.0133
8BITMAP CONVERSION FROM ROWIDS10110100:00:00.1038268
---(b)
  • 9
INDEX RANGE SCANSALES_JH_INDEX021017437800:00:00.0838268
10BITMAP MERGE1500:00:00.31428631024K512K254K (0)
11BITMAP KEY ITERATION110000:00:00.1042863
12TABLE ACCESS BY INDEX ROWIDDEPT_JH1450010000:00:00.069312
  • 13
INDEX RANGE SCANDEPT_JH_INDEX01181010000:00:00.0120
14BITMAP CONVERSION FROM ROWIDS10010000:00:00.0433551
  • 15
INDEX RANGE SCANSALES_JH_INDEX011005526700:00:00.0233551

Predicate Information (identified by operation id):

---

7 - access("PRODUCTS_JH"."SYS_NC00003$" LIKE 'PA%')
filter("PRODUCTS_JH"."SYS_NC00003$" LIKE 'PA%')
9 - access("PRODUCT_CD"="PRODUCT_CD")
13 - access("DEPT_JH"."SYS_NC00003$" LIKE '456%')
filter("DEPT_JH"."SYS_NC00003$" LIKE '456%')
15 - access("SALES_DEPT"="DEPT_NO")

{CODE}
* 이전 쿠리랑 동일 하고 비트맵 머지( BITMAP MERGE )를 하는 것만 추가되어 있다.
* (a) 서브쿼리 테이블을 액세서( 머전 인덱스 부터 )하여 그 결과값으로 메인쿼리의 비트리 인덱스
(b)를 엑세스( BITMAP KEY ITERATION )하여 BITMAP CONVERSION 해서 임시 비트맵으로 머지( BITMAP MERGE )해 두고 있다.
* 스타변형조인의 기본 원리이다.
p. 621 실행 스크립트
{CODE:SQL}

SQL> SELECT /*+ LEADING( S ) STAR_TRANSFORMATION INDEX_COMBINE( S ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND S.PRODUCT_CD IN ( SELECT PRODUCT_CD FROM PRODUCTS_JH WHERE TRIM( PRODUCT_CD ) IN ( 'AT080' , 'CP001' ))
10 AND ( S.SALES_TYPE ) IN (SELECT TYPE_CD FROM TYPES_JH WHERE TRIM( TYPE_CD ) BETWEEN 'A' AND 'Z' )
11 AND S.SALES_DEPT IN ( SELECT DEPT_NO FROM DEPT_JH WHERE TRIM( DEPT_NO ) LIKE '8%' )
12 -- AND ROWNUM <= 5
13 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
...

21 개의 행이 선택되었습니다.

SQL>
SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsOMem1MemUsed-Mem

0SELECT STATEMENT12100:01:16.81134K102K
1HASH GROUP BY1492100:01:16.81134K102K813K813K1356K (0)
  • 2
HASH JOIN1492100:01:16.81134K102K889K889K1263K (0)
  • 3
HASH JOIN1492100:01:16.77133K102K915K915K582K (0)
  • 4
HASH JOIN1492100:01:16.74133K102K947K947K1181K (0)
  • 5
TABLE ACCESS BY INDEX ROWIDSALES_JH1492100:01:16.72133K102K
6BITMAP CONVERSION TO ROWIDS116900:01:16.72133K102K
7BITMAP AND1100:01:16.72133K102K
8BITMAP MERGE1100:00:00.091601024K512K7168 (0)
9BITMAP KEY ITERATION1200:00:00.01160
10INLIST ITERATOR1200:00:00.0160
11TABLE ACCESS BY INDEX ROWIDPRODUCTS_JH2683200:00:00.0160
  • 12
INDEX UNIQUE SCANPRODUCTS_JH_INDEX012273200:00:00.0140
13BITMAP CONVERSION FROM ROWIDS2200:00:00.01100
  • 14
INDEX RANGE SCANSALES_JH_INDEX022146300:00:00.01100
15BITMAP MERGE121100:01:11.3390635904161024K512K86M (0)
16BITMAP KEY ITERATION1267900:00:16.689063590416
17TABLE ACCESS BY INDEX ROWIDTYPES_JH112600:00:00.0121
  • 18
INDEX RANGE SCANTYPES_JH_INDEX01112600:00:00.0111
19BITMAP CONVERSION FROM ROWIDS26267900:00:16.689063390415
  • 20
INDEX RANGE SCANSALES_JH_INDEX032650M00:00:09.399063390415
21BITMAP MERGE117300:00:05.2842693119291024K512K25M (0)
22BITMAP KEY ITERATION11000000:00:02.014269311929
23TABLE ACCESS BY INDEX ROWIDDEPT_JH145001000000:00:00.05907830
  • 24
INDEX RANGE SCANDEPT_JH_INDEX0118101000000:00:00.022424
25BITMAP CONVERSION FROM ROWIDS100001000000:00:01.953361511899
  • 26
INDEX RANGE SCANSALES_JH_INDEX01100005552K00:00:01.153361511899
27TABLE ACCESS FULLTYPES_JH1262600:00:00.0131
28TABLE ACCESS FULLPRODUCTS_JH1682766827600:00:00.01213208
29TABLE ACCESS FULLDEPT_JH1900009000000:00:00.012793

Predicate Information (identified by operation id):

---

2 - access("D"."DEPT_NO"="S"."SALES_DEPT")
3 - access("P"."PRODUCT_CD"="S"."PRODUCT_CD")
4 - access("T"."TYPE_CD"="S"."SALES_TYPE")
5 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."COUNTRY_CD" IS NOT NULL))
12 - access(("PRODUCTS_JH"."SYS_NC00003$"='AT080' OR "PRODUCTS_JH"."SYS_NC00003$"='CP001'))
14 - access("S"."PRODUCT_CD"="PRODUCT_CD")
18 - access("TYPES_JH"."SYS_NC00003$">='A' AND "TYPES_JH"."SYS_NC00003$"<='Z')
20 - access("S"."SALES_TYPE"="TYPE_CD")
24 - access("DEPT_JH"."SYS_NC00003$" LIKE '8%')
filter("DEPT_JH"."SYS_NC00003$" LIKE '8%')
26 - access("S"."SALES_DEPT"="DEPT_NO")

{CODE}
* 비트리로 구성된 SALES_JH_INDEX02가 BITMAP CONVERSION으로 변화하여 'BITMAP AND'를 수행한다는 것을 알 수 있다.
* 이 조인 방식은 디멘전 테이블에 주어진 조건들을 최대한 이용하였을 때 팩트 테이블의 처리범위가 현격하게 줄어든다면 비로소 빛을 발한다는 것을 의미한다.
* 만약 여러개의 디멘저 테이블들이 각각 서브쿼리를 통해 팩트 테이블의 비트맵을 액세스( BITMAP KEY ITERATION )하였으나 거의 감소하지 않았다고 하자
그렇다면 이들은 아무리 BITMAP AND 를 하더라도 그 결과는 매우 넓은 범위가 될 것이다.
더구나 이를 ROWID로 변환하여 일일이 팩트 테이블을 액세스 한다면 애써 많은 처리를 해 왔지만 정작 효과는 전혀 없다.
게다가 아직 조인해야 할 디멘저 테이블들이 그대로 남아 있으므로 우리가 얻은 것은 아무 것도 없다.
그 반대의 경우에는 최상의 실행 계획이라 할 수있다 ( 디멘전 처리 범위가 넓은 경우 = 스타 조인, 디멘전의 처리 범위가 작을 경우
* 그 결고 ROWID로 변환하여 팩트 테이블을 액세스 한다.
* SALES_JH 검색 범위가 넓어 부하가 상당하다.

스타 조인과 스타 변형 조인은 어떤경우에 유용한가.? ( 주관 )

  • 스타 조인 : 1) 디멘전 테이블들의 처리 범위 가 넓은 경우
    2) 최소의 인덱스 정책 수립만으로 대응이 가능할 경우
  • 스타 변형 조인 : 1) 디멘전 테이블들의 처리 범위가 작은 경우
    2) 크리티걸한 인덱스 정책 수립이 필요한 경우

그러나 아니란다.;;

  • 실전에서 적용되는 경우를 보면 스타 변형 조인이 훨씬 가치가 있고, 자주 사용되고 있다. ( DW 주로 사용 )
  • 팩트 테이블에는 디멘전 컬럼과 계측값을 가진 메져( Measure ) 컬럼으로 구성되고 있다.
  • 디멘전 컬럼은 디멘전 테이블들의 외부키로써 존재한다.
  • 외부에서 주어지는 조건들은 디멘전 컬럼에 직접 주어지거나 디멘전 테이블의 컬럼에 주어진다.
  • 실전에서 발생하는 대부분의 이러한 엑세스등른 주어진 조건들을 모두 적용하면 팩트 테이블의 처리범위가 크게 감소하는 경우가 많다.
  • 그러므로 현질적으로는 스타변형 조인이 DW에서 일반적으로 훨씬 유리하다.

스타 변형 조인의 필수적인 전제 조건

  • 1) 하나의 팩트 테이블과 최소한 2개 이상의 디멘전 테이블이 있어야 한다.
  • 2) 팩트 테이블에 있는 디멘전 - 즉, 디멘전 테이블들의 외부키 - 에는 반드시 비트맵 인덱스가
    존재해야 한다. 그러나 비트리 인덱스가 있어도 비트맵 컨버전이 일어나므로 스타변형조인이
    발생할 수 있다. 그러나 복합( Composite ) 비트맵 인덱스 상에서 발생할 때는 사용상의 주의가 필요하다.
  • 3) 팩트 테이블에 반드시 통계정보가 생성되어 있어야 한다. 그러나 ANALYZE 모드에는 영향을 받지 않는다.
  • 4) STAR_TRANSFORMATION_ENABLED 파라미터가 FALSE가 아닌 TRUE 나 TEMP_DISABLE로 설정되어 있거나.
    아니면 쿼리에 직접 STAR_TRANSFORMATION 힌트를 주어야 작동한다.

스타 변형 조인의 제약 조건

  • 1) 비트맵을 사용할 수 없게 하는 힌트와는 서로 양립할 수 없다. 가령, FULL, ROWID, STAR와 같은 힌트는 논리적으로 서로 공존할 수 없기 때문이다.
  • 2) 쿼리 내에 바인드 변수를 사용하지 않아야 한다. 어떤 경우의 바인드 변수의 사용도 스타 변형 조인을 발생시키지 않는다.
    스타 변형 조인을 위한 WHERE절의 바인드 변수 뿐만 아니라, 이와 상관없는 WHERE 절에 바인드 변수가 있어도 스타 변형 조인은 일어 나지 않는다.
    이러한 현상이 발생하는 이유에 대해서는 뒤에서 별도로 설명하기로 하겠다.
  • 3) 원격( Remote ) 팩터 테이블인 경우에는 스타 변형 조인이 일어나지 않는다. 그 이유는 각 서브쿼리마다 원격에 있는 팩트 테이블을 비트맵 탐침( BITMAP KEY ITERATION )하고
    최종적으로 합성 ( BITMAP AND) 된 결과로 다시 원격 테이블을 액세스 해야 하므로 오히려 부하가 크게 증가하기 때문이다.
  • 4) 그러나 디멘저 테이블이 원격에 있으면 이 조인은 일어날 수 없다.
    디멘전 테이블은 일반적으로 소량이며, 서브쿼리에서 먼저 수행하거나 마지막으로 해쉬조인 드응로 조인할 때 사용되므로 그리 큰 부담이 되지 않기 때문이다.
  • 5) 부정형 조인으로 수행되는 경우에는 이 조인이 발생하지 않는다.
    우리가 앞서 부정형 조인에서 그 특성을 알아보았듯이 부정형 조인은 제공자 역할이 아닌 확인자의 역할만을 담당하므로 스타변형 조인으로 수행되더라도 전혀 얻을 것이 없기 때문이다.
  • 6) 인라인뷰나 뷰 쿼리 중에는 독립적으로 먼저 수행될 수 있는 경우와 액세스 쿼리와 머지 한 후에 수행될 수 있는 두 가지 종류가 있다.
    후자의 경우는 이 조인이 일어날 수 없다.
    그 이유는 논리적으로 보더라도 아직 머지가 일어나지 않은 쿼리르 대상으로 스타변형 조인을 위한 질의 재생성을 할수는 없기 때문이다.
  • 7) 서브쿼리에서 이미 디멘전 테이블로 사용한 테이블에 대해서는 이런 방식의 조인을 위한 변형이 일어나지 않는다.
    물론 여기서 말하는 서브쿼리는 변형에 의해 생성된 서브쿼리가 아니라 사용자 쿼리에 직접 기술한 것ㅇ르 말한다.
    만약 변형이 일어난다면 이중으로 생기게 되므로 이러한 제약이 있는 것은 당연하다.

옵티마이져의 판단에 의해 스타변형 조인이 일어 나지 않는 경우.? ;;

  • 1) 팩트 테이블이 가진 조건들 만으로도 충분히 처리범위가 주어든다고 판단하였거나 특정 디멘전 테이블이 선행해서 제공자 역하을 한 것만으로도 충분한 경우에는 스타변형조인은 일어나지않는다.
  • 2) 팩트 테이블의 크기가 너무 작아서 굳이 스타변형 조인을 수행시킬 가치가 없다고 판단한 경우에도 이 조인은 일어나지 않는다.
  • 3) 인덱스 머지에서도 나타났듯이 '머지'

바인드 변수는 상수값 아니므로 옵티마이져가 정확한 판단을 할 수 없어 효과를 보장 할 수 없기 때문에 아예 포기하는 것이다. ( 필자님 고찰 )

  • 1) 실전에서 일어나는 대부분의 경우는 평균적ㅇ니 분포도를 기준으로 결정했을 때도 크나큰 문제가 없으며,
    실전에서 사용되는 대다수의 쿼리에는 바인드 변수가 포함되어 있기 때문에 이러한 경우를 모두 제외해 보리면 이 조인을 활용할 기회가 너무 적어지기 때문에 바람직하지 않다.
  • 2) 정 부담되면 변수를 사용한 조건이 포함된 부분만 변형에 포함시키지 않는다거나 퀄의 사용자가 힌트를 통해 반드시 적용하겠다고 의지를 보였다면
    그 책임은 옵티마져에게 있는 것이 아니므로 이를 허용하는 것이 바람직하다고 믿는다. 이점은 점차 개선될 것이다 ( ㅡㅡ^ )
  • 3) 비트맵 합성에서도 어느 한쪽이 지나치게 넓은 범위를 가지고 있다면 이를 합성 대상에서 제외 시킨다.
    물론 항상 제외되는 것은 아니다 좁은 범위를 가지고 있는 다른디멘저들이 충분히 그 역할을 담당한다는 판단이 서면 제외시키지만 그렇지 않을 때는 대상에 포함시킨다.
    실제로 테스트를 해보면 등장한 디멘전 테이블들의 수에 따라 선택된 디멘전이나 개수에 미묘한 차이를 보이고 있다.
    그러나 여기에는 굳이 테이스트 결과를 공개하지 않겠다.
    이것은 어차피 옵티마이져가 판단할 문제이므로 여러분이 너무 깊순한 부분까지 알면 오리혀 혼란만 가중될 수도 있을 거이라는 생각이기 때문이다 ( ㅡㅡ^ )
변칙적이지만 이런 생각 하시는 분이 있을것입니다. 굳이 서브쿼리들이 필요한가.? 네 필요합니다. 책에서는 디멘전 테이블이 중앙 테이블을 제어하기 때문입니다. 하지만 TEST ㄱㄱ
{CODE:SQL}

SQL> SELECT /*+ LEADING( S ) STAR_TRANSFORMATION INDEX_COMBINE( S ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND S.PRODUCT_CD IN ( SELECT PRODUCT_CD FROM PRODUCTS_JH WHERE ( PRODUCT_CD ) IN ( 'AT080' , 'CP001' ))
10 AND ( S.SALES_TYPE ) IN (SELECT TYPE_CD FROM TYPES_JH WHERE ( TYPE_CD ) BETWEEN 'A' AND 'Z' )
11 AND S.SALES_DEPT IN ( SELECT DEPT_NO FROM DEPT_JH WHERE ( DEPT_NO ) LIKE '8%' )
12 -- AND ROWNUM <= 5
13 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_88348 000046 NAME_AT080 NAME_B 303000
NAME_80030 000490 NAME_AT080 NAME_F 476000
...

21 개의 행이 선택되었습니다.

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWritesOMem1MemUsed-MemUsed-Tmp

0SELECT STATEMENT12100:01:14.92104K309K218K
1HASH GROUP BY122100:01:14.92104K309K218K813K813K1355K (0)
2NESTED LOOPS12100:01:14.92104K309K218K
3NESTED LOOPS122100:01:14.92104K309K218K
4NESTED LOOPS122100:01:14.92103K309K218K
  • 5
HASH JOIN122100:01:14.92103K309K218K947K947K539K (0)
  • 6
TABLE ACCESS BY INDEX ROWIDSALES_JH192100:01:14.92103K309K218K
7BITMAP CONVERSION TO ROWIDS116900:01:14.92103K309K218K
8BITMAP AND1100:01:14.92103K309K218K
9BITMAP OR1100:00:00.011000
10BITMAP CONVERSION FROM ROWIDS1100:00:00.01500
  • 11
INDEX RANGE SCANSALES_JH_INDEX02171400:00:00.01500
12BITMAP CONVERSION FROM ROWIDS1100:00:00.01500
  • 13
INDEX RANGE SCANSALES_JH_INDEX02174900:00:00.01500
14BITMAP CONVERSION FROM ROWIDS117300:00:07.99131672186221862
15SORT ORDER BY15552K00:00:07.3013167218622186296M3353K112M (1)89088
  • 16
INDEX RANGE SCANSALES_JH_INDEX0115552K00:00:02.321316200
17BITMAP CONVERSION FROM ROWIDS120400:01:06.9190602287K196K
18SORT ORDER BY150M00:01:01.4690602287K196K865M9615K112M (1)774K
  • 19
INDEX RANGE SCANSALES_JH_INDEX03150M00:00:11.8290582903900
20INLIST ITERATOR1200:00:00.01610
21TABLE ACCESS BY INDEX ROWIDPRODUCTS_JH22200:00:00.01610
  • 22
INDEX UNIQUE SCANPRODUCTS_JH_PK22200:00:00.01410
23TABLE ACCESS BY INDEX ROWIDTYPES_JH2112100:00:00.012500
  • 24
INDEX UNIQUE SCANTYPES_JH_PK2112100:00:00.01400
  • 25
INDEX UNIQUE SCANDEPT_JH_PK2112100:00:00.012380
26TABLE ACCESS BY INDEX ROWIDDEPT_JH2112100:00:00.012100

Predicate Information (identified by operation id):

---
5 - access("P"."PRODUCT_CD"="S"."PRODUCT_CD")
6 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."COUNTRY_CD" IS NOT NULL))
11 - access("S"."PRODUCT_CD"='AT080')
13 - access("S"."PRODUCT_CD"='CP001')
16 - access("S"."SALES_DEPT" LIKE '8%')
filter(("S"."SALES_DEPT" IS NOT NULL AND "S"."SALES_DEPT" LIKE '8%' AND "S"."SALES_DEPT" LIKE '8%'))
19 - access("S"."SALES_TYPE">='A' AND "S"."SALES_TYPE"<='Z')
22 - access(("P"."PRODUCT_CD"='AT080' OR "P"."PRODUCT_CD"='CP001'))
24 - access("T"."TYPE_CD"="S"."SALES_TYPE")
filter(("T"."TYPE_CD">='A' AND "T"."TYPE_CD"<='Z'))
25 - access("D"."DEPT_NO"="S"."SALES_DEPT")
filter("D"."DEPT_NO" LIKE '8%')

{CODE}
* 옵티마이져들이 불필요하다고 생각하고 쿼리 변형이 일어나서 서브쿼리들을 배제해버렸음.
* ( 'AT080' , 'CP001' ) 조건때문에 BITMAP OR 수립됩
처리범위가 작다면 스타 변형 조인이 답인데.. 위에 처럼 처리 범위가 넓다면 정말 스타 변형 조인이 답인가.?
{CODE:SQL}

SQL> SELECT /*+ LEADING( P ) INDEX_JOIN( S ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND S.PRODUCT_CD IN ( SELECT /*+ UNNEST */ PRODUCT_CD FROM PRODUCTS_JH WHERE ( PRODUCT_CD ) IN ( 'AT080' , 'CP001' ))
10 AND ( S.SALES_TYPE ) IN (SELECT /*+ UNNEST */ TYPE_CD FROM TYPES_JH WHERE ( TYPE_CD ) BETWEEN 'A' AND 'Z' )
11 AND S.SALES_DEPT IN ( SELECT /*+ UNNEST */ DEPT_NO FROM DEPT_JH WHERE ( DEPT_NO ) LIKE '8%' )
12 -- AND ROWNUM <= 5
13 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
...

21 개의 행이 선택되었습니다.

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

0SELECT STATEMENT12100:00:00.011547
1HASH GROUP BY122100:00:00.011547813K813K1355K (0)
2NESTED LOOPS12100:00:00.011547
3NESTED LOOPS122100:00:00.011526
4NESTED LOOPS122100:00:00.011522
5NESTED LOOPS122100:00:00.011478
6INLIST ITERATOR1200:00:00.016
7TABLE ACCESS BY INDEX ROWIDPRODUCTS_JH22200:00:00.016
  • 8
INDEX UNIQUE SCANPRODUCTS_JH_PK22200:00:00.014
  • 9
TABLE ACCESS BY INDEX ROWIDSALES_JH212100:00:00.011472
  • 10
INDEX RANGE SCANSALES_JH_INDEX0221146300:00:00.0110
11TABLE ACCESS BY INDEX ROWIDDEPT_JH2112100:00:00.0144
  • 12
INDEX UNIQUE SCANDEPT_JH_PK2112100:00:00.0123
  • 13
INDEX UNIQUE SCANTYPES_JH_PK2112100:00:00.014
14TABLE ACCESS BY INDEX ROWIDTYPES_JH2112100:00:00.0121

---

Predicate Information (identified by operation id):

---

8 - access(("P"."PRODUCT_CD"='AT080' OR "P"."PRODUCT_CD"='CP001'))
9 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."SALES_DEPT" LIKE '8%' AND "S"."COUNTRY_CD" IS NOT NULL AND
"S"."SALES_TYPE">='A' AND "S"."SALES_TYPE"<='Z' AND "S"."SALES_DEPT" IS NOT NULL))
10 - access("P"."PRODUCT_CD"="S"."PRODUCT_CD")
filter(("S"."PRODUCT_CD"='AT080' OR "S"."PRODUCT_CD"='CP001'))
12 - access("D"."DEPT_NO"="S"."SALES_DEPT")
filter("D"."DEPT_NO" LIKE '8%')
13 - access("T"."TYPE_CD"="S"."SALES_TYPE")
filter(("T"."TYPE_CD">='A' AND "T"."TYPE_CD"<='Z'))

{CODE}

한번 액세스한 디멘전의 결과를 재사용하기 위해서 생성하는 임시( TEMP ) 테이블에 대해 소개하겠다.

  • 임시 테이블을 생성하는 이유 : 설사 내부적으로 테이블을 새롭게 만드는 부담이 있더라도 이를 재사용 하도록 하기 위한 것이다.
  • 임시 테이블을 사용하는 기준 : 가령, '고객' 데이블과 같이 대량의 데이터를 가진 디멘전 테이블이 자신의 범위를 크게 줄여주는 조건을 가지고 있을 때
    먼저 액세스하여 소량의 임시 테이블을 생성하고, 이를 활용하는 것은 분명히 효과가 있다.
    그러나 소량의 데이터를 가진 디멘전 테이블이거나 조건이 처리범위를 별로 줄여주지 못한다면 거의 효과가 없다.
  • BYPASS_RECURSIVE_CHECK : 인서트 문에서 사용하는 WITH 구분과 같음 ( TEMP )

  • 변형에 사용할 디멘전 테이블을 주어진 조건에 대한 추출하고 ( SYS_TEMP_OFD9D6615_BA951634 )
  • 이것을 임시 테이블에 저장했다가 ( TEMP TABLE TRANSFORMATION )
  • 비트맵 탐침에도 사용하고( BITMAP KEY ITERATION )
  • 연결을 위한 해쉬조인에서도 사용 하였음을 알수 있다. ( 아래에서 두번째 TABLE ACCESS FULL )
  • 스타변형 조인에서 디멘전 테이블이 항상 두번씩 액세스되는 것을 알고 있다 ( SELECT 절에 디멘전 테이블 컬럼을 사용 할 경우 ( 주관 ) )
  • 옵티마이져가 대형( Big dimension )이라고 판단한 것에 대해서만 생성하며, 또한 대형이라고 해서 모두 생성되는 것이 아니라
    조인에 참여하는 디멘전의 수에 따라 옵티마이져가 판단한다.
  • 디멘전이 4개 이상 있을 때는 가장 큰 디멘전은 아예 제외되기 때문에 어차피 임시 테이블은 영향을 주지 못한다.
    그러므로 사실 실전에서는 임시 테이블로 인한 수행속도 향상은 크게 기대할 만큼 나타나지 않는다.
  • 만약 여러분드이 보유하고 있는 데이터 웨어하우스의 데이터 모델에서 대부분의 디멘전들이 소형이라면 'TEMP_DISABLE'을 적용해도 무리가 없다.
    그러나 대형 디멘전들이 자주 사용되는 환경이라면 TRUE로 관리하는 것이 보다 좋은 결정이 될 것이다.

그럼 테스트 해봐야죠.?( 조건 범위가 큰 디멘저를 먼서 드라이빙해서 TEMP TABLE로 유도 하겠음 )

{CODE:SQL}

SQL> SELECT /*+ LEADING( D P T ) STAR_TRANSFORMATION USE_MERGE( D P T ) USE_NL( S ) INDEX( T TYPES_JH_INDEX01 ) */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND TRIM( P.PRODUCT_CD ) IN ( 'AT080' , 'CP001' )
10 AND TRIM( T.TYPE_CD ) BETWEEN 'A' AND 'Z'
11 AND TRIM( D.DEPT_NO) LIKE '8%'
12 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
NAME_84500 000349 NAME_AT080 NAME_C 71000
...

21 개의 행이 선택되었습니다.

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWritesOMem1MemUsed-Mem

---

0SELECT STATEMENT12100:01:22.37133K104K32
1TEMP TABLE TRANSFORMATION12100:01:22.37133K104K32
2LOAD AS SELECT1000:00:00.031051269K269K269K (0)
3INLIST ITERATOR1200:00:00.03650
4TABLE ACCESS BY INDEX ROWIDPRODUCTS_JH2683200:00:00.03650
  • 5
INDEX UNIQUE SCANPRODUCTS_JH_INDEX012273200:00:00.01430
6LOAD AS SELECT1000:00:00.2091128131269K269K269K (0)
7TABLE ACCESS BY INDEX ROWIDDEPT_JH145001000000:00:00.199078810
  • 8
INDEX RANGE SCANDEPT_JH_INDEX0118101000000:00:00.0324240
9HASH GROUP BY132100:01:22.14124K104K0813K813K1337K (0)
  • 10
HASH JOIN132100:01:22.14124K104K0766K766K1317K (0)
  • 11
HASH JOIN132100:01:22.13124K104K0785K785K580K (0)
12MERGE JOIN132100:01:22.13124K104K0
13SORT JOIN1492100:01:22.13124K104K0409640964096 (0)
  • 14
TABLE ACCESS BY INDEX ROWIDSALES_JH1492100:01:22.13124K104K0
15BITMAP CONVERSION TO ROWIDS116900:01:21.03124K103K0
16BITMAP AND1100:01:21.03124K103K0
17BITMAP MERGE1100:00:00.09161001024K512K7168 (0)
18BITMAP KEY ITERATION1200:00:00.0416100
19TABLE ACCESS FULLSYS_TEMP_0FD9D6678_9F5568191683200:00:00.01610
20BITMAP CONVERSION FROM ROWIDS2200:00:00.041090
  • 21
INDEX RANGE SCANSALES_JH_INDEX022146300:00:00.041090
22BITMAP MERGE121300:01:14.11906359060901024K512K86M (0)
23BITMAP KEY ITERATION1267900:00:17.8490635906090
24TABLE ACCESS BY INDEX ROWIDTYPES_JH112600:00:00.01220
  • 25
INDEX RANGE SCANTYPES_JH_INDEX01112600:00:00.01110
26BITMAP CONVERSION FROM ROWIDS26267900:00:17.8390633906070
  • 27
INDEX RANGE SCANSALES_JH_INDEX032650M00:00:10.4990633906070
28BITMAP MERGE117500:00:06.81336211322101024K512K25M (0)
29BITMAP KEY ITERATION11000000:00:03.1433621132210
30TABLE ACCESS FULLSYS_TEMP_0FD9D6679_9F556819145001000000:00:00.0236320
31BITMAP CONVERSION FROM ROWIDS100001000000:00:03.1033585131890
  • 32
INDEX RANGE SCANSALES_JH_INDEX01100005552K00:00:01.9533585131890
  • 33
SORT JOIN2112100:00:00.01200204820482048 (0)
34TABLE ACCESS BY INDEX ROWIDTYPES_JH112600:00:00.01200
  • 35
INDEX RANGE SCANTYPES_JH_INDEX01112600:00:00.01100
36TABLE ACCESS FULLSYS_TEMP_0FD9D6678_9F5568191683200:00:00.01320
37TABLE ACCESS FULLSYS_TEMP_0FD9D6679_9F556819145001000000:00:00.013300

---

Predicate Information (identified by operation id):

---

5 - access(("P"."SYS_NC00003$"='AT080' OR "P"."SYS_NC00003$"='CP001'))
8 - access("D"."SYS_NC00003$" LIKE '8%')
filter("D"."SYS_NC00003$" LIKE '8%')
10 - access("C0"="S"."SALES_DEPT")
11 - access("C0"="S"."PRODUCT_CD")
14 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."COUNTRY_CD" IS NOT NULL))
21 - access("S"."PRODUCT_CD"="C0")
25 - access("T"."SYS_NC00003$">='A' AND "T"."SYS_NC00003$"<='Z')
27 - access("S"."SALES_TYPE"="T"."TYPE_CD")
32 - access("S"."SALES_DEPT"="C0")
33 - access("T"."TYPE_CD"="S"."SALES_TYPE")
filter("T"."TYPE_CD"="S"."SALES_TYPE")
35 - access("T"."SYS_NC00003$">='A' AND "T"."SYS_NC00003$"<='Z')

{CODE}
* 설명위 위와 같음

복합 인덱스 사용시 주의할 사항.

준비 스크립
{CODE:SQL}

SQL> SELECT COUNT(*) / ( 101 * 13 )
2 FROM PRODUCTS_JH;

COUNT(*)/(101*13)

-
52

ALTER TABLE PRODUCTS_JH ADD SUPPLIER_CD NUMBER;

UPDATE PRODUCTS_JH
SET SUPPLIER_CD = TRUNC(dbms_random.value( 1, 101 * 13 + 1 ) )

COMMIT;

SQL> SELECT *
2 FROM (SELECT SUPPLIER_CD, COUNT(*)
3 FROM PRODUCTS_JH
4 GROUP BY SUPPLIER_CD
5 )
6 WHERE ROWNUM <= 5;

SUPPLIER_CD COUNT(*)

---
--
1 46
2 51
3 54
4 64
5 60

CREATE TABLE SUPPLIER_JH AS
SELECT LEVEL SUPPLIER_CD
, 'NAME_' || LEVEL SUPPLIER_NAME
FROM DUAL
CONNECT BY LEVEL <= 101 * 13

CREATE UNIQUE INDEX SUPPLIER_JH_PK ON SUPPLIER_JH ( SUPPLIER_CD ) TABLESPACE ORA03TS02

ALTER TABLE SUPPLIER_JH ADD (
CONSTRAINT SUPPLIER_JH_PK
PRIMARY KEY
(SUPPLIER_CD)
USING INDEX SUPPLIER_JH_PK);

ALTER TABLE PRODUCTS_JH ADD (
CONSTRAINT PRODUCTS_JH_FK1
FOREIGN KEY (SUPPLIER_CD) --SALES_DEPT 4 Y VARCHAR2 (20 Byte) Height Balanced 90144
REFERENCES SUPPLIER_JH (SUPPLIER_CD));

--CREATE UNIQUE INDEX PRODUCTS_JH_INDEX02 ON PRODUCTS_JH ( SUPPLIER_CD, PRODUCT_CD ) TABLESPACE ORA03TS02

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'PRODUCTS_JH' );

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'SUPPLIER_JH' );

SQL> SELECT *
2 FROM COUNTRY_JH
3 WHERE ROWNUM <= 5
4 ;

COUNTRY_CD COUNTRY_NAME

--

000001 NAME_1
000002 NAME_2
000003 NAME_3
000004 NAME_4
000005 NAME_5

{CODE}
실행 스크립트
{CODE:SQL}

SQL> SELECT /*+ LEADING( U ) STAR_TRANSFORMATION FULL( P ) FULL( U ) INDEX_COMBINE( S ) */ C.COUNTRY_CD, P.PRODUCT_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM SALES_JH S, COUNTRY_JH C, PRODUCTS_JH P, SUPPLIER_JH U
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND P.SUPPLIER_CD = U.SUPPLIER_CD
7 AND C.COUNTRY_NAME = 'NAME_5'
8 AND U.SUPPLIER_NAME = 'NAME_3'
9 GROUP BY C.COUNTRY_CD, P.PRODUCT_NAME
10 ;

COUNTRY_CD PRODUCT_NAME SUM(S.SALES_AMOUNT)

--
--
---
000005 NAME_CP047 899000
000005 NAME_CV073 385000
...

38 개의 행이 선택되었습니다.

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersReadsWritesOMem1MemUsed-Mem

0SELECT STATEMENT13800:00:00.5490711
1TEMP TABLE TRANSFORMATION13800:00:00.5490711
2LOAD AS SELECT1000:00:00.0533801269K269K269K (0)
  • 3
HASH JOIN1525400:00:00.05334001180K1180K381K (0)
  • 4
TABLE ACCESS FULLSUPPLIER_JH11100:00:00.01600
5TABLE ACCESS FULLPRODUCTS_JH1682766827600:00:00.0132800
6HASH GROUP BY1373800:00:00.4956610865K865K1357K (0)
  • 7
HASH JOIN1769200:00:00.4956610858K858K1264K (0)
8NESTED LOOPS1525400:00:00.01910
9TABLE ACCESS BY INDEX ROWIDCOUNTRY_JH11100:00:00.01300
  • 10
INDEX UNIQUE SCANCOUNTRY_JH_INDEX11100:00:00.01200
11TABLE ACCESS FULLSYS_TEMP_0FD9D66A7_9F5568191525400:00:00.01610
12TABLE ACCESS BY INDEX ROWIDSALES_JH1769200:00:00.4955700
13BITMAP CONVERSION TO ROWIDS19200:00:00.4946500
14BITMAP AND1100:00:00.4946500
15BITMAP MERGE1400:00:00.22206001024K512K173K (0)
16BITMAP KEY ITERATION15400:00:00.0220600
17TABLE ACCESS FULLSYS_TEMP_0FD9D66A7_9F5568191525400:00:00.01300
18BITMAP CONVERSION FROM ROWIDS545400:00:00.0220300
  • 19
INDEX RANGE SCANSALES_JH_INDEX02543932900:00:00.0120300
20BITMAP MERGE1900:00:00.27259001024K512K291K (0)
21BITMAP KEY ITERATION1900:00:00.0525900
22TABLE ACCESS BY INDEX ROWIDCOUNTRY_JH11100:00:00.01300
  • 23
INDEX UNIQUE SCANCOUNTRY_JH_INDEX11100:00:00.01200
24BITMAP CONVERSION FROM ROWIDS1900:00:00.0525600
  • 25
INDEX RANGE SCANSALES_JH_INDEX041100K00:00:00.0325600

Predicate Information (identified by operation id):

3 - access("P"."SUPPLIER_CD"="U"."SUPPLIER_CD")
4 - filter("U"."SUPPLIER_NAME"='NAME_3')
7 - access("C"."COUNTRY_CD"="S"."COUNTRY_CD" AND "C0"="S"."PRODUCT_CD")
10 - access("C"."COUNTRY_NAME"='NAME_5')
19 - access("S"."PRODUCT_CD"="C0")
23 - access("C"."COUNTRY_NAME"='NAME_5')
25 - access("S"."COUNTRY_CD"="C"."COUNTRY_CD")

{CODE}
* PRODUCTS_JH.SUPPLIER_CD 범위가 넓어 옵티마이져가 템프를 선택함.

비트맵 조인 인덱스 ( Bitmap Joint Index )

비트맵 조인 인덱스
{CODE:SQL}

SQL> SELECT /*+ STAR_TRANSFORMATION */ D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME
2 , SUM( S.SALES_AMOUNT )
3 FROM COUNTRY_JH C, PRODUCTS_JH P, DEPT_JH D, TYPES_JH T, SALES_JH S
4 WHERE C.COUNTRY_CD = S.COUNTRY_CD
5 AND P.PRODUCT_CD = S.PRODUCT_CD
6 AND D.DEPT_NO = S.SALES_DEPT
7 AND T.TYPE_CD = S.SALES_TYPE
8 AND S.SALES_DATE LIKE '201104%'
9 AND P.PRODUCT_NAME IN ( 'NAME_AT080' , 'NAME_CP001' )
10 AND T.TYPE_NAME BETWEEN 'NAME_A' AND 'NAME_Z'
11 AND D.DEPT_NO LIKE '8%'
12 GROUP BY D.DEPT_NAME, C.COUNTRY_CD, P.PRODUCT_NAME, T.TYPE_NAME;

DEPT_NAME COUNTRY_CD PRODUCT_NAME TYPE_NAME SUM(S.SALES_AMOUNT)

--
--
--
--
---
NAME_80030 000490 NAME_AT080 NAME_F 476000
NAME_88348 000046 NAME_AT080 NAME_B 303000
...

21 개의 행이 선택되었습니다.

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

0SELECT STATEMENT12100:00:00.021846
1HASH GROUP BY112100:00:00.021846813K813K1342K (0)
2NESTED LOOPS12100:00:00.021846
3NESTED LOOPS112100:00:00.021825
4NESTED LOOPS112100:00:00.021802
  • 5
HASH JOIN112100:00:00.0117971049K1049K504K (0)
  • 6
TABLE ACCESS FULLPRODUCTS_JH12200:00:00.01328
7INLIST ITERATOR12100:00:00.011469
  • 8
TABLE ACCESS BY INDEX ROWIDSALES_JH214652100:00:00.011469
9BITMAP CONVERSION TO ROWIDS2146300:00:00.017
  • 10
BITMAP INDEX SINGLE VALUESALES_PROD_NAME_BIX2200:00:00.017
  • 11
TABLE ACCESS BY INDEX ROWIDTYPES_JH2112100:00:00.015
  • 12
INDEX UNIQUE SCANTYPES_JH_PK2112100:00:00.014
  • 13
INDEX UNIQUE SCANDEPT_JH_PK2112100:00:00.0123
14TABLE ACCESS BY INDEX ROWIDDEPT_JH2112100:00:00.0121

---

Predicate Information (identified by operation id):

---

5 - access("P"."PRODUCT_CD"="S"."PRODUCT_CD")
6 - filter(("P"."PRODUCT_NAME"='NAME_AT080' OR "P"."PRODUCT_NAME"='NAME_CP001'))
8 - filter(("S"."SALES_DATE" LIKE '201104%' AND "S"."SALES_DEPT" LIKE '8%' AND "S"."COUNTRY_CD" IS NOT NULL))
10 - access(("S"."SYS_NC00009$"='NAME_AT080' OR "S"."SYS_NC00009$"='NAME_CP001'))
11 - filter(("T"."TYPE_NAME">='NAME_A' AND "T"."TYPE_NAME"<='NAME_Z'))
12 - access("T"."TYPE_CD"="S"."SALES_TYPE")
13 - access("D"."DEPT_NO"="S"."SALES_DEPT")

{CODE}
* 마치 팩트 테이블 컬럼처럼 인식하여 서브쿼를 만들어 비트랩 탐침도, 비트맵 머지도 하지 않고 바로 'BITMAP AND'에 참여하는 것을 확인하기 바란다.
* 아주 빈번하게 사용되는 디멘전 테이블의 컬럼들의 일부를 같은 방법으로 관리한다면 보다 향상된 수행속도를 얻을 수 있다.

비트맵 조인 인덱스의 개념에 대해 좀더 자세하게 알아보기로 하자.

  • 개념을 가장 쉽게 이해하는 방법은 조인된 결과의 집합을 하나의 테이블이라고 생각하는 것이다.
    바로 이 논리적 테이블에 있는 컬럼을 비트맵 인덱스로 생성하였다고 생각하면 아주 쉽다.
  • 비트맨 조인 인덱스를 생성하는 문장을 살펴보자. 마치 SELECT 문의 조인처럼 FROM저로가 WHERE절을 가지고 있다.
    서로 조인되는 테이블들은 동등한 관계를 가지고 있기 때문에 조인된 결과는 어느 특정 테이브의 소유로 볼 수는 없다.
    이를 분명히 하기 위해 위의 생성문장에는 'ON SALES_JH'을 지정한 것이다.

조인을 하더라도 인덱스를 생성하는 집합이 그대로 보존되기 위해서는 다음의 사항을 준수해야한다. p. 633

  • 인덱스를 생성하는 테이블에 조인( 참조 )되는 테이블은 반드시 기본키 컬럼이거나 유일성 제약 조건( Unique Constraints )을 가진 컬럼이 조인조건이 되어야 한다.
    그것은 조인되는 집합의 유일성이 보장되지 않으면 그 결과는 보존될 수 없기 때문이다.
  • 참조되는테이블의 조인컬럼은그 테이블의 기본키로 지정되어 있거나, 인텍스를 생성하는 테이블에서 외부키 제약조건이 선언되어야 한다.
  • 조인 조건은 반드시 이퀄( = )이어야 하며,아우터 조인을 사용할 수 없다.
  • 인덱스를 생성하는 조인 문에 집합을 처리하는 연산( Union, Minus 등 ), DISTINCT, SUM, AVG, COUNT 등의 집계 함수, 분석 함수, GROUP BY, ORDER BY, CONNECT BY,
    START WITH절의 상요해서는 안 된다.
  • 인덱스 컬럼은 반드시 조인되는 테이블에 소속된 컬럼이어야 한다.
  • 비트맵 조인인덱스는 비트맵 인덱스의 일종으므로 당연히 일반적인 비트맵 인덱스의 각종 규칙을 그대로 준수해야 한다.
    가령, 유일성을 가진 컬럼은 비트맵 인덱스를 생성할 수 없다.

비트맵 조인인덱스 사용상제약 사항

  • 병렬DML 처리는 비트맵조인 인덱스르 가지고 있는 테이블에서만 지원된다.
    만약 관련된 참조 테이블에서 병렬 DML을 수행시키면 비트맵 조인인덱스는 'UNUSABLE'상태가 된다.
  • 비트맵조인인덱스르 사용하게 되면, 언떤 트랜잭션에서 동시에 오직 하나의 테이블만처리해야 한다. 조인된 테이블이 COMMIT되지 않은상태에서 동시에 변경되면일관성을 보장할 수가 없기 때문이다.
  • 조인 문장에서 동일한 테이블이 두 번 등장 할 수 없다.
동시에 여러 테이블을 참조하여 결합된 구조를 생성할 수도 있다.
{CODE:SQL}

CREATE BITMAP INDEX SALES_PROD_CTRY_NAME_BIX
ON SALES_JH( PRODUCTS_JH.PRODUCT_NAME, COUNTRY_JH.COUNTRY_NAME )
FROM SALES_JH
, PRODUCTS_JH
, COUNTRY_JH
WHERE SALES_JH.PRODUCT_CD = PRODUCTS_JH.PRODUCT_CD
AND SALES_JH.COUNTRY_CD = COUNTRY_JH.COUNTRY_CD

{CODE}
* 방법상 전혀 문제가 없지만, 바람직 하지 않다. ( 비트맵 인덱스의 특성상 임의의 비트맵이 독립적으로 사용되어 그때마다 비트맵 연사을 수행하여 해결하는 것이 여러 가지로 유리 하기 때문이다.)

결론 ( 이거 왜 필요한데.? )

  • 결합 인덱스는 어떤 조건절에서 특정 컬럼이 사용되지 않거나 =로 사용되지 않을 때 결합된 인덱스 컬럼의 순서에 영향을 받는다.
    그러므로 상황에 따라 적절한 인덱스 구조를 제공하려면 너무 많은 인덱스가 필요하게 되었다.
    이러한 단점을 개선하기 위해 도입한 비트맵 인덱스르 굳이 결합된 컬럼으로 할 필요는 없다.
    특히 비트맵인덱스는 보다 제한 사항이 많기 때문에 함부로 결합하지 않는 것이 좋다.
    물론 아주 업무적으로 친밀도가 강력하여 언제나 같이 사용하는 경우라면 검토해볼 수도 있을 것이다.
    그러나 역시 우리가 관심을 가져야 할 것은 여러 디멘전 테이븡레서 다양한 형태의 검색 조건을 받았을 때의 조인 최적화를 위한 스타변형 조이을 위해 이 개념을 활용하는 것이 바람직하다.

2000

  1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2011년 하반기 대용량데이터베이스 스터디
  4. 2.3.6 스타조인