3.2.3 연산 방식에 따른 실행계획

1. IN-List 탐침 실행계획
2. 연쇄(Concatenation) 실행계획
3. 원격(Remote) 실행계획
4. 정렬 처리(Sort Operation)
5. 합집합 (Union, Union-All) 실행계획
6. 교집합(Intersection) 실행계획
7. 차집합(Minux) 실행계획
8. COUNT(STOPKEY) 실행계획

 

 

3.2.3.1. IN-List 탐침(Iterator) 실행계획

• IN-LIST ITERATOR의 특징
• 옵티마이져가 'IN' 연산을 'OR' 형태로 변형 후 실행한다.
  예) COL1 IN (1,2) AND COL2 =10  ☞  (COL1=1 AND COL2=10) OR (COL1=2 AND COL2=10)
• 'IN'을 사용했더라도 비교값이 하나만 있는 경우에는 INLIST ITERATOR 실행계획은 나타나지 않는다.

◆ SQL 예

SELECT order_id, order_type, order_amount

FROM orders

WHERE order_type IN (1,2,3);

 

Execution Plan

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

SELECT STATEMENT

   INLIST ITERATOR  => IN 연산이 OR로 실행 / 결합 인덱서의 처리범위를 효율적으로 처리 / 비교값 만큼 반복 수행

      TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'

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

 

• 'INLIST ITERATOR'   ☞   IN 조건의 비교값 만큼 반복 수행을 의미

 

 

 3.2.3.2 연쇄(Concatenation) 실행계획

• 'OR' 조건을 별도의 실행단위로 분리하여 액세스 경로를 수립하여 이를 연결하는 실행계획
• 'OR' 조건이 처리주관 조건의 역할을 하는 경우

 

• 1개의 테이블에 OR 조건이 있을 경우의 실행계획

 CONCATENATION
   TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1'
      INDEX (RANGE SCAN) OF 'B_IDX' (NON-UNIQUE)
   TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1'
      INDEX (RANGE SCAN) OF 'A_IDX' (NON-UNIQUE)

• 실행계획 예

SQL문 예

SELECT o.header_id, i.line_id, i.revenue_amount
FROM order_item i, orders o
WHERE i.item_group = :b1
   AND (o.s_order_id=i.order_id

      OR o.m_order_id=i.order_id);

 

• 실행계획 예 1 : USE_CONCAT 힌트를 사용한 경우

Execution Plan

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

SELECT STATEMENT
  CONCATENATION
    NESTED LOOPS
      TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
        INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX1' (NON-UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
        INDEX (UNIQUE SCAN) OF 'ORDER_PK' (UNIQUE)
    NESTED LOOPS
      TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
        INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX1' (NON-UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
        INDEX (UNIQUE SCAN) OF 'ORDER_U1' (UNIQUE)

• 실행계획 예 2 : NO_EXPAND 힌트를 사용한 경우

Execution Plan

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

SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
      INDEX (RANGE SCAN) OF 'ORDER_ITEM_IDX1' (NON-UNIQUE)
    TABLE ACCESS (FULL) OF 'ORDERS'

 

 

• 연쇄(CONCATENATION) 적용하지 말아야 할 경우
1. 조인의 연결고리가 'OR' 조건을 가질 때 조인의 상대방이 넓은 처리범위를 가질 때
2. 동일 컬럼의 'OR' 조건 : 이때는 IN-List 탐침이 유리하기 때문이다
3. 보다 효율적으로 처리범위를 줄일 수 있는 다른 액세스 경로가 있을 때
4. 'OR' 조건들 중에서 너무 넓은 처리범위를 가진 것들이 존재할 때

 

 

 3.2.3.3 원격(Remote) 실행계획

• 분산되어 있는 다른 데이터베이스의 테이블을 링크를 통한 액세스
• 물리적 : 다른 데이터베이스  /  논리적 : 같은 데이터베이스로 인식
• 원격 실행계획에서 옵티마이져 성향
• 원격 테이블의 실행계획은 나타내지 않음 (라이브러리 캐시를 통해 얻을 수 있음)

◆ SQL 예

SELECT c.customer_name, count(*)
FROM customers c, orders@crm_db o
WHERE c.cust_id = o.cust_id

  AND o.order_number = :b1
GROUP BY c.customer_name;

 

Execution Plan

-------------------------------------------------------------------------------------------
SELECT STATEMENT
  SORT(GROUP BY)
    NESTED LOOPS
      REMOTE
        TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
          INDEX (RANGE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)

-------------------------------------------------------------------------------------------
SELECT "ORDER_NUMBER", "CUST_ID"

FROM "ORDERS" "O"

WHERE "ORDER_NUMBER"=:"SYS_B_0";

 

원격 테이블이 선행 집합이 되어 외측 루프를 수행하고 로컬 테이블이 내측 루프를 수행하고 있다.

 

 

3.2.3.4. 정렬 처리(Sort Operation) 실행계획 

• 정렬작업의 종류
1. SORT (UNIQUE)
2. SORT (AGGREGATE)
3. SORT (GROUP BY)
4. SORT (JOIN)
5. SORT (ORDER BY)

 

1. SORT (UNIQUE)
설명 : 추출된 로우에 대하여 유일한 집합을 생성하는 작업

 

• SORT (UNIQUE) 정렬이 일어나는 경우
• DISTINCT 함수를 사용했을 때
• 서브쿼리에서 제공자 역할을 할 때
  

2. SORT (AGGREGATE)

• GROUP BY를 하지 않은 상태에서 전체 대상에 대해 그룹함수로 계산을 할 때 나타난다.
  (그룹함수 : SUM, COUNT, MIN, MAX, AVG)
• 일반적인 정렬을 하지 않는다.
 

3. SORT (GROUP BY)

• GROUP BY 구문에 의해 정렬을 수행한다.
• 데이터 용량이 많을 경우 정렬 작업에 부담이 생김   ☞  'HASH(GROUP BY)' 사용
  (해쉬는 대용량 처리시 정렬보다 유리하다)
• 인덱스와 키값이 동일할 경우 정렬이 필요 없다.  ☞  실행계획에 SORT(GROUP BY NOSORT) 나타남

3.2.3.5. 집합 처리(Set Operations) 실행계획

1. 합집합(Union, Union-All)
2. 교집합(Intersection)
3. 차집합(Minus)

 

• 합집합(Union, Union All) 실행계획
• 서로 다른 쿼리에서 처리한 결과를 다시 모음

◆ SQL 예

SELECT order_id, order_date, status
FROM orders
WHERE order_date > :b1

   OR order_id IN (SELECT order_id

                          FROM order_item
                          WHERE item_group='A001');

 

Execution Plan

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

SELECT STATEMENT
FILTER
TABLE ACCESS (FULL) OF 'ORDERS'
TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEM'
    INDEX (RANGE SCAN) OF 'ODER_ITEM_IDX2' (NON-UNIQUE)

 

◆ UNION 연산자를 사용하여 개선한 경우

SELECT order_id, order_date, status
FROM orders
WHERE order_date > :b1

UNION

SELECT order_id, order_date, status

FROM order_item

WHERE order_id IN (SELECt order_id

                             FROM  order_item

                             WHERE item_group = 'A001');

 

Execution Plan

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

SELECT STATEMENT
  SORT(UNIQUE)
    UNION-ALL
      TABLE ACCESS (BY INDEX ROWID) OF orders
        INDEX (RANGE SCAN) OF order_index
      NESTED LOOPS
        VIEW
          SORT(UNIQUE)
            TABLE ACCESS (BY INDEX ROWID) OF order_item
              INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)
        TABLE ACCESS (BY INDEX ROWID) OF orders
          INDEX (UNIQUE SCAN) OF order_pk_index (UNIQUE)

 

•  교집합(Intersection) 실행계획
• 양쪽 집합 모두에 속하는 공통집합을 구한다.(최대 공약수 집합)

◆ SQL 예

SELECT resist_no FROM employees WHERE dept_no = :b1
INTERSECT
SELECT social_no FROM billboard WHERE cre_date = :b2

 

Execution Plan

-------------------------------------------------------------------------------------------
SELECT STATEMENT
  INTERSECTION
    SORT (UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF employees
        INDEX (RANGE SCAN) OF employee_index(NON-UNIQUE)     => 유일하게 정렬
    SORT (UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF billboard
        INDEX (RANGE SCAN) OF billboard_index(NON-UNIQUE)       => 유일하게 정렬

 

• 차집합(Minus) 실행계획
• 한쪽의 집합을 기준으로 다른 집합의 요소들을 제거

◆ SQL 예

SELECT resist_noFROM employees WHERE dept_no = :b1
MINUS
SELECT social_noFROM billboard WHERE cre_date = :b2

 

Execution Plan

-------------------------------------------------------------------------------------------
SELECT STATEMENT
  MINUS
    SORT (UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
        INDEX (RANGE SCAN) OF 'EMPLOYEES_IDX2' (NON-UNIQUE)    =>   유일하게 정렬
    SORT (UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF 'BILLBOARD'
        INDEX (RANGE SCAN) OF 'BILLBOARD_IDX1' (NON-UNIQUE)    =>  유일하게 정렬

 

3.2.3.6. COUNT(STOPKEY) 실행계획

• 조건절에 ROWNUM을 사용했을 경우 발생

 

ROWNUM은 가상 컬럼이라 부르며 테이블에 존재하지 않고 DBMS가 제공한 컬럼이다

가상 컬럼 : SYSDATE, USER, ROWID, LEVEL(CONNECT BY 사용시)

 

◆ SQL 예

SELECT *
FROM orders
WHERE order_date = :b1

    AND acc_deptno = :b2

    AND ROWNUM <= 20;

Execution Plan

-------------------------------------------------------------------------------------------
SELECT STATEMENT
  COUNT (STOPKEY)
    TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS'
      INDEX (RANGE SCAN) OF 'ORDER_IDX2' (NON-UNIQUE)