(2) 한쪽 연결고리 이상

• 어느 한쪽의 연결고리에 인덱스(혹은 클러스터)가 없는 상태

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

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

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이 상수값이 되었으므로)

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

{section}
{column}

SELECT a.FLD1, ........., b.FLD1, .........
  FROM TAB2 b, TAB1 a
 WHERE a.KEY1 = b.KEY2
   AND a.FLD1 = '10'
 ORDER BY a.FLD2 

{column}
{column}

1) SELECT FLD1, ........., FLDn
     FROM TAB1
    WHERE FLD1 = '10'
    ORDER BY FLD2 

{column}
{column}

2) SELECT COL1, ........., COLn
    FROM TAB2
   WHERE KEY2 = :a.KEY1

{column}
{section}

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

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

반복연결이 유리한 경우 2

{section}
{column}

SELECT b.부서명, SUM(a.매출액)
  FROM TAB1 a, TAB2 b
 WHERE a.부서코드 = b.부서코드
   AND a.매출일 LIKE '9503%'
 GROUP BY b.부서명 

{column}
{column}

1) SELECT 부서코드, sum(a.매출액)
     FROM TAB1
    WHERE 매출일 LIKE '9503%'
    GROUP BY 부서코드

{column}
{column}

2) SELECT 부서명
     FROM TAB2
    WHERE 부서코드 = :a.부서코드

{column}
{section}

<대용량데이터베이스 솔루션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 그림참고>

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 그림참고>

나. 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의 인덱스는 전혀 사용되지 않았고 머지의 조건으로만 사용.

다. 조인방식의 비교

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

문서에 대하여

• 최초작성자 : VLDB:김종원
• 최초작성일 : 2007년 9월 15일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션1 을 참고했습니다.