* 한글문서를 복사하다보니 편집대로 복사가 안되었습니다.

   (첨부파일에 한글 문서 첨부하였습니다. WIKI 쪽에 붙일떄는 제대로 옮기죠)

   3-2-2-데이터연결을위한실행계획-4.hwp

 

3.2.2. 데이터 연결을 위한 실행계획

* 주요 내용은 조인, 서브쿼리 등 여러 개의 테이블이 연결될 때 실행계획에 대한 설명

  (일반 조인연산) Nested Loop Join, Sort Merge Join, Hash Join

  (특별 조인연산) Semi Join, Cartesian Join, Outer Join, Index Join

* 조인을 위한 힌트절

   조인 알고리즘힌트문

   Nested LoopUSE_NL(table1, table2)

  Sort MergeUSE_MERGE(table1, table2)

  Hash JoinUSE_HASH(table1, table2)

 

3.2.2.1. Nested Loop Join (내포 조인, 중첩루프 조인)

- 기본적인 조인 형태이다.

- 프로그램 구조

  NestedLoops

    { (outer loop) - 선행테이블(Driving Table)

       (inner loop) - 후행테이블(Driven Table)

       (process join)

     }

- 실행계획 분석

  SELECT STATEMENT

    NESTED LOOPS

      TABLE ACCESS (CLUSTER) OF table1 (outer loop)

        INDEX (RANGE SCAN) OF table1index.

      TABLE ACCESS (BY ROWID) OF table2(inner loop)

        INDEX (RANGE SCAN) OF table2index

- Advanced Nested Loop Join

1:M 테이블의 M쪽 테이블의 ROWID를 찾아 테이블을 한 번에 접근하는 방법.

TABLE ACCESS (BY INDEX ROWID) OF table1

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF table2(outer loop)

INDEX (RANGE SCAN) OF table2index(NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF table1(inner loop)

INDEX (RANGE SCAN) OF table1index(NON-UNIQUE)

3.2.2.2. Sort Merge Join(정렬병합조인)

- Nested Loop 조인에서 적절한 인덱스가 존재하지 않아서 조인의 대상 범위가 넓을 때는 랜덤 액세스로 인해 비효율적인 점을 개선하기 위한 방법이다.

- 두 집합을 스캔하여 정렬한 후 머지한다.

- 대용량을 처리해야하는 경우는 정렬의 부담이 크기 때문에 이 방법보다는 해시조인 방법을 사용한다.

- 조인 연결고리 연산자가 ‘=’이 아닌 경우(LIKE, BETWEEN, 부등호)일 경우 Nested Loop 조인 보다 유리하며 해시조인은 이 경우 사용할 수 없다.

- 정렬된 집합이 준비되어있거나 인덱스를 이용하여 정렬이 가능할 때 유리하다.

- Sort Merge 조인의 실행계획 예(한쪽을 인덱스 정렬)

MERGE JOIN

SORT(JOIN)

TABLE ACCESS (BY ROWID) OF table1

INDEX (RANGE SCAN) OF table1index

SORT(JOIN)

TABLE ACCESS(FULL) OF table2

- Sort Merge 조인의 실행계획 예(한쪽을 정렬된 인덱스 사용)

MERGE JOIN

TABLE ACCESS (BY ROWID) OF table1

INDEX (RANGE SCAN) OF table1index

SORT(JOIN)

TABLE ACCESS(FULEE) OF table2index

3.2.2.3. Hash Join(해시 조인)

- 해싱함수를 이용한 조인, 동등조인(=)일 경우만 사용 가능

- Hash Join의 실행계획 예(인덱스 처리된 스캔과 전체테이블 스캔)

HASH JOIN

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF table1index

TABLE ACCESS(FULEE) OF table2

- Hash Join의 실행계획 예(인라인뷰와 전체테이블을 해시조인한 후 다른 테이블 해시조인)

HASH JOIN

HASH JOIN(outer)

VIEW

SORT(GROUP BY)

INDEX (RANGE SCAN) OF BPM01T_index(NON-UNIQUE)

TABLE ACCESS(FULEE) OF BPM50T

INDEX (RANGE SCAN) OF BPM38T_index(NON-UNIQUE)

3.2.2.4. Semi Join(세미 조인)

- 조인의 정의에서 자연조인에서 한쪽 릴레이션의 속성은 제거되고 다른 한쪽의 속성만 나타나는 조인연산으로 주로 서브질의에 의한 조인을 말한다.

- 세미조인의 관계대수 정의

R(X), S(Y)의 조인 애트리뷰트를 Z=X∩Y라 하면, R?S= R?N(∏X∩Y(S) = ∏X(R?S)

- 1:M의 조인에서 서브질의의 결과가 1인 경우 예

(SQL 문)

SELECT EMP_ID, EMP_NM, AMT

FROM BPM01T

WHERE ORG_CD=:org_cd

AND PAY_CD IN ( SELECT PAY_CD FROM BPM40T WHERE ...) (실행계획)

NESTED LOOPS

VIEW

SORT(unique)

TABLE ACCESS (BY ROWID) OF BMP40T

INDEX (RANGE SCAN) OF ..

TABLE ACCESS(BY ROWID) OF BPM01T.

INDEX (RANGE SCAN) OF ..

3.2.2.5 Cartesian Join(카티젼조인)

- 조인되는 2 테이블간에 공통 속성이 없어서 조인연산이 카티젼곱으로 계산되는 경우이다.

- (실행계획의 예)

MERGE JOIN(CARTESIAN)

VIEW

SORT(GROUP BY)

TABLE ACCESS (FULL) OF table1

SORT(JOIN)

INDEX (RANGE SCAN) OF tableindex (UNIQUE)

- 카티젼 조인이 나타나는 경우

(1) 2개의 테이블을 합하기 위하여 고의적으로 카티젼조인을 할 경우

(2) 스타조인을 하기 위하여 디멘전 테이블을 조인하는 경우

(3) 3개 이상 테이블 조인시 조인 순서 잘못으로 나타난다.

3.2.2.6. Outer Join(아우터 조인)

- 아우터조인은 조인에 참여하지 못하는 테이블도 결과에 포함시킬 경우 사용하는 조인이다.

[1] Nested Loop 아우터조인

- 아우터 조인의 아우터루프가 결정되기 때문에 조인시 신중해야 한다.

- 힌트는 USE_NL(table1, table2)와 동일하지만 실행계획에는 NESTED LOOPS(OUTER)로 나타난다.

[2] Hash 아우터조인

- Nested Loop 조인으로는 용량이 많아 부담되는 경우 사용한다.

- 내측 조인 집합이 해시테이블로 생성되어 연결된다.

- (실행계획 예)

HASH(GROUP BY)

HASH JOIN(OUTER)

TABLE ACCESS (FULL) OF table1 (outer table)

INDEX(RANGE SCAN) OF tableindex (inner table)

- 조인뷰와 조인인라인뷰와 아우터조인 수행 - 뷰의 병합이나 조건절 진입이 허용되지 않고 뷰의 전체 집합이 독립적으로 수행된 결과와 아우터조인을 수행한다(실행 예 참조)

[3] SORT Merge 아우터조인

- Nested Loop 조인으로 문제가 있을 때 사용

[4] 전체 아우터조인

- 양쪽 데이블 모두에 아우터 조인을 실행할 경우

- 실행계획의 예

SELECT STATEMENT

VIEW

UNION-ALL

HASH JOIN(OUTER)

TABLE ACCESS (FULL) OF emp

TABLE ACCESS (FULL) OF employee

HASH JOIN(ANTI)

TABLE ACCESS (FULL) OF employee

TABLE ACCESS (FULL) OF emp

(주의) : (+) 연산자를 사용하여 아우터조인시 주의할 점 - 내측테이블에 조건을 기술할 경우(p230 참조)

3.2.2.7 인덱스조인

- 인덱스간의 해시조인을 통하여 액세스하는 방법이다.

3.2.3. 연산 방식에 따른 해시조인

3.2.3.1. IN-List 탐침 실행계획

- IN 사용예(WHERE 조건)

SELECT order_id, order_type, order_amount

FROM orders

WHERE order_type IN (1,2,3)

- IN 실행계획 예 - IN 조건에 있는 비교만큼 반복한다.

SELECT STATEMENT

INLIST ITERATOR

TABLE ACCESS (BY INDEX ROWID) OF table

INDEX (RANGE SCAN) OF tableindex (NON-UNIQUE)

- IN을 사용해도 비교값이 하나인 경우 ITERATOR가 안나타난다(그런경우가 있을까요?)

- 조인에서 나타나는 INLIST 실행계획 - table2의 속성에 IN 조건이 있을 경우

SELECT STATEMENT

NESTED LOOPS

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF tabelindex (NON-UNIQUE)

INLIST ITERATOR

TABLE ACCESS (BY INDEX ROWID) OF table2

INDEX (RANGE SCAN) OF table2index (NON-UNIQUE)

3.2.3.2. Concatenation(연쇄) 실행계획

- OR 조건으로 연결된 경우(WHERE 조건)

- 실행계획 예(1개의 테이블에 OR 조건이 있을 경우)

CONCATENATION

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF table1index1 (NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF table1

INDEX (RANGE SCAN) OF table1index2 (NON-UNIQUE)

- 실행계획 예(조인의 연결고리에 OR 조건이 있을 경우)

(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 힌트를 사용한 경우)

SELECT STATEMENT

CONCATENATION

NESTED LOOPS

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_index (UNIQUE)

NESTED LOOPS

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_index (UNIQUE)

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

SELECT STATEMENT

HASH JOIN

TABLE ACCESS (BY INDEX ROWID) OF order_item

INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)

TABLE ACCESS (FULL) OF orders

- OR 조건이 사용될 경우 CONCAT 실행계획이 불리한 경우

(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 BYc.customer_name;

(실행계획의 예 : REMOTE)

SELECT STATEMENT

SORT(GROUP BY)

NESTED LOOPS

REMOTE

TABLE ACCESS (BY INDEX ROWID) OF customers

INDEX (RANGE SCAN) OF customer_index (UNIQUE)

- 원격테이블 액세스에서 문제는 원격테이블을 랜덤액세스하거나 원격테이블이 조인의 내측에 있어서 액세스를 빈번히 해야하는 경우이다.

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

- 정렬작업의 종류

SORT(UNIQUE), SORT(AGGREGATE), SORT(GROUP BY), SORT(JOIN), SORT(ORDER BY)

(1) SORT UNIQUE

- SELECT 문에서 DISTINCT를 사용했을 경우

- subqyery에서 IN 다음에 사용되는 부속질의 결과

(2) SORT AGGREGATE

- GROUP BY 문을 사용하지 않은 상태에서 SUM, COUNT, MIN, MAX, SUM을 사용했을 때

- 실제 SORT를 하지는 않고 결과 값을 구한다.

(3) SORT(GROUP BY)

- GROUP BY 계산을 위하여 정렬을 수행한다.

- 데이터 용량이 많을 경우 HASH(GROUP BY)를 사용한다.

- 인덱스가 키값으로 정렬되어있는 경우 정렬이 필요없으므로 SORT(GROUP BY NOSORT)로 나타난다.

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

(1) 합집합(UNION 연산자)

- UNION과 UNION ALL (UNION은 최소공배수 집합을 구한다)

- 실행계획 예 1(OR를 사용할 경우 비효율적인 사례)

(SQL문의 예)

SELECT order_id, order_date, status

FROM orders o

WHERE order_date > :b1 OR

order_id IN (SELECT order_id FROM order_item

WHERE item_group='A');

(실행계획의 예 : full table 스캔이 일어난다)

SELECT STATEMENT

FILTER

TABLE ACCESS (FULL) OF orders

TABLE ACCESS (BY INDEX ROWID) OF order_item

INDEX (RANGE SCAN) OF order_item_index (NON-UNIQUE)

- 실행계획 1을 UNION 연산자를 사용하여 개선한 경우

(SQL문의 예)

SELECT order_id, order_date, status

FROM orders

WHERE order_date > :b1

UNION

SELECT order_id, order_date, status

FROM orders

WHERE order_id IN

(SELECT order_id FROM order_item WHERE item_group='A');

(실행계획의 예 2 : UNION-ALL 사용)

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)

(2) 교집합(INTERSECTION 연산자)

- 2집합을 정렬하여 머지한다.

- 실행계획 예

(SQL문의 예)

SELECT resist_noFROM employees

WHERE dept_no=:b1

INTERSECT

SELECT social_noFROM billboard

WHERE cre_date=:b2

(실행계획의 예)

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)

(3) 차집합(MINUS 연산자)

- 실행계획 예

(SQL문의 예)

SELECT resist_noFROM employees

WHERE dept_no=:b1

MINUS

SELECT social_noFROM billboard

WHERE cre_date=:b2

(실행계획의 예)

SELECT STATEMENT

MINUS

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)

3.2.3.6. COUNT(STOP) 실행계획

- ROWNUM을 사용했을 때 나타난다.(2부의 부분처리 내용 참조)

- ROWNUM은 SQL 문을 수행한 결과에 사용되는 가상의 일련번호이다.

- (*) 가상컬럼 : SYSDATE, USER, ROWID, LEVEL(connect by 사용시)

- 실행계획 예 1: ROWNUM을 이용한 결과 튜플 수 20개로 제한

(SQL문의 예)

SELECT *

FROM orders

WHERE order_date=:b1 AND acc_deptno=:b2 AND ROWNUM <=20;

(실행계획)

SELECT STATEMENT

COUNT(STOPKEY)

TABLE ACCESS (BY INDEX ROWID) OF orders

INDEX (RANGE SCAN) OF order_index(NON-UNIQUE)

- 실행계획 예 2: ROWNUM을 이용한 결과 튜플 수 1개로 제한

(SQL문의 예)

SELECT /*+ index-desc(s, subject_prgs_pk) */ subject_seq

FROM subject_prgs s

WHERE subject_id=:b1 AND subject_id <= :b2 AND ROWNUM=1;

(실행계획)

COUNT(STOPKEY)

INDEX (RANGE SCAN DESCENDING) OF subject_prgs_pk(UNIQUE)

- 실행계획 예 3: 복잡한 ROWNUM 예 /*?*/는 SQL문의 해당되는 처리 위치

(SQL문의 예)

SELECT MIN(DECODE(ROWNUM,1,rnum2)), plan_time

FROM (SELECT rnum * - 1 rnum2, MIN(plan_time) plan_time

FROM (SELECT ROWNUM rnum1, plan_time/*1*/

FROM subjects

WHERE role_cd='1007'

GROUP BY plan_time, ROWNUM

GROUP BY rnum * -1)/*2*/

WHERE ROWNUM <= 10;/*3*/

(실행계획)

COUNT(STOPKEY)/*3*/

VIEW

SORT(GROUP BY STOPKEY)

VIEW

SORT(GROUP BY)/*2*/

COUNT/*1*/

TABLE ACCESS (BY INDEX ROWID) OF subjects

INDEX (RANGE SCAN) OF subject_index(NON-UNIQUE)