이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

3.2.4. 비트맵(Bitmap) 실행계획

2009.08.23 21:54

휘휘 조회 수:86016

다양한 실행계획의 종류를 하나씩 살펴보고

각 상황에 맞는 쿼리 형태를 학습한후 상황에 맞게 사용할수 있도록 이해하여 필요시 적적하게

사용할수 있도록 하는게 중요



3.2.4. 비트맵(Bitmap) 실행계획
비트맵은 단일 테이블의 입장에서만 살펴봄

** 범위
조건 연산자별 비트맵 실행계획
- 동치(Equal) 비교 실행계획
- 범위(Range) 비교 실행계획
- AND 조건 실행계획
- OR 조건 실행계획
- 부등식(Not equal)실행계획
-NULL 비교 실행계획
서브 쿼리 실행계획
B-Tree 인덱스와의 연합(Comnbine) 실행계획

3.2.4.1. 조건 연산자별 비트맵 실행계획

가)동치(Equal) 비교 실행계획


'=' 이나 'IN' 연산 모두 SINGLE VALUE로 나타남


SELECT *

FROM SALES_SUM

WHERE TIME_CD='200512';


Execution Plan

---------------------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP INDEX(SINGLE VALUE) OF 'TIME_BIX'


....

WHERE TIME_CD IN ('200505','200507','200510');


Execution Plan

-------------------------------------

INLIST ITERATOR

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP INDEX(SINGLE VALUE) OF 'TIME_BIX'



나)범위(Range)비교 실행계획

'BETWEEN,LIKE,>,<,>=,<='  ;  RANGE SCAN


SELECT *

FROM SALES_SUM

WHERE TIME_CD BETWEEN '200505' AND '200507'


Exectuion Plan

-------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

         BITMAP INDEX (RANGE SCAN) OF 'TIME_BIX'


만약 NUMBER 형으로 저장된 컬럼에 LIKE 를 사용한다면

BTREE INDEX는 자료 변형으로 사용되지 않지만 BITMAP은 'FULL SCAN'으로 나타난다.


SELECT * FROM FROM SALES_SUM

WHERE SALE_DEPT LIKE '1250%'

 

Execution Plan

-------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP INDEX (FRULL SCAN) OF 'SALE_DEPT_BIX'


다) AND 조건 실행계획

두 비트맵 인덱스 보유 컬럼의 'AND' 조건 사용시 'AND 연산' 실시


SELECT * FROM SALES_SUM

WHERE SALE_DEPT = '12500'

AND TIME_CD ='200510';


Execution Plan

-------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP AND

                BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

                BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'


BETWEEN등으로 범위스캔하고 AND 조건 사용시


SELECT * FROM SALES_SUM

WHERE SALE_DEPT = '12500'

    AND TIME_CD BETWEEN '200510' AND '200512';


Execution Plan

-------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP AND

                BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'

                BITMAP MERGE

                    BITMAP INDEX (RANGE SCAN) OF 'TIME_BIX'



라)OR조건 실행계획

각컬럼별로 단위 액세스를 생성후 'OR' 연산 실시


SELECT * FROM SALES_SUM

WHERE TIME_CD = '200510'

OR ITEM_CD LIKE 'ABC%'

OR SALE_DEPT = 12500;


Execution Plan

---------------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP OR

                BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

                BITMAP MERGE

                    BITMAP INDEX (RANGE SCAN) OF 'ITEM_BIX'

                BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'



복잡한 OR 조건에도 동일한 원리로 수행

부정형 조건과 사용되면 부정형 조건이 적용된 컬럼은 사용되지 않음



마)부등식(Not equal)비교 실행계획

'BITMAP MINUS' 연산을 수행 ; 등식 조건으로 액스사한 후 먼저 수행한 비트맵에서 제거하는 처리


단일 컬럼만 존재할경우 ; FULL SCAN 수행


NOT NULL 제약 조건을 가진경우 AND 연산 ; BITMAP MINUS를 한번만 거침

SELECT * FROM SALES_SUM

WHERE TIME_CD = '200512'

AND SALE_DEPT <> 12500;


Execution Plan

-------------------------------

SELECT STATMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP MINUS

                BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

                BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'



NULL을 허용할 경우


        BITMAP MINUS    

               BITMAP MINUS    

                    BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

                    BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'

              BITMAP INDEX (SINGLE VALUE) OF 'SALE_DEPT_BIX'   <--- NULL인경우 제거




바)NULL비교 실행계획


NULL 연산 역시 일반 값들과 동일하게 비트맵 연산에 참여시켜 동작


SELECT * FROM SALES_SUM

WHERE TIME_CD='200512'

AND COUNTRY IS NULL;


Execution Plan

----------------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP AND

                BITMAP INDEX (SINGLE VALUE) OF 'TIME_BIX'

                BITMAP INDEX (SINGLE VALUE) OF 'COUNTRY_BIX'



3.2.4.2. 서브쿼리 실행계획


SELECT * FROM SALES_SUM

WHERE ITEM_CD IN (SELECT ITEM_CD FROM ITEM_T

                            WHERE CATEGORY_CD = 'ABC' );


Execution Plan

------------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM'

        NESTED LOOPS

            SORT (UNIQUE)  <----- 서브 쿼리

                TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_T'

                    INDEX (RANGE SCAN) OF 'ITEM_IDX2' (NON-UNIQUE)

            BITMAP CONVERSION (TO ROWIDS)

                BITMAP INDEX (SINGLE VALUE) OF 'ITEM_BIX'  <-- 제공 받은 값으로 비트맵 인덱스



서버쿼리가 동시에 사용될경우는

첫 서브쿼리가 먼저 수행되어 비트맵 인덱스로 실행되어 제공자 역할을 한후 후 수행되는 서브쿼리가

제공자 역할을 하는 형태의 수행계획이 생성됨


스타 변형 조인 ; 모든 서브쿼리가 먼저 수행되어 처리 범위를 충분히 줄인 다음 팩트 테이블을 액세스 하는것


SELECT /*+ STAR_TRANSFORMATION */

        I.ITEM_CD, SUM(S.AMOUNT) SAL_AMOUNTS

FROM    SALES_SUM S,ITEMS I,COUNTRYS C

WHERE S.ITEM_CD = I.ITEM_CD

AND S.COUNTRY = C.COUNTRY_CD

AND I.CATEGORY_TYPE = 'Clothes'

AND C.AREA='EUROPE'

GROUP BY I.ITEM_CD;



Execution Plan

--------------------------------

SELECT STATMENT Optimizer=ALL_ROWS

    SORT GROUP BY

        HASH JOIN

            HASH JOIN <-- 엑세스한 팩트 테이블과 디멘전 테이블 조인

                TABLE ACCESS (BY INDEX ROWID) OF 'SALES_SUM<-- 비트맵들을 결합하여 액세스

                    BITMAP CONVERSION (TO ROWIDS)

                        BITMAP AND

                            BITMAP AND

                                BITMAP MERGE  <-- ITEMS 서브쿼리로 팩트 테입ㄹ의 비트맵 액세스

                                    BITMAP KEY ITERATION

                                        TABLE ACCESS (FULL) OF 'ITEMS'

                                        BITMAP INDEX (RANGE SCAN) OF 'ITEM_BIX'

                                BITMAP MERGE <-- COUNTRY 서브쿼리로 팩트 테이블의 비트맵 액세스

                                    BITMAP KEY ITERATION

                                        TABLE ACCESS (FULL) OF 'COUNTRYS'

                                        BITMAP INDEX (RANGE SCAN) OF 'COUNTRY_BIX'



3.2.4.3. B-tree 인덱스와의 연합(Combine) 실행계획


'ITEM_CD'; 비트맵 인덱스

'PRICE' ; B-Tree 인덱스


SELECT /*+ INDEX_COMBINE (SALES) */ *

FROM SALES

WHERE ITEM_CD = 'PA100'

AND PRICE >= 100000;


Execution Plan

----------------------------------------

SELECT STATEMENT

    TABLE ACCESS (BY INDEX ROWID) OF 'SALES'

        BITMAP CONVERSION (TO ROWIDS)

            BITMAP AND

                BITMAP INDEX (SINGLE VALUE) OF 'ITEM_BIX'

                BITMAP CONVERSION (FROM ROWIDS)  <- BTREE를 비트맵으로 전환

                    SORT (ORDER BY)

                        INDEX (RANGE SCAN) OF 'PRICE_IDX' (NON-UNIQUE)


B-TREE인덱스만 사용될경우도 'INDEX_COMBINE' 힌트를 적용하면 비트맵 액세스가능


테이블 액세스전 최대한 인덱스간의 머지를 통해 범위를 줄이는것이 가능하지만

인덱스 머지의 오버해드가 더 커다면 역효과가 나타날수도 있음




3.2.5. 기타 특수한 목적을 처리하는 실행계획


3.2.5.1. 순환(Recursive) 실행계획

'CONNECT BY ... START WITH' 문을 사용했을 때나타나는 실행계획

순전개


SELECT LPAD(' ',2* (LEVEL -1__ || ename, empno, sal,mgr,

        SYS_CONNECT_BY_PATH(last_name,'/') "Path"

FROM emp

WHERE job='CLERK'

CONNECT BY mgr= PRIOR empno

START WITH empno= :b1;


Execution Plan

-------------------------------------

SELECT STATEMENT

    FILETER  --- 3  where절 job='clerk'

        CONNECT BY (WITH FILTERNING)

            NESTED LOOPS  --- 1  start with 에서 정한 초기 조건

                INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

                TABLE ACCESS (BY USER ROWID) OF 'EMP
            NESTED LOOPS  ---2   connect by 절의 prior 절뒤쪽을 가져와서 상대 컬럼을 조인비교
                BUFFER (SORT)

                    CONNECT BY PUMY

                TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

                    INDEX (RANGE SCAN) OF 'MGR_IDX' (UNIQUE)



* 1,2,3 의 순서대로 수행


각 단계(level)별 정렬이 필요할경우 ORDER  SIBLINGS BY ... 을 사용

connect by 의 연결에 사용할 인덱스가 없거나 테이블 크기가 적다면 해쉬조인으로 내측 루프를 수행


조인쿼리를 순환 전개 시킬경우

where 절의 조인을 수행한후 루트들을 선별 (조인한 결과 집합이 필요하기 때문)


3.2.5.2. UPDATE 서브쿼리 실행계획


UPDATE 문은 SET절과 WHERE 절에서 서브쿼리 사용가능

 SET 절의 서브쿼리를 스칼라 서브 쿼리 (하나의 로우에 하나의 컬럼값을 가짐)


UPDATE emp e

SET sal = (SELECT AVG(sal) * 1.2

                FROM bouns b

                WHERE b.empno=e.empno

                    AND b.pay_date between :b1 and :b2)

WHERE deptno IN (SELECT deptno FROM dept

                         WHERE loc='BOSTON');


Execution Plan

-------------------------

UPDATE STATEMENT

    UPDATE OF 'EMP'

           TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

                NESTED LOOPS

                    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'  --- 서브쿼리를 수행 하여 메인쿼리와 조인

                        INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)

                    INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

            SORT (AGGREGATE)

                    TABLE ACCESS (BY INDEX ROWID) OF 'BONUS'  -- SET 절의 스칼라 쿼리 수행

                        INDEX (RANGE SCAN) OF 'BONUS_PK' (UNIQUE)


UPDATE 문이라고해도 해쉬조인으로도 수행

* 메인쿼리에서 제공받은 값으로 수행된 서브쿼리의 결과가 'No DATA FOUND' 라면 'NULL'로 인정되어 갱신

* 에러: 쿼리가 정상적으로 수행되지 않은것

* 실패: 결과가 존재하지 않은것 ; NVL 등이 듣지 않음

* SUM,MIN,MAX,COUNT,AVG등의 그룹함수는 '실패'가 발생하지 않음


뷰의 UPDATE


CREATE OR REPLACE VIEW EMP_DEPT_VIEW AS

       SELECT X.EMPNO, X.ENAME, X.JOB, X.SAL, Y.LOC, Y.DNAME

       FROM EMP X, DEPT Y

       WHERE X.DEPTNO = Y.DEPTNO;


    UPDATE EMP_DEPT_VIEW E

        SET SAL= DECODE(DNAME,'SALES',1.2,1,1) * SAL

    WHERE LOC LIKE 'AB%';


Execution Plan

-------------------------------------

UPDATE STATEMENT

        UPDATE OF 'EMP'

            HASH JOIN

                TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

                    INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)

                TABLE ACCESS (FULL) OF 'EMP'






3.2.5.3. 특이한 형태의 실행계획

가) 서브쿼리 팩토링 실행계획


'WITH' : 쿼리를 임시 테이블에 저장한후 사용 (INLINE 뷰와 유사하게 사용)


WITH total_sal AS

    (SELECT D.deptno, D.loc, E.job, sum(E.sal) tot_sal

        FROM emp E, dept D

        WHERE E.deptno=D.deptno

            AND    E.hiredate > :b1

        GROUP BY D.deptno, D.loc, E.job)

SELECT e.empno, e.name, e.sal, e.sal/t.tot_sal sal_percent

FROM emp e,total_sal t

WHERE e.deptno = t.deptno

    AND e.sal > (SELECT max(tot_sal)

                    FROM total_sal

                    WHERE job='CLERK');


Execution Plan

----------------------

SELECT STATEMENT

        RECURSIVE EXECUTION OF 'SYS_LE_2_0'   --- WITH 절의 쿼리 수행

        TEMP TABLE TRANSFORMATION

            TABLE ACCESS (BY INDEX ROWID) OF OF 'EMP'

                NESTED LOOPS

                    VIEW

                        TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6616_165207A'

                    INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)

                SORT (AGGREGATE)

                    VIEW

                        TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6616_165207A'


TEMP TABLE TRANSFORMATION ; 기존의 쿼리를 새로 생성된 임시 테이블로 변형

WITH절의 쿼리가 한번만 쓰임 인라인 뷰였다면 동일 쿼리를 두번수행

한번의 복잡한 가공을 여러번 재활용 할때 최적

WITH절의 하나이상을 사용가능



나) 특인한 DELETE 문 서브쿼리

DELETE FROM (SELECT * FROM EMP

                    WHERE JOB='CLERK'

                        AND comm > 10000

                        AND deptno IN (SELECT deptno FROM dept

                                                WHERE loc='BOSTOM'));


Execution Plan

-----------------------------------------

DELETE STATEMENT

    DELETE OF 'EMP'

        NESTED LOOPS

            TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

                INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

            TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

                INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)


다)다중테이블 입력(Multi-table Insert)서브쿼리

INSERT ALL

    WHEN ORDER_TOTAL < 1000000

            THEN    INTO SMALL_ORDERS

    WHEN ORDER_TOTAL > 1000000 AND ORDER_TOTAL  < 2000000

            THEN    INTO MEDIUM_ORDERS

    WHEN ORDER-TOTAL > 2000000

            THEN INTO LARGE_ORDERS

    SELECT ORDER_ID,ORDER_TOTAL,SALES_REP_ID,CUSTOMER_ID

    FROM ORDERS;


------------------

INSERT STATEMENT

    MULTI-TABLE INSERT

        INTO OF 'SMALL_ORDERS'

        INTO OF 'MEDIUM_ORDERS'

        INTO OF 'LARGE_ORDERS'

            TABLE ACCESS (FULL) OF 'ORDERS'


시스템 데이터를 관계형 데이터베이스 구조로 이행할때 유용


라)HAVING 절 서브쿼리 실행 계획



SELECT department_id,manager_id

FROM employees

GROUP BY department_id,manager_id

HAVING (department_id, manager_id) IN (SELECT e.deptno, e.mgr

                                                        FROM emp e,dept d

                                                        WHERE e.deptno = d.deptno

                                                            AND d.oc='BOSTON');


Execution Plan

-------------------------------

SELECT STATEMENT

    FILTER

        SORT (GROUP BY)

            TABLE ACCESS (FULL) OF 'EMPLOYEES'

        NESTED LOOPS

            TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

                INDEX (RANGE SCAN) OF 'LOC_IDX' (NON-UNIQUE)

            AND-EQUAL

                INDEX (RANGE SCAN) OF 'EMP_MGR_IDX' (NON-UNIQUE)

                INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)



마)ROLLUP,CUBE,GROUPING SETS 처리 실행계획



SELECT co.country_region,co.country_subregion,

        SUM*s.amount_sold) "Revenue",

        GROUP_ID() g

FROM sales s,customers c, countries co

WHERE s.cust_id=c.cust_id

    AND c.country_id=co.country_id

    AND s.time_id= :b1

    AND co.country_region IN ('Americas','Europe')

GROUP BY ROLLUP (co.country_region, co.country_subregion);


Execution Plan

-----------------------------

SELECT STATEMENT

    SORT (GROUP BY ROLLUP)

        NESTED LOOPS

            NESTED LOOPS

                PARTITION RANGE (SINGLE)

                    TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES'

                        BITMAP CONVERSION (TO ROWIDS)

                            BITMAP INDEX (SINGLE VALUE) OF 'SALES_TIME_BIX'

                TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'

                     INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)

            INDEX (UNIQUE SCAN) OF 'COUNTRY_C_ID_PK' (UNIQUE)


CUBE 역시 동일 형태의 실행계획 생성

GROUPING SETS; GROUPING SETS( (DEPTNO,JON),(ENAME,DEPTNO) ) 의 형태로 사용되며

         정의된 그룹별로 결과가 나타남



바)MERGE문 실행계획


조인에 성관한것은 UPDATE, 실패한것은 INSERT


MERGE INTO bonuses D

USING ( SELECT employee_id, salary, department_id

            FROM employees

            WHERE department_id=80) S

ON (D.employee_id = S.employee_id)

WHEN MATCHED     THEN UPDATE SET D.bonus = D.bonus + S.salary * .01

                                    DELETE WHERE (S.salary > 8000)

WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

                                     VALUES (S.employee_id, S.salary * 0.1);


Execution Plan

-------------------------------------

MERGE STATEMENT

    MERGE OF 'BONUSES'

        VIEW

            NESTED LOOPS (OUTER)

                TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'

                    INDEX (RANGE SCAN) OF 'EMPLOYEE_X1' (NON-UNIQUE)

                TABLE ACCESS (BY INDEX ROWID) OF 'BONUSES'

                    INDEX (RANGE SCAN) OF 'BONUSES_X1' (NON-UNIQUE)