테이블 , 뷰 혹은 인라인뷰로 가공된 중간집합들은 조인을 통하여 연결된다.
조인은 조건절에 연결을 위한 논리적인 연결고리를 가지고 있다.
조인의 종류는 다음과 같다.
가장 고전적인 형태의 조인방식.
현실적으로 가장 많이 적용 되고 그럴수 밖에 없는 가장 기본적인 조인.
이 조인으ㅣ 핵심은 먼저수행되는 칩합의 처리범위가 전체의 일량을 좌우한다는 것과
나중에 반복 수행되는 연결작업이 랜덤 액세스로 발생한다는 점이다.
이 조인의 단계는 다음과 같다.
. 옵티마이져는 외측집합(Outer Lopo)을 결정한다. 이를 선행(Driving) 집합 이라 한다.
선행집합의 처리범위에 있는 각 로우에 대해 내측집합을 연결한다.
. 선행 집합이 액세스되면 그들의 모든 컬럼은 상수값을 가지게 되며
이미 존재하던 상수값 까지 감안해서 나머지 집합들 중에서 다음 수행할 내측 집합을 선택한다.
. 만약 조인될 집합이 더 있다면 위의 방법으로 나머지 순서도 결정한다.
. 외측 집합 각각의 로우에 대해 내측 집합으ㅣ 대응되는 모든 로우가 액세스 된다.
큰 흐름은 이렇다
NESTED LOOPS
outer_loop
inner_loop
Rows Execution Plan
\
위 실행계획의 외측 루프는 클러스터 키를 범위 스캔 하면면서 테이블을 클러스터 스캔을 하고 있다.
이렇게 액세스된 각각의 로우에 대해 내측루프가 수행된다.
외측 루프에서 액세스한 로우수보다 내측루프의 인덱스 스캔량이 많고 'RANGE SCAN'을 한것을 보면 내측 루프 액세스는 'M'쪽 집합이다.
이 인덱스를 경유하여 테이블을 액세스 한것이 806건 이니 인덱스 스캔에서 540건이 걸러졌다는 것도 알 수 있다.
그후 내측조인에서 테이블 액세스까지 완료한 후 다시 조건에 의해 걸러졌기 때문에 최종 완료 건수는 240건 이다.
이 조인 방식으로 유도하기 위하여 'USE_NL(table1, table2)'를 사용할 수 있다.
◈ 진보된 내포 조인(Advanced Nested Loops Join)
진보된 Nested Loops 조인은 클러스터링 팩터가 양호하다면 보다 많은 부분을 한번의 블록 액세스에서 연결할 수 있기 때문에
물리적, 논리적 블록 액세스 량이 크게 감소함으로써 효율성이 크게 높아지게 되었다.
SELECT e.\* , d.DNAME
FROM EMP e , DEPT d
WHERE e.DEPTNO = d.DEPTNO
AND d.LOC = 'SEOUL'
AND e.JOB = 'CLERK';
Execution Plan
\
내측 루프의 테이블 액세스 부분이 제일위로 이동하고, 인덱스를 액세스 하는 부분이 차지 하고 있음.
그 절차는. 다음과 같다.
1. 'DEPT_LOC_IDX' 인덱스를 이용하여 LOC = 'SEOUL'인 DEPT 테이블의 첫번째 로우를 액세스.
2. 액세스한 DEPT 테이블의 DEPTNNO를 이용하여 EMP 테이블의 인덱스를 범위 스캔하면
DEPT 테이블의 DNAME과 결합한 결과의 집합이 만들어 진다.
3. 이제 ROWID를 이용하여 EMP 테이블의 블록을 액세스 하여 PGA 버퍼에 저장
4. 앞서 2. 에서 만들어진 집합에 있는 해당 블록을 가진 로우와 3. 에서 액세스해 둔 PGA버퍼를 이용하여
대응되는 로우를 찾아서 JOB='CLERK' 조건을 체크하고, 성공하면 운반단위로 보냄. 이부분이 핵심.
각각의 ROWID 마다 테이블 액세스를 시도하는것이 아니라 한 번 액세스한 블록에서 계속 연결을 시도 한다는 것.
5.PGA 버퍼와의 연결이 완료되면 다시 2.에서 만들어진 집합으ㅣ ROWID 에서 다음 블록을 찾아 EMP 테이블의 블록을
액세스하는 3. 작업을 수행한다.
6.PGA 버퍼에 새로운 블록이 등장했으므로 다시 4.의 작업을 수행.
7. 앞서 1.에서 수행한 'DEPT_LOC_IDX'인덱스에서 두 번째 로우를 액세스 하여 위의 2~6까지으ㅣ 작업을 반복
8. 'DEPT_LOC_IDX'인덱스의 처리 범위가 끝나면 쿼리 종료.
고전적인 형태 이며 NL 조인의 문제를 해결하기 위한 일종의 대안으로 나타 났다.
가장 큰 특징은 연결을 위해 랜덤 액세스를 하지 않고 스캔을 하면서 이를 수행 한다는것.
연결작업은 효과적으로 수행하게 디었지만 먼저 정렬을 해야한다는 부담이 있음.
정렬은 메모리에서 수행되기 때문에 정렬을 위한 영역(Sort Area Size)에 따라 효율은 큰 차이가 난다.
어느 한계를 넘으면 NL 보다 더 나쁜 수행속도를 가져오 수 있다.
또한 연결고리의 비교연산자가 '=' 이 아닌경우 (LIKE,BETWEEN,>,>=,<,<=) 일때 NL 조인보다 유리한 경우가 많다.
NL과 달리 선행 집합의 개념이 없이 독립적인 처리를 한다.
이 형태로 조인의 방식을 유도 하려면 'USE_MERGE(table1,table2)' 힌트를 사용 한다.
해슁함수 기법을 홀용하여 조인을 수행하는 방식
해쉬함수란 컬럼의 값을 받아서 이함수를 경유하면 로우의 저장위치를 리턴 하는 것.
(이것을 활용하는 것이 해쉬클러스터라는 것을 우린 알고 있다.?)
NL의 랜덤액세스로 연결하는 큰 부하 때문에 그 대안으로 Sort Merge 조인을 선택하였으나
정렬에 대한 부담은 데이터 양이 늘어날수록 크게 증가 하기 때문에 대용량에선 해결책이 될 수 없다.
해쉬 조인은 이러한 경우의 해결대안 으로 등장.
정렬을 하지않으면서 연결할 대상을 주변에 위치시킬 수 있는 방법은 바로 해쉬 함수를 활용하는 것이다.
그러나 해쉬 함수는 직접적인 연결을 담당하는 것이 아니라 연결될 대상을 특정 지역에 모아 두는 역할만을 담당한다.
이렇게 동일한 해쉬값을 가진 데이터들을 모아둔 공간을 파티션(Partition) 이라고 하고
이들중 서로 조인해야 할 것들을 연결하게 되는데 이것을 파티션 짝(Pair) 라고 한다. 실제 연결작업은 이 짝들을 대상으로 일어난다.
이 파티션중 작은 파티션을 메모리 내에 임시적인 해쉬 테이블로 만든다.
큰 파티션의 로우들이 외측 루프가 되고 해쉬 테이블 액세스는 내측 루프가 되어 조인을 수행한다.
해쉬함수는 실제 연결이 수행될 때는 마치 랜덤 액세스가 발생하는 것처럼 보이지만 이미 연결대상 짝들만 모아둔 상태이고
메모리 내에서 해쉬 액세스를 하기 때문에 일반적인 정렬에 비해 훨씬 유리하다. 또한 한건의 연결을 위해 하나의 블록이 액세스 될수도 있는
NL 과는 근본적으로 다르다.
만약 조인의 한쪽이 해쉬영역 보다 작아서 In-Memory 해쉬조인이 가능 하게 되면 수행속도는 매우 빨라진다.
그리하여 최근의 옵티마이져는 많은 조인을 해쉬 조인으로 선택하려는 경향이 있다.
해쉬 조인은 연산자에 대한 제약이 있다.
해쉬 조인은 동치조인(Equijoin) 일때만 가능하다.
대량 범위에 해단 조인이나 테이블이 너무 많은 조각으로 산재 되어 있을때 특히 유리하다.
또한 인덱스를 가질 수 없는 가공된 집합과의 조인 에서도 매우 효과적이다.
해쉬 조인으로 유도하기 위해서는 'USE_HASH(table1,table2)' 힌트를 사용 한다.
\- 이 책에서 말하는 세미조인은 다양한 비교연산자에 의해 사용된 서브쿼리가 메인쿼리와 연결되는 모든 경우를 뜻하는 넓은 의미의 세미조인을 의미한다.
\- 우리가 여러형태의 서브쿼리를 사용했더라도, 결국은 메인쿼리와 서브쿼리의 집합을 연결해야 하는 일종의 조인인 것이다.
\- 조인은 조인되는 집합간에는 어느 것이 먼저 수행되느냐 상관없이 논리적으로는 수평적 관계이다.
하지만, 서브쿼리는 메인쿼리와 종속적 관계이다. 이는 서브쿼리의 집합이 메인쿼리의 집합을 변형시켜서는 안된다는 것을 의미한다.
\- M*1이 M이듯이, 메인쿼리가 변형없이 조인하기 위해서는 서브쿼리의 집합은 항상 '1'집합이 되도록 하는 것이다.
\- 그러므로 서브쿼리가 '1'이라는 것이 증명되면 옵티마이저는 조인과 완전히 동일한 실행계획을 수립한다.
\- 서브쿼리가 'M'집합인 경우, 옵티마이저는 'M'집합인 채로 조인을 시도하지 않도록 하기위해. 중간처리를 하게된다. 이때 수행되는 순서나 조인방식에 따라 처리하는 방법이 다르다.
\- 서브쿼리가 먼저 수행되어 메인쿼리에 결과를 제공하는 Nested Loops 방식이라면 서브쿼리는 먼저 수행되머 'SORT(UNIQUE)'처리를 하여 메인쿼리에 '1'집합을 조인하게 한다.
만약 Sort Merge 조인이난 해쉬 조인으로 수행된다면 서브쿼리는 언제나 이런처리를 해야한다.
\- 'M'집합을 가진 서브쿼리가 나중에 수행되는 Nested Loops로 수행된다면
메인쿼리가 외측 루프가 되고, 서브쿼리가 내측 루프가 되어서, 첫번째 연결에 성공하면 해당 내측 루프를 즉시 종료하는 방법이다.
이러한 처리를 실행계획에서는 '필터(Filter)형 처리'라고 부른다.
\- IN을 사용한 서브쿼리라고 해서 항상 먼저 수행되는 것은 아니지만, EXISTS를 사용한 경우가 Nested Loops형으로 수행된다면 언제나 나중에 수행된다.
\- 특별히 Sort Merge나 해쉬조인형식으로 유도하기 위해서,
10g부터는 일반적 조인과 같이 서브쿼리에 'USE_MERGE'나 'USE_HASH'힌트를 사용하도록 변경되었다.
[정리]
\- 비교연산자에 의해 사용된 서브쿼리도 넓은 의미로는 조인이나 마찬가지다.
\- 단, 조인은 조인되는 집합간에 어느집합이 먼저 수행되느냐가 상관없지만, 서브쿼리는 메인쿼리에 종속관계이므로, 조인형태를 취하더라도, 메인집합에 변형을 가해서는 안된다.
M*1은 M이므로, 서브쿼리는 항상 '1'집합 모양이 되어야, 변형이 안되는것이다. => '1'집합이라는게 증명되면, 옵티마이저는 조인과 동일한 실행계획을 수립한다.
\- 서브쿼리가 'M'집합이면, 옵티마이저는 메인집합에 변형을 가하지 않게 하기 위해 중간처리를 한다.
\- 서브쿼리가 먼저 수행된다면, 'SORT(UNIQUE)'처리를 하여 메인쿼리에 '1'집합과 조인하게 해주고,
서브쿼리가 나중에 수행된다면, 메인쿼리가 바깥쪽의 Loop가 되어서, 안쪽 Loop인 서브쿼리와 첫번째 연결에 성공하면 바로 안쪽 Loop를 빠져나오는 방식으로 처리하는 것이다.
=> 이를 'Filter 처리'라 한다.
\- 특별히, Sort Merge나 해쉬조인으로 유도하기 위해서, 10g부터는 서브쿼리에 일반적인 조인과 같이 'USE_MERGE'나 'USE_HASH'힌트를 사용하면된다.
h4.
\- 카티젼조인은 조인되는 두 개의 집합간에 연결고리가 되는 조건이 전혀 없는 경우를 말한다. 넓은 의미에서 보면 M:M 조인을 말하기도 한다.
\- 실제로는 실행계획에서 'CARTESIAN'으로 나오는 조인은 Sort Merge조인뿐이며, 다른조인은 정상적인 조인과 동일하게 실행계획이 나타난다.
\- 카티젼 조인이 발생하는 경우는 특별한 목적으로 고의적으로 만드는 경우와, 3개 이상의 집합을 조인할 때 조인순서의 잘못으로 연결고리가 빠져서 발생하는 경우가 있다.
h4.
\- 아우터 조인은 어떤 대상집합을 기준으로 거기에 아우터 조인되어 있는 집합에 대응되는 Row가 없더라도 기준집합의 모든 Row를 리턴하는 조인이다,
{+}Nested Loops 아우터 조인{+}
\- 이 조인은 반드시 기준이 되는 집합이 바깥쪽 루프로써 먼저 수행되어야 하기때문에, 목적없이 함부로 아우터 조인을 시키면 조인의 방향이 고정되므로 주의한다.
\- 이 방식으로 유도하는 힌트는 기존 'USE_NL(table1, table2)'을 사용하며 실행계획은 동일하나 'NESTED LOOPS(OUTER)'로 표현되는 것이 다르다.
{+}Hash 아우터 조인{+}
\- Nested Loops조인으로는 부담이 되는 대량의 데이터이거나 인덱스 등의 문제로 NL조인으로 수행에 문제가 있을때 선택될 수있다.
\- 이 방식에서도 기준집합은 무조건 빌드입력(Build Input)을 담당하고, 내측 조인집합이 해쉬테이블로 생성되어 연결작업이 수행된다.
\- 해쉬조인에서는 역할이 고정되드라도 NL조인에서와 같은 부담은 크게 발생하지 않는다.(2.3.4 해쉬조인참고)
\- 조인뷰나 조인 인라인뷰와 아우터 조인을 수행한다면 일반적인 조인에서와 같이 뷰의 병합이나 조건절 진입이 허용되지 않고, 뷰의 전체집합이 독립적으로 수행된 결과와 아우터 조인을 수행한다.
그러므로, 경우에 따라서는 뷰내로 조건들이 파고 들어갈 수 없으므로 심각한 부하를 발생시킬 수 있다.
\- 내측테이블의 특정조건을 만족하는 집합과 아우터 조인을 시도해야 한다면, 조건을 담은 집합을 인라인뷰로 만든 후 아우터 조인을 하면 해당 조건을 만족 할수 있다.
\- 인라인뷰가 단일테이블로 구성된 경우에는 조건절 진입(Pushing Predicates)가 가능하므로, 처리범위를 줄일 수 있다.
\- 하지만, 조인된 인라인 뷰는 조건절 진입이 불가능하게 되므로, 인라인뷰 내에서 충분히 처리범위를 줄일 수 없다면, 아우터 조인으로 인해 심각한 수행속도 저하를 가져올수 있다.
{+}Sort Merge 아우터 조인{+}
\- Nested Loops조인으로는 부담이 되는 대량의 데이터이거나 인덱스 등의 문제로 NL조인으로 수행에 문제가 있을때 선택될 수있다.
\- 또한, 조건 연산자로 인해 해쉬조인이 불가능 할때이거나, 이미 다른 처리에 의해 조인을 위한 정렬이 선행되어 있어서 더 유지해질때 적용된다.
{+}전체 아우터 조인{+}
\- 양쪽 집합이 모두 기준집합이면서 대응집합이 되는 아우터 조인이다.
\- 먼저 한 쪽을 기준으로 아우터조인을 수행한 결과와 다른 쪽을 기준으로 부정형 조인을 한 결과를 결합해서 리턴하는 방법이다.
\- 이러한 경우는 설계상의 문제나, 데이터의 일관성에 대한 문제가 원인이 되는 경우가 많다.
\- 이러한 경우 다양한 대안들이 있으며, 예를 들어 UNION ALL로 합집합을 만들고 GROUP BY를 이용하여 소소공배수 집합을 만드는 방법 또는 인라인뷰, 사용자 지정 저항형 함수 등을 활용하는 방법이 있다.
\- 인덱스 조인이란 어떤 쿼리에서 특정 테이블에서 사용된 모든 컬럼이 하나 이상의 인덱스들에 존재할 때, 인덱스들을 결합하여 테이블을 엑세스하지 않고 인덱스들로만 처리하는 방법이다.
\- 인덱스를 경유해서 다른 인덱스를 액세스하는 것은 불가능 하므로, 인덱스 병합은 머지나 해쉬 조인으로만 가능하다.
\- 인덱스를 조인한다고 해서 항상 유리한 것은 아니므로, 함부로 유도하지 말하야 한다.
\- 자주 같이 액세스되며, 특정 컬럼 때문에 테이블을 액세스해야 하는 경우가 많이 발생한다면 인덱스 조인을 활용할수있다.