SSISO Community
해쉬 함수 (Hash function) 스캔을 위한 실행계획 Data Access Pattern - ORACLE ORACLE 9 i 개발자 튜닝가이드 v0.92 드라이빙(Driving) 순서규칙 조인의 최적화 ORACLE Hint 정리 1.3 인덱스의 선정 1.1 인덱스의 구조 대용량 솔류션

시소당

조인의 최적화

2. 조인의 최적화

  • 최적의 엑세스 경로를 찾고 RDB의 특성이 최대한 발휘되도록 SQL을 구사하여 옵티마이져가 최적의 처리경로를 찾을 수 있도록 해야 한다.
  • 인덱스, 클러스터 등의 옵티마이징 펙터(Optimizing Factor)를 적절히 지정하고 경우에 따라서는 힌트나 사용제한 기능을 사용한다.

2.1 조인효율 향상의 원리

  • 옵티마이져는 가장 효율적인 조인의 경로를 찾아 줄 뿐이지 새롭게 없는 경로를 생성해 주는 것이 아니다.

가. 조인 순서

  • 조인의 효율은 조인되어야 할 각 집합의 처리범위와 순서에 따라 영향을 받는다. 즉, 처리범위가 가장 좁은 범위를 먼저 처리할 수록 조인의 효율은 증가한다.
  • 조인의 순서에 영향을 미치는 것은 Nested Loop 조인에서 발생한다.
    조인은 1:1로 이루어 진다.
    1) TABLE1의 처리 범위인 10,000 로우의 첫번째 로우를 읽는다.
    2) 읽혀진 TABLE1의 값에 대응하는 TABLE2의 로우를 찾는다
    3) TABLE2의 조인 컬럼값과 대응되는 TABLE3의 로우를 찾는다.
    4) TABLE1의 처리범위가 모두 끝날 때까지 계속한다.
    => 최소 10,000회 이상ACCESS    		 1) TABLE3의 처리 범위인 2개 로우의 첫번째 로우를 읽는다.
    2) 읽혀진 TABLE3의 값에 대응하는 TABLE2의 로우를 찾는다
    3) TABLE2의 조인 컬럼값과 대응되는 TABLE1의 로우를 찾는다.
    4) TABLE3의 두번째 로우를 읽어 위의 작업을 반복한다.
    => 최대 4회 이하ACCESS

    <대용량데이터베이스 솔루션1 84page 그림참고>

  • 처리범위는 주어진 조건을 만족하는 로우수만을 의미하는 것이 아니라 그 조건의 범위를 찾는데 사용되는 주처리(Main Driving)영역의 액세스 범위를 의미한다.
  • Nested Loop 조인에서는 가장 먼저 수행되는 집합의 처리범위가 전체 일량을 좌우한다. 따라서 가장 적은 처리 범위를 가진 테이블이 먼저 수행되도록 하면 최적의 조인을 구현할 수 있다.

나. 조인 성공률

  • 조인의 성공률이란 조인의 작업이 수행한 후 조인 조건을 만족하는 총 로우수(성공한 로우수)를 의미
    조인은 M:1로 이루어 진다.
    1) TABLE1의 처리 범위인 100 로우의 첫번째 로우를 읽는다.
    2) 읽혀진 TABLE1의 값에 대응하는 TABLE2의 로우를 찾는다
    => TABLE1과 TABLE2는 1:10의 비율로 되어 있으며 100%연결에 성공했다고 가정
    3) TABLE1과 TABLE2의 연결작업에 성공한 각각의 로우들에 대응하는 TABLE3의 로우를 찾는다.
    4) TABLE1의 두번째 로우를 읽어 위의 작업을 반복한다.
    => 2)번 연결작업에서 성공한 로우수 1000건, 3)번 연결 작업이 1000회
    총 2000회의 연결작업 수행    		 1) TABLE1의 처리 범위인 100 로우의 첫번째 로우를 읽는다.
    2) 읽혀진 TABLE1의 값에 대응하는 TABLE3의 로우를 찾는다.
    => 2개의 로우만 성공 했다고 가정
    3) 위의 작업에서 성공한 로우들에 대응하는 TABLE2의 로우를 찾는다.
    4) TABLE1의 두번째 로우를 읽어 위의 작업을 반복한다.
    => TABLE1과 TABLE3의 연결작업 100회, 연결에 성공한 결과와 TABLE2간의 연결작업 2회
    총 102회의 연결작업 수행

    <대용량데이터베이스 솔루션1 86page 그림참고>

  • 두번째 처리되는 테이블을 어느 것으로 하느냐에 따라 세번째 처리할 일량에 차이가 난다.

다. 연결고리 상태

(1) 연결고리 정상
  • 조건절에 기술되는 연결컬럼에 인덱스가 모두 존재하고 있는 상태

    <대용량데이터베이스 솔루션1 89,90page 그림참고>
    1번의 경우
    1) TAB1의 처리 범위인 첫번째 로우를 읽는다.
    2) TAB2의 연결고리가 되는 인덱스가 있으므로 TAB1의 연결고리의 값에 대응되는 로우를 KEY2에서 찾는다.
    3) KEY2에 있는 ROWID를 이용하여 TAB2를 읽는다.
    4) TAB1의 두번째 로우를 읽어 위의 작업을 반복한다.
    2번의 경우
    1) TAB2의 처리 범위인 첫번째 로우를 읽는다.
    2) TAB1의 연결고리가 되는 인덱스가 있으므로 TAB2의 연결고리의 값에 대응되는 로우를 KEY1에서 찾는다.
    3) KEY1에 있는 ROWID를 이용하여 TAB1를 읽는다.
    4) TAB2의 두번째 로우를 읽어 위의 작업을 반복한다.
  • 연결고리가 정상인 상태에서는 어느 방향으로 연결작업이 수행되든 간에 발생되는 연결작업은 동일하다.
    연결고리가 정상인 상태에서는 먼저 처리범위를 줄여 주는 테이블이 먼저 처리되면 수행속도가 향상된다.
    SELECT a.FLD1, ........., b.FLD1, .........
   FROM TAB2 b, TAB1 a
  WHERE a.KEY1 = b.KEY2
    AND b.FLD2 LIKE 'ABC%'
    AND a.FLD1 = '10'
    b.FLD2 LIKE 'ABC%'를 만족하는 범위는 10로우,
    a.FLD1 = '10'를 만족하는 범위는 200 로우라고 가정.
    1)FLD1 인덱스에서 FLD1 = '10'인 범위의 첫번째 로우를 찾아 인덱스에 있는 ROWID를 이용하여 TAB1의 로우를 찾는다.
    2)KEY1의 값이 상수가 되었으므로 TAB2의 KEY2 인덱스를 이용해 KEY1의 상수값과 대응되는 로우를 찾는다. 연결에 성공했다면 KEY2 인덱스에 있는 ROWID를 이용하여 TAB2의 로우를 찾는다
    3)상수값이 된 FLD2의 값이 LIKE 'ABC%'를 만족하는 지를 확인하여 만족하는 로우만 운반단위로 보낸다.
    4)다시 TAB1의 두번째 로우를 읽어 위의 작업을 반복한다.
    이 반복작업이 멈추는 경우는
    ① TAB1의 처리범위인 200 로우를 모두 처리 한 경우
    ② 부분범위처리라면 추출된 로우가 운반단위에 도달하는 경우
    최소 200회 이상 처리    		 1)FLD2 인덱스에서 LIKE 'ABC%'인 범위의 첫번째 로우를 찾아 인덱스에 있는 ROWID를 이용하여 TAB2의 로우를 찾는다.
    2)KEY2의 값이 상수가 되었으므로 TAB1의 KEY1 인덱스를 이용해 KEY2의 상수값과 대응되는 로우를 찾는다. 연결에 성공했다면 KEY1 인덱스에 있는 ROWID를 이용하여 TAB1의 로우를 찾는다
    3)상수값이 된 FLD1의 값이 '10'을 만족하는 지를 확인하여 만족하는 로우만 운반단위로 보낸다.
    4)다시 TAB2의 두번째 로우를 읽어 위의 작업을 반복한다.
    이 반복작업이 멈추는 경우는
    ① TAB2의 처리범위인 10 로우를 모두 처리 한 경우
    ② 부분범위처리라면 추출된 로우가 운반단위에 도달하는 경우
    최대 20회 이내 처리

    <대용량데이터베이스 솔루션1 92,93page 그림참고>

    • 처리 범위가 적은 테이블을 먼저 액세스하면 처리할 양이 줄어든다.
    • 만약 부분범위처리가 되고 운반단위를 빨리 채울 수만 있다면 넓은 범위의 처리가 먼저 일어나더라도 수행속도는 빨라진다.
    • 특정순서로 정렬하기를 원하며 부분범위처리가 가능하다면 넓은 범위부터 처리해도 수행속도는 보장받을 수 있다.

    사용자가 어느 방향이 먼저 처리되는 것이 유리한지를 알고 있다면 힌트나 사용제한 기능을 이용하여 튜닝을 하라

    SELECT a.FLD1, ........., b.FLD1, .........
   FROM TAB2 b, TAB1 a
  WHERE a.KEY1 = b.KEY2
    AND b.FLD2 LIKE 'ABC%'
    AND RTRIM(a.FLD1) = '10'
    		 
    SELECT /*+ ORDERED */
        a.FLD1, ........., b.FLD1, .........
   FROM TAB2 b, TAB1 a
  WHERE a.KEY1 = b.KEY2
    AND b.FLD2 LIKE 'ABC%'
    AND a.FLD1 = '10'
(2) 한쪽 연결고리 이상
  • 어느 한쪽의 연결고리에 인덱스(혹은 클러스터)가 없는 상태
    1) TAB1에서 처리해야 할 범위의 첫번째 로우를 읽는다.
    2) TAB2의 KEY2에는 인덱스가 없으므로 TAB2를 전부 스캔하여 'KEY2=KEY1'을 만족하는 로우를 찾는다.
    3) 값을 찾더라도 멈추지 않고 테이블 끝까지 스캔한다.
    4) TAB1의 두번째 로우를 읽어 TAB2의 전체 테이블 스캔을 한다.(TAB1의 처리범위가 끝날 때까지)    		 1) '연결고리 정상'상태의 두번째 그림과 동일함.(TAB2는 좌측과 달리 한번만 액세스 된다.)
    ※ 연결고리가 어느 한쪽에 이상이 있는 경우는 이상이 발생한 테이블을 반드시 먼저 처리해야한다.
    옵티마이져는 연결고리의 어느 한쪽에 인덱스가 없으면 무조건 없는 쪽을 먼처 처리하도록 실행계획을 수립한다.

    <대용량데이터베이스 솔루션1 95,96page 그림참고>

    연결고리 이상상태를 발생시키는 유형
    • 조인되는 컬럼이 1:1로 대응되지 않는 경우 
      SELECT ..., columns, ...
  FROM TABLE1, TABLE2
 WHERE A || B || C = D
-- TABLE1의 컬럼들이 사용제한 되어 TABLE1이 먼저 액세스
      		 
      SELECT ..., columns, ...
  FROM TABLE1, TABLE2
 WHERE A = substr(D,1,2)
   AND B = substr(D,3,1)
   AND C = substr(D,4,3)
-- TABLE2의 컬럼이 사용제한 되어 TABLE2가 먼저 액세스
    • 데이터 타입의 차이에 의해 발생되는 경우
양쪽 연결고리 이상
  • 양쪽 연결고리 모두에 인덱스가 없는 경우
  • 옵티마이져는 연결고리에 영향을 받지않는 방식인 Sort Merge 방식으로 실행계획 수립

2.2 조인의 튜닝(Tuning)

  • 옵티마이져는 단지 액세스 경로를 찾아 줄 뿐이지 없는 경로를 새롭게 생성해 주는 것이 아니다.
  • 최적의 처리경로를 옵티마이져의 판단과는 상관없이 알수 있어야 한다.
  • 옵티마이져가 정확하게 그 경로를 찾아 주는 지를 판단할 수 있어야 한다.
  • 옵티마이져는 중요한 몇 개의 자료를 토대로 통계학적으로 분석하여 가능한 보다 높은 확률로 양호한 액세스 경로를 판단할 수 밖에 없다.
  • 인덱스, 클러스터 등이 어떻게 지정되어 있느냐에 따라 많은 차이가 발생한다.

    <대용량데이터베이스 솔루션1 99page 그림참고>
  • TAB1 -> TAB2 -> TAB3
    • TAB1: 상수값을 가진 칼럼(x.A2='10')의 첫번째 로우를 읽는다. => TAB1의 모든 컬럼 값은 상수가 된다.
    • TAB2: y.B1=x.A1, y.b2 LIKE 'B%'의 조건으로 액세스 한다.
    • TAB3: z.C1=y.B2의 조건으로 액세스 된다.
  • TAB2 -> TAB3 -> TAB1
    • TAB2: y.b2 LIKE 'B%'의 조건으로 액세스 한다.
    • TAB3: z.C1=y.B2의 조건으로 액세스 된다. (y.B2가 상수값이 되었으므로)
    • TAB1: x.A1=y.B1, x.A2='10'의 조건으로 액세스된다. (y.B1이 상수값이 되었으므로)
  • TAB3 -> TAB2 -> TAB1
    • TAB3: z.C1=y.B2의 조건으로 액세스 해야 하나 y.B2가 미지수 이므로 전체 테이블 스캔한다.(TAB3의 첫번째 로우를 읽는 순간 모든 컬럼 값은 상수가 된다.)
    • TAB2: y.B2=z.C1, y.b2 LIKE 'B%'의 조건으로 액세스 한다.(z.C1이 상수값이 되었으므로)
    • TAB1: x.A1=y.B1, x.A2='10'의 조건으로 액세스된다. (y.B1이 상수값이 되었으므로)
튜닝의 절차
  • Nested Loop 조인이 유리한 지 Sort Merge 조인이 유리할 것인지를 판단.
  • 연결고리 상태 확인 (이상이 있다면 현 상태의 문제점을 확인하여 인덱스의 추가 여부 결정)
  • 연결고리를 제외한 컬럼들의 조건에 사용된 연산자와 인덱스의 상태를 비교하여 처리범위를 가장 많이 줄여 주는 조건을 찾는다.
  • 현재의 인덱스가 최소의 범위를 액세스할 수 있도록 구성되어 있는지 확인.
    특정한 액세스 유형을 위해 함부로 인덱스를 추가하게 되면 기존의 액세스에 영향을 미칠 수 있으므로 반드시 종합적인 판단을 해야한다.

2.3 조인과 반복연결(Loop Query)

가. 전체범위처리방식의 조인

1) TAB1에서 처리해야 할 전체범위를 하나씩 스캔하여 대응되는 TAB2의 로우를 연결한다.
2) TAB1에서 처리해야 할 로우의 범위가 1,000건이라면 1,000번의 연결작업이 랜덤 액세스방식으로 수행한다.
3) 연결된 로우들은 수행시킨 SQL에 따라 추가적인 2차 가공을 한 후 운반단위만큼 추출한다.
4) 1,000번의 연결을 위해 단 한번의 SQL이 수행한다.		 1) TAB1의 로우수에 대응하는 TAB2의 로우들을 랜덤으로 액세스한다.
2) 각각의 연결작업마다 별도의 SQL이 수행되어 연결작업에만 1,000번의 SQL이 수행된다.
3) 1,001의 SQL이 수행된다.

<대용량데이터베이스 솔루션1 104page 그림참고>

반복연결이 유리한 경우 1
SELECT a.FLD1, ........., b.FLD1, .........
  FROM TAB2 b, TAB1 a
 WHERE a.KEY1 = b.KEY2
   AND a.FLD1 = '10'
 ORDER BY a.FLD2
 
1) SELECT FLD1, ........., FLDn
     FROM TAB1
    WHERE FLD1 = '10'
    ORDER BY FLD2
 
2) SELECT COL1, ........., COLn
    FROM TAB2
   WHERE KEY2 = :a.KEY1

<대용량데이터베이스 솔루션1 106page 그림참고>

  • TAB1의 a.FLD1 = '10'인 전체범위를 대상으로 TAB2를 모두 연결한 후 그 결과를 정렬하여 운반단위만큼 추출한다.
  • 만약 만족하는 범위가 1,000로우라면 1,000회의 조인이 먼저 일어난 후 정렬작업이 수행된다.
  • TAB1만 전체범위처리를 하게 만들고, 운반단위만큼만 TAB2를 연결하도록 바꿀 수 있다.
  • 반복연결방식이 유리해지려면 연결되는 작업의 수행 횟수는 반드시 처리되어야 할 일량의 일부분이어야 한다.
반복연결이 유리한 경우 2
SELECT b.부서명, SUM(a.매출액)
  FROM TAB1 a, TAB2 b
 WHERE a.부서코드 = b.부서코드
   AND a.매출일 LIKE '9503%'
 GROUP BY b.부서명
 
1) SELECT 부서코드, sum(a.매출액)
     FROM TAB1
    WHERE 매출일 LIKE '9503%'
    GROUP BY 부서코드
 
2) SELECT 부서명
     FROM TAB2
    WHERE 부서코드 = :a.부서코드

<대용량데이터베이스 솔루션1 108page 그림참고>

  • SELECT List에 TAB1의 컬럼과 TAB2의 컬럼이 모두 존재하고 GROUP BY는 TAB2의 부서명으로 하지만 SUM은 TAB1의 매출액으로 하여야 한다.
  • 면저 두 개 테이블의 전체범위를 모두 조인한 후 GROUP BY를 하여 운반단위만큼 추출해야 한다.
  • TAB1 테이블을 먼저 부서코드로 GROUP BY 하여 그 결과를 10개의 로우로 줄인 다음, 각 로우에 대응하는 TAB2의 로우를 연결한다.

나. 부분범위처리방식의 조인

1) 단 한번의 SQL이 수행.
2) SQL내에서 조인된 테이블의 모든 컬럼을 마음대로 가공가능		 1)'운반단위 + 1'번의 SQL이 수행.
2) 별도의 언어를 통하여 추가적인 가공

<대용량데이터베이스 솔루션1 108page 그림참고>

  • 전체범위처리가 된 이유가 연결되는 모든 테이블에 원인이 있다면 조인이 빠르다.
  • 연결되는 테이블 중 하나를 부분범위처리로 바꿀 수 있다면 반복연결방식이 빠르다.
  • SQL이 부분범위처리가 된다면 조인이 빠르다.
쿼리에서의 활용
SELECT b.부서명, SUM(a.매출액)
  FROM TAB1 a, TAB2 b
 WHERE a.부서코드 = b.부서코드
   AND a.매출일 LIKE '9503%'
 GROUP BY b.부서명
 
SELECT b.부서명, a.매출액
  FROM (SELECT 부서코드, SUM(매출액)
          FROM TAB1
         WHERE 매출일 LIKE '9503%'
         GROUP BY 부서코드) a, TAB2 b
 WHERE b.부서코드 = a.부서코드

Nested Loop 조인과 Sort Merge 조인

  • 조인에는 Nested Loop 조인과 Sort Merge 조인이 있다. 최적화 측면에서 보면 'First_Rows'와 'All_Rows'의 개념이 있다.
  • 처리의 조건이나 인덱스의 구성에 따라 유리한 조인 방식이 있다.

가. Nested Loop 조인

  • 먼저 어떤 테이블의 처리범위를 하나씩 액세스하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식이다. 
    SELECT a.FLD1, ........., b.COL1, .........
  FROM TAB1 a, TAB2 b
 WHERE a.KEY1 = b.KEY2
   AND a.FLD1 = 'AB'
   AND b.FLD2 = '10'
    1) TAB1의 FLD1인덱스를 경유하여 FLD1 = 'AB'인 처리범위 중 첫번째 로우 액세스한다.
    2) FLD1인 인덱스에 있는 ROWID에 의해 TAB1로우를 액세스한다.
    3) TAB2의 KEY2인덱스를 이용하여 대응되는 인덱스 로우를 찾는다.
    4) KEY2인덱스에 있는 ROWID에 의해 TAB2의 로우를 액세스 한다.
    5) FLD2 = '10'의 값을 비교(check)하여 조건을 만족하면 최종적인 결과를 운반단위로 보낸다.
    6) FLD1 인덱스의 두번째 로우를 읽어 위의 작업을 반복하여 만약 부분범위처리가 가능하다면 운반단위가
    채워질 때까지만 수행되고 전체범위처리라면 FLD1범위가 끝날 때까지 계속(Nested)해서 반복(Loop) 수행한다.

    <대용량데이터베이스 솔루션1 111page 그림참고>

    특징

    1) 순차적으로 처리된다. 선행테이블(Driving table)의 처리범위에 있는 각각의 로우들이 순차적으로 수행될 뿐만 아니라 테이블 간의 연결도 순차적이다. (순차적)
    2) 먼저 액세스 되는 테이블의 처리범위에 의해 처리량이 결정된다. (선행적)
    3) 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스 된다. 즉, 자신에게 주어진 상수값에 의해 스스로 범위를 줄이는 것이 아니라 값을 받아서 처리범위가 정해진다. (종속적)
    4) 주로 랜덤액세스 방식으로 처리된다. 선행테이블의 인덱스 액세스는 첫번째 로우만 랜덤 액세스이고 나머지는 스캔이며 연결작업은 모두 랜덤 액세스이다. (랜덤액세스)
    5) 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라질 수 있다. (선택적)
    6) 연결고리가 되는 인덱스에 의해 연결작업이 수행되므로 연결고리 상태가 매우 중요하다. 연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 발생된다. (연결고리상태, 방향성)
    7) 연결작업 수행 후 마지막으로 CHECK되는 조건은 부분범위처리를 하는 경우에는 조건의 범위가 넓을 수록, 아예 없다면 오히려 빨라진다. (부분범위처리 가능)

    1) 부분범위처리를 하는 경우에 주로 유리해진다.
    2) 조인되는 어느 한쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리하다.
    3) 주로 처리량이 적은 경우 (많더라도 부분범위처리가 가능한 경우)에 유리하다. (처리방식이 주로 랜덤액세스이기 때문에 많은 양의 랜덤액세스에서는 수행속도가 당연히 나빠진다.)
    4) 가능한 한 연결고리 이상 상태를 만들지 않도록 주의해야 한다.
    5) 순차적으로 처리되기 때문에 어떤 테이블이 먼저 액세스 되느냐에 따라 수행속도에 영향을 미치므로 최적의 액세스 순서가 되도록 조치해야 한다.
    6) 부분범위처리를 하는 경우에는 운반단위의 크기가 수행속도에 많은 영향을 미칠 수 있다. 운반단위가 적을 수록 빨리 운반단위를 채울 수 있으나 패치(Fetch) 횟수에서는 불리해지는 이중성을 가지고 있다.
    7) 선행테이블의 처리범위가 많거나 연결테이블의 랜덤액세스의 양이 아주 많다면 Sort Merge조인보다 불리해 질 수 있다.

나. Sort Merge 조인

  • 양쪽 테이블의 처리범위를 가자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지해 가는 방식. 
    SELECT a.FLD1, ........., b.COL1, .........
  FROM TAB1 a, TAB2 b
 WHERE a.KEY1 = b.KEY2
   AND a.FLD1 = 'AB'
   AND b.FLD2 = '10'
    1) TAB1의 FLD1인덱스를 경유하여 FLD1 ='AB'인 범위를 차례로 액세스 하여 연결고리인 KEY1의 값으로 정렬해 둔다.
    2) TAB2도 FLD2인덱스를 경유하여 FLD2 = '10'인 범위를 차례로 액세스하여 연결고리인 KEY2의 값으로 정렬해 둔다.
    3) 두개의 정렬된 결과를 스캔하면서 KEY1 = KEY2를 만족하는 로우를 찾도록 머지하여 운반단위가 채워지면 추출한다.

    <대용량데이터베이스 솔루션1 113page 그림참고>

    • TAB1은 FLD1,TAB2는 FLD2인덱스만 사용.
    • 연결고리인 KEY1, KEY2의 인덱스는 전혀 사용되지 않았고 머지의 조건으로만 사용.
      특징

      1) 동시에 처리된다. 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다.(동시적)
      2) 각 테이블은 다른 테이블에서 어떠한 상수값도 제공받지 않고 자신에게 주어진 상수값만으로 범위를 줄인다.(독립적)
      3) 부분범위처리를 할 수 없으며 항상 전체범위처리를 한다.(전체범위처리)
      4) 주로 스캔방식으로 처리한다. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우엔 랜덤액세스 이고, 머지하는 작업은 스캔방식이다.(스캔방식)
      5) 주어진 조건에 있는 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않는다.(선택적)
      6) 조인의 방향과는 무관하다.(무방향성)
      7) 스스로 자신의 처리범위를 줄이기 위해서 사용되는 인덱스는 대개 가장 유리한 한가지만 사용된다. 그러나 그 외의 조건들은 비록 인덱스를 사용하지 못하더라도 머지할 작업대상을 줄여주는 역할을 하기 때문에 중요한 의미를 가진다.

      사용기준

      1) 전체범위처리를 하는 경우에 주로 유리해진다.
      2) 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있는 상태인 경우 주로 유리해 질 수 있다.
      3) 주로 처리량이 많은 경우(항상 전체범위처리를 해야 하는 경우)에 유리해진다. 처리방식이 스캔방식이기 때문에 많은 양의 랜덤 액세스를 줄일 수 있기 때문이다.
      4) 연결고리 이상 상태에 영향을 받지 않으므로 연결고리를 위한 인덱스를 생성할 필요가 없을 때 유용하다.
      5) 스스로 자신의 처리범위를 어떻게 줄이느냐가 수행속도에 많은 영향을 미친므로 보다 효율적으로 액세스 할 수 있는 인덱스 구성이 중요하다.
      6) 전체범위처리를 하므로 운반단위의 크기가 수행속도에 영향을 미치지 않는다. 가능한 운반단위를 크게 하는 것이 페치(Fetch) 횟수를 줄여준다.
      7) 처리할 데이터 량이 적은 온라인 애플리케이션에서는 Nested Loop조인이 유리한 경우가 많으므로 사용시 주의해야 한다.
      8) 옵티마이져 목표(Goal)가 'ALL_ROWS'인 경우는 Sort Merge조인으로 실행계획이 수립되므로 부분처리를 하고자 한다면 주의해야 한다.

다. 조인방식의 비교

SELECT a.FLD1, ........., b.COL1, .........
  FROM TAB1 a, TAB2 b
 WHERE a.KEY1 = b.KEY2
   AND a.FLD1 = 'AB'
b.FLD2 = '10' 조건을 없앰
1) TAB1의 FLD1 인덱스를 경유하여 FLD = 'AB'인 처리범위 중 첫번째 로우를 액세스한다.
2) FLD1 인덱스에 있는 ROWID에 의해 TAB1의 로우를 액세스한다.
3) TAB2의 KEY2 인덱스를 이용하여 대응 되는 인덱스 로우를 찾는다.
4) KEY2 인덱스에 있는 ROWID에 의해 TAB2의 로우를 액세스한다. 액세스된 로우의 모든 컬럼값들은 상수값이 되며 추가적인 조건이 없으므로 그대로 운반단위로 옮겨진다.
5) FLD1 인덱스의 두번째 로우를 읽어 위의 작업을 반복한다.
부분범위처리라면 오히려 체크 기능이 없어짐으로써 더 빨리 운반단위를 채울수 있다.		 1) TAB1은 FLD1 인덱스를 경유하여 FLD1 = 'AB'인 범위를 차례로 액세스하여 연결고리 인 KEY1의 값으로 정렬해 둔다.
2) TAB2는 전체 테이블을 차례로 액세스하여 연결고리인 KEY2의 값으로 정렬해 둔다.
3) 두개의 정렬된 결과를 스캔하면서 KEY1 = KEY2를 만족하는 로우를 찾도록 머지하여 운반단위가 채워지면 추출한다.
액세스 범위가 훨씬 증가했고 머지할 양 또한 많이 늘어나 일량이 훨씬 증가 되었다

<대용량데이터베이스 솔루션1 116,117page 그림참고>

SELECT a.FLD1, ........., b.COL1, .........
  FROM TAB1 a, TAB2 b
 WHERE a.KEY1 = b.KEY2
 ORDER BY a.FLD5, b.COL5
양쪽 조건을 모두 없앰
ORDER BY를 하여 전체 범위처리
Nested Loop Sort Merge
어느 한쪽이 먼저 전체 테이블 스캔을 하면서 읽혀진 KEY1의 상수값에 대응되는 KEY2 인덱스를 경유하여 TAB2를 찾게된다.
TAB2 전체 테이블을 대상으로 랜덤 액세스가 발생한다.
만일 ORDER BY를 사용하지 않았다면 부분범위처리를 하게 되므로 아주 빠른 수행속도를 보장한다.		 각각의 테이블에 대해 전체 테이블을 스캔하여 연결고리가 되는 컬럼으로 정렬하여 머지한다.
랜덤으로 전체 테이블을 액세스하는 것보다 스캔방식으로 전체 테이블을 액세스하는 것이 유리하다.

문서에 대하여

1567 view

4.0 stars

SSISO Community

해쉬 함수 (Hash function) 스캔을 위한 실행계획 Data Access Pattern - ORACLE ORACLE 9 i 개발자 튜닝가이드 v0.92 드라이빙(Driving) 순서규칙 조인의 최적화 ORACLE Hint 정리 1.3 인덱스의 선정 1.1 인덱스의 구조 대용량 솔류션