조인은 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 그림참고>
조인은 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) 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 그림참고>
사용자가 어느 방향이 먼저 처리되는 것이 유리한지를 알고 있다면 힌트나 사용제한 기능을 이용하여 튜닝을 하라
{section}
{column}
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'
{column}
{column}
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'
{column}
{section}
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이 먼저 액세스
{column}
{column}
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가 먼저 액세스
{column}
{section}
튜닝의 절차
특정한 액세스 유형을 위해 함부로 인덱스를 추가하게 되면 기존의 액세스에 영향을 미칠 수 있으므로 반드시 종합적인 판단을 해야한다.
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 그림참고>
{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 그림참고>
{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 그림참고>
1) 단 한번의 SQL이 수행. 2) SQL내에서 조인된 테이블의 모든 컬럼을 마음대로 가공가능 | 1)'운반단위 + 1'번의 SQL이 수행. 2) 별도의 언어를 통하여 추가적인 가공 |
<대용량데이터베이스 솔루션1 108page 그림참고>
쿼리에서의 활용
{section}
{column}
SELECT b.부서명, SUM(a.매출액)
FROM TAB1 a, TAB2 b
WHERE a.부서코드 = b.부서코드
AND a.매출일 LIKE '9503%'
GROUP BY b.부서명
{column}
{column}
SELECT b.부서명, a.매출액
FROM (SELECT 부서코드, SUM(매출액)
FROM TAB1
WHERE 매출일 LIKE '9503%'
GROUP BY 부서코드) a, TAB2 b
WHERE b.부서코드 = a.부서코드
{column}
{section}
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조인보다 불리해 질 수 있다.
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를 사용하지 않았다면 부분범위처리를 하게 되므로 아주 빠른 수행속도를 보장한다. | 각각의 테이블에 대해 전체 테이블을 스캔하여 연결고리가 되는 컬럼으로 정렬하여 머지한다. 랜덤으로 전체 테이블을 액세스하는 것보다 스캔방식으로 전체 테이블을 액세스하는 것이 유리하다. |