adminschool.net wiki

사용자 도구

사이트 도구

추적: • 4week_1
study:oracle:datadb:4week_1

목차

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)
  • 각 단계(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)
study/oracle/datadb/4week_1.txt · 마지막으로 수정됨: 2010/05/27 16:38 저자 tlsga

문서 도구

별도로 명시하지 않을 경우, 이 위키의 내용은 다음 라이선스에 따라 사용할 수 있습니다: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki