조인은 2개 이상의 테이블들을 Relation Key로 연결하여 Merge시키는 것을 말한다.
조인 종류
-조인 메소드 : Nested Loop Join, Sort Merge Join, Hash Join-
-조인 타입 : Basic(Natural) Join, Outer Join, Semi Join, Anti Join 등
Relation
-WHERE절에 조인 조건이 누락되거나 서로 Relation을 갖지 않는 테이블간의 조인으로 인해 M:M Relation이 발생할 수 있음
Cartesian Product
-2개의 집합이 Relation Key로 연결되지 못하는 경우의 조인 Relation
-일반적으로 잘못 작성된 SQL에서 비롯되며 성능에 좋지 않으므로 꼭 필요한 경우에만 사용
조인(join)의 예
SQL> SELECT c.cust_last_name, c.cust_first_name,
co.country_id, co.country_name
FROM customers c, countries co
WHERE c.country_id = co.country_id
AND co.country_id = ‘JP’ 비조인 술어 (Predicates)
OR c.coust_id = 205 ; 단일행 술어 (Predicates)
조인 술어 : 조인에 있는 두 테이블의 열을 결합하는 WHERE 절에 있는 술어
비조인 술어 : 하나의 테이블만 참조하는 WHERE 절에 있는 술어
단일행 술어 : 고유 또는 PK 제약 조건이 있는 열이나 해당하는 제약 조건 없이 고유 인덱스가 있는 열의 동등 술어(Optimizer는 이러한 술어가 항상 하나의 행을 반환하거나 행을 전혀 반환하지 않는다는 사실을 인식함)
Nested Loop Join
선행(Driving) 테이블 조건에 맞는 데이터와 매치되는 값을 다른(후행) 테이블에서 참조
수행과정 – TAB1이 선행 테이블일 때
SQL> SELECT a.FLD1, …, b.FLD1,…
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.FLD2 like ‘A%’
AND a.FLD1 = ’10’
a.FLD1 인덱스 5000건 Range Scan
rowid로 TAB1에 5000회 random access
성공/실패에 상관없이 b.KEY2 인덱스에 5000회 random access 및 조인시도
조인에 성공한 rowid로 TAB2에 100회 random access
b.FLD2 like ‘A%’ 체크 후 성공한 50건 운반단위 이동
수행과정 – TAB2가 선행테이블일 때
SQL> SELECT a.FLD1, …, b.FLD1,…
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND b.FLD2 like ‘A%’
AND a.FLD1 = ’10’
b.FLD2 인덱스 100건 Range Scan
TAB2의 100회 random access
성공/실패에 상관없이 a.KEY1 인덱스에 100회 random access 및 조인시도
조인에 성공한 rowid로 TAB1에 70회 random access
a.FLD1 = ’10’ 체크 후 성공한 50건 운반단위 이동
특징
선행 테이블의 처리범위가 일량을 결정함(방향성)
선행 테이블의 값을 받아서 후행 테이블의 처리범위가 결정됨(종속적)
주로 random access 방식으로 처리됨(random access)
후행 테이블의 조인 컬럼의 인덱스의 유무 및 조건의 인덱스 참여의 정도에 따라 수행속도가 많이 차이 남(연결고리 상태)
사용기준
부분범위처리를 하는 경우에 유리함
처리량이 적은 경우에 유리함 (random access가 많을 경우 수행속도를 보장할 수 없으므로 Sort Merge Join이나 Hash Join으로 유도)
선행 테이블의 결과를 받아야만 후행 테이블의 처리범위를 줄일 수 있는 경우에 유리함 (연결고리에 Index가 반드시 존재하여야 함)
선행 테이블의 처리범위가 수행속도에 절대적 영향을 미치므로 최적의 조인 순서가 될 수 있도록 유도해야 함
Sort – Merge Join
양쪽테이블을 각자 액세스하여 처리 범위를 줄이고, 조인컬럼 순으로 데이터를 Sort 후에 조인하는 방식
수행과정
-a.FLD1 인덱스 Range Scan
-rowid로 TAB1에 random access
-조인컬럼인 a.KEY1로 SORT
-b.FLD2 인덱스 Range Scan
-rowid로 TAB2에 random access
-조인컬럼인 b.KEY2로 SORT
-양쪽 집합을 Scan하면서 a.KEY1 = b.KEY2 조인 시도
-성공한 Row 운반단위 이동
특징
상대 테이블로부터 결과 값을 제공받지 않고, 자신에게 주어진 조건으로만 처리범위를 결정함(독립적)
각자 SORT후에 조인함으로 부분범위 아닌 전체범위 처리를 함(전체범위 처리)
조인의 순서에는 상관없음(무방향성)
인덱스가 아닌 컬럼도 Merge할 작업 대상을 줄이므로 중요한 의미를 가짐
테이블로부터 읽을 때 DB_FILE_MULTIBLOCK_READ_COUNT를 적용함
사용기준
처리량이 많거나 전체범위 처리 시에 유리함(random access가 많은 Nested Loop Join은 불리)
스스로 자신의 처리범위를 많이 줄일 수 있을 때 유리함
연결고리 이상 상태에 영향을 받지 않으므로 연결고리 컬럼을 위한 인덱스를 생성하지 않고도 유용하게 사용할 수 있음
처리할 데이터량이 적은 온라인 어플리케이션에서는 Nested Loop Join이 유리한 경우가 많으므로 Sort Merge Join은 주의하여 사용해야 함
Hash Join
해슁함수 기법을 활용하여 조인을 수행하는 방식으로 대량의 데이터를 조인하는데 주로 사용되며 CBO의 동치조인(equijoin)에서만 가능
수행과정
-두 테이블 중 적은 테이블을 선행 테이블로 결정
-선행 테이블을 Hash Function을 이용하여 Hash Area에 Hash Table을 구성(Build Input)
-Hash Area만으로 Hash Table이 생성 가능하다면 후행테이블은 크기에 상관없이 차례로 Hash Function을 이용하여 Hash Table과 조인(Probe Input)하면서 성공한 결과값을 운반단위로 이동
-Hash Area만으로 Hash Table 생성이 불충분 하다면 Hash Table Overflow가 발생하여 데이터를 나눠서 저장 할 Partition 수를 결정(Fan-out)
-선행 테이블의 조인 컬럼과 Select List 컬럼을 메모리로 읽어 들여 첫번째 Hash Function을 이용하여 Partition을 Mapping하고, 두번째 Hash Function를 이용하여 Hash Table 생성시 사용 할 Hash Value를 생성
-선행 테이블의 조인 컬럼의 유일 값만으로 Bit-Vector을 생성(추후 Bit-Vector filtering에 사용하기 위함)
-Partition에 데이터를 MOVE하고 채워진 Partition은 디스크로 내려감
-선행 테이블이 모두 읽혀지면 Partition 테이블을 완성하고, Partition 크기순으로 정렬한 후 작은 Partition N개를 메모리에 로드
-후행 테이블을 읽으면서 조인컬럼으로 Bit-Vector와 Filtering에 성공하였다면, 첫번째 Hash Function으로 Partition을 결정하고, 두번째 Hash Function를 이용하여 메모리 상에 있는 선행테이블과 조인하고 성공하면 운반단위로 이동하고, 해당 Partition이 메모리에 존재하지 않는다면 Hash Key Value, 조인컬럼, Select List를 디스크에 씀
-후행 테이블이 모두 읽혀지면,Bit-Vector Filtering에 성공했지만,조인에 성공하지 못해 미 처리된 선행 파티션과 후행 파티션을 메모리에 올려 차례로 반복수행
특징
다른 테이블의 결과 값을 제공받지 않고, 자신에게 주어진 조건으로만 처리범위를 결정함(독립적)
Hash Function을 이용하여 mapping하는 후행테이블은 전체범위 처리를 하게 됨(반 부분범위 처리)
메모리 영역만으로 Hash Table을 생성할 때 최적의 효과를 낼 수 있으므로 적은 테이블이 선행테이블로 선택됨
Hash Function을 이용하므로 결과값의 정렬을 보장 받을 수 없음
사용기준
대량의 데이터 access시, 배치 처리, Full Table Scan 하면서 조인 해야 할 때 유리함
비용은 많이 들지만 수행속도를 보장해야 하는 작업에 유용
가능한 메모리 내에서 작업 가능하도록 initialization Parameter나 Session 정보를 변경하여 사용하도록 함(HASH_AREA_SIZE 등)
글 탐색
