대용량 데이터베이스 솔루션 발표자: 박보영 2007년 5월19일

목 차 3.2 .1 데이터 연결을 위한 실횡계획 3.2.2.1 내포 조인(nested loops join) 목 차 3.2 .1 데이터 연결을 위한 실횡계획 3.2.2.1 내포 조인(nested loops join) 3.2.2.2 정렬 병합(sort merge join) 3.2.2.3 해쉬 조인(hash join) 3.2.2.4 세미 조인(semi join) 3.2.2.5 카티젼 조인(cartesian join) 3.2.2.6 아우터 조인(outer join) 3.2.2.7 인덱스 조인(index join)

내포조인(Nested loops join) 3.2.2.1 내포조인(Nested loops join) 방 식 : 어떤 범위의 집합(Outer)의 각 로우에 대하여 연결고리를 통해 반복적으로 다른 집합(Inner)의 대응되는 로우를 탐침(Iteration) 방식이고. 먼저 수행되는 집합의 처리범위가 전체의 일량을 좌우하는 종속적인 방식이고 나중에 반복 소행되는 연결작업이 램덤 액세스로 발생한다 특 징 : 소량의 범위를 연결할 때는 매우 유용하다. 문제점 : 대량의 범위일 경우 램덤 액세스 빈번함, 연결고리에 마땅한 인덱스가 존재하지 않을 때 속도의 저하를 가져온다.

내포조인(Nested loops join) 3.2.2.1 내포조인(Nested loops join) ① ① 처리 범위가 넓은 테이블을 먼저 읽는 경우 ▷테이블 A의 20000건 중 첫 번째 자료를 읽는다. ▷특정 테이블 A.칼럼 과 같을 값의 테이블 b.칼럼 값을 가진 자료를 테이블 B에서 찾는다. ▷이 작업을 테이블 A의 자료건수(20000건) 만큼 반복한다. ▷테이블 B에 같은 데이터의 존재 여부와 관계없이 항상 20000건 램덤 ACCESS를 한다. A B (1000행) (20000행) ② ② 처리 범위가 좁은 테이블 을 먼저 읽는 경우(우측) ▷테이블 B의 1000건 중 첫 번째 자료를 읽는다. ▷특정 테이블 B.칼럼 과 같은 값의 테이블 A.칼럼 값을 가진 자료를 테이블 A에서 찾는다. ▷이 작업을 테이블 B의 존재여부와 관계없이 항상 1000건 램덤 ACCESS를 한다. A B (1000행) (20000행)

내포조인(Nested loops join) 3.2.2.1 내포조인(Nested loops join) SELECT STATEMENT NESTED LOOPS 3 NESTED LOOPS 2 (OUTER loop 3.1) NESTED LOOPS 1 (OUTER loop 2.1) outer loops 1.1 - #1 inner loops 1.2 - #2 inner loops 2.2 -#3 inner loops 3.2 -#4 [Full Scan] [중첩된 모습]

내포조인(Nested loops join) 3.2.2.1 내포조인(Nested loops join) ※ 진보된 내포 조인 방 식 : 액세스한 브록을 PGA 버퍼에 저장하여 로우를 찾는 방식 PGA(program global Area) 서버 프로세스가 시작될 때 할당 하고 interface 와 user 사이에 존재하는 공간 사용자 가 join 걸어주는 부분 PGA(Program Grobal Area)공간에 저장 즉,Rowid 저장되어있는 위치 가지고있음 SELECT e.*, d.DNAME FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO AND d.LOC = ‘SEOUL’ AND e.JOB = ‘CLERK’; Execution plan ------------------------------------------------------------------------------------------- table access (by index rowid) of ‘emp’ NESTED LOOPS table access (by index rowid) of ‘dept’ index (range scan) of ‘dept_loc_inx’ (non-unique) table access (by index rowid) of ‘emp’ index (range scan) of ‘emp_deptno_index’ (non-unique) SGA(System Grobal Area) 공유 글로벌 영역이라고 도 하며 데이터 베이스 프로세서에서 공유하는 데이터베이스 정보를 저장하는데 사용된다.

정렬 병합 조인(sort merge join) 3.2.2.2 정렬 병합 조인(sort merge join) 방 식 : Nested loops 조인과 달리 선행집합이라는 개념이 없다. 즉, 그 다음 정렬할 집합의 처리에 아무런 영향을 미치지 못하는 독립적인 처리 방식이다. 특 징 : 이미 정렬이 된집합일 경우, 비교 연산자가(like,between,<,<=,>,>=)일 경우 nested loops join 보다 유리하며 해쉬 조인 이런 경우 사용할 수가 없다. 문제점 : 정렬은 메모리에서 수행되는데 그 영역(sort Area Size)에 따라 효율이 큰 차이 나는데 적은 량의 범위는 빠른 속도를 보장받을 수 있지만 범위가 클수록 영역의 한계 정렬을 하는데 엄청난 부화를 가져온다. Row Execution plan --------- ------------------------------------------------------------------ 1080 MERGE JOIN 76 SORT (JOIN) 76 TABLE ACCESS (BY ROWID) OF ‘CODE_T’ 87 INDEX (RANG SCAN) OF ‘CODE_T_PK’ (UNIQUE) 1018 SORT (JOIN) 1121 TABLE ACCESS (FULL) OF ‘APP_ACCOUNT’

해쉬 조인(Hash Join) 3.2.2.3 방 식 : 해슁함수 기법을 활용하여 조인을 수행하는 방식 방 식 : 해슁함수 기법을 활용하여 조인을 수행하는 방식 행슁함수란 컴럼의 값을 받아서 이 함수를 경유하는 로우의 저장위치를 리턴하는 이며, 이를 활용한 것이 해쉬 클러스터가 있다. 행슁함수의 역할 : 연결될 대상을 특정 지역에 모아 두는 역할만을 담당한다. 수행원리: Hash Function 을 이용하여 Hash Table을 구성하고, 후행집합은 차례로 Hash Function 을 이용하여 Hash Table을 탐침한다. 특 징 : 조인의 어느 한쪽 집합이 해쉬 영역보다 작아서 인-메모리(In-memory) 해쉬 조인이 가능하게 되면 수행속도는 매우 빨라진다. 해쉬값을 가진 데이터들을 모오둔 공가을 파티션(patition)이라하고 이들 중에서 서로 조인해야 할 것들을 연결하게 되는 데 이것을 파티션 짝(pair)라고 부른다. 실질적은 작은은 이러한 짝들을 대상으로 이러난다. 연결작업을 위해 작은 파티션을 메모리에 내에 임시적인 해쉬 테이블로 만든다. 큰 파티션의 로우들이 외측 루프가 되고 해쉬 테이블 액세스는 내측 루프가 되어서 조인을 수행한다. ※ 조인은 동치조인(Equijoin,=)일때만 가능

3.2.2.4 세미 조인(Semi Join) 방 식 : Subquery table의 값 중 한번씩만 Join을 수행하여 원하는 결과를 추출하는 방식 이며 보통 존재유무 확인에 사용되는 Join Type이다. Exists 또는 IN SubQuery에 자주 쓴다. A∩B(교환법칙 성립) 모델링적인 개념 --------------------- dept Table emp Table 1 : M 존재유무를 확인해주는 Table은 반드시 모델링 관계에서 M에 해당하는 Table이 되어야한다. 따라서 IN Subquery 나 Exists Subquery 에 제공되는 Table은 M에 해당되는 Table이므로 일반적으로 많은 Row를 가지게된다. 그러므로 Subquery 의 성능에 주의해야한다.

세미 조인(Semi Join) 3.2.2.4 수행 방식 SELECT deptno FROM dept a WHERE exists (SELECT ‘x’ FROM emp b WHERE a.deptno = b.deptno AND b.sal > 200) AND a.location = ‘SEOUL’ ------------------------------------------------------------------------------------------------------------------------------------------------------------------ a.locatoin = ‘SEOUL’인 첫 번째 Row를 찾는다. 추출된 Row로부터 deptno 값을 제공받아 emp Table의 deptno 찾아 존재유무 확인. 없으면 Join실패로 1단계에서 두 번째 Row를 Access하여 반복 수행한다. 3. 2단계에서 deptno가 존재한다면 b.sal > 200 인지 확인한다. 만족하지 않으면 다음 Row 확인. b.sal > 200 조건을 만족하는 Row가 emp Table에 존재할 때까지 Scan한다. 만족하는 Row가 하나라도 존재하면 dept Table의 해당 Row는 Join에 성공하게 되어 추출된다. 3단계에서 Join에 성공한 dept Table의 Row Table의 Row는 추출 대상 집합이 된다. 1 ~ 4 단계 반복 수항하게 된다.

Cartesian Join test1 test2 14 X 4 = 56 3.2.2.5 Catesian Join은? 조인되는두개의 집합 간에 연결고리 조건이 전혀 없는 경우를 말한다. test1 test2 NO NAME DEPTNO 123 A 20 456 B 30 789 C NESTED LOOPS --------------------------------------------------------------------------------------------------- MERGE JOIN (CARTESIAN) TABLE ACCESS (BY ROWID) OF ‘test2’ INDEX (RANGE SCAN) OF ‘test2_DEPTNO_IDX’ (NON-UNIQUE) TABLE ACCESS (BY ROWID) OF ‘tes1’ INDEX (RANGE SCAN) OF ‘test1_DEPTNO_INX’ (NON-UNIQUE) DEPTNO LOCATION 10 서울 20 대전 30 부산 40 제주 모델링적인 개념 ---------------------- test1 Table test2 Table M : M … 14 ROWS selected 4 ROWS selected (=) NO NAME DEPT LOC 123 A 10 서울 456 B 20 대전 789 C 30 부산 .... 56 ROW selected WHERE 조건이 주지않았거나 정확하게 선언해주지 않았을경우? SELECT A.NO, A.NAME ,B.DEPTNO DEPT, B.LOCATION LOC FROM test1 A , test2 B WHERE A.DEPTNO=B.DEPTNO 14 X 4 = 56 ( A x B ) 곱을 이용한 조인 일어날수있는 경우의 수의 모든 값을 가져오는 불쌍사가 발행한다 이것을 Cartesian Product

아우터 조인(Outer Join) 3.2.2.6 test1 test2 (=) Outer Join Join 조건을 만족하지 않는 경우에도 모든 행들을 다 보고자 하는 경우에 사용 #Outer Join을 하는 경우에는 (+) 연산자를 사용합니다. (혹은 OUTER 조인을 사용합니다.) #(+)를 사용하는 위치는 Join할 데이터가 부족한 쪽에 위치시킵니다. #(+)는 WHERE절에서 비교연산자 기준으로 좌변 또는 우변의 어느 한쪽에만 위치시킵니다. (양변 동시에 사용할 수 없습니다.) #Outer Join에서 IN을 쓰거나 Outer Join조건이 OR로 연결될 수 없습니다. test1 test2 NO NAME DEPTNO 123 A 10 456 B 20 789 C 30 DEPTNO NAME LOCATION 10 P1 서울 20 P2 대전 30 P3 부산 만약 Test2 테이블에 해당 부서가 추가되었을경우? (=) 데이터 량이 적은 쪽에 (+)기호를 붙여준다. (PK) 40 P4 제주 (FK) SELECT A.NO, A.NAME, ,B.DEPTNO ,B.NAME, B.LOCATION FROM test! A test2 B WHERE A.DEPTNO TEST1에 존재하지는 않지만 TEST2에 부서가 신설되었을경우 TEST2의 집합의 모든 값을 가져오고 싶을때! (+) = B.DEPTNO NO NAME DEPTNO LOCATION 123 A 10 서울 456 B 20 대전 789 C 30 부산 40 제주

아우터 조인(Outer Join) 3.2.2.6 Nested Loops Outer Join Hash Outer Join 양쪽 모두에 아우터 조인이 발생하는 경우의 처리 문제 생겼을 경우 먼저 어느 한쪽을 기준으로 아우터 조인을 수행한 결과와 다른 쪽을 기준으로 부정형 조인을 한 결과를 결합해서 결과를 만들게 된다. Nested Loops Outer Join 이 조인은 반드시 기준(보존)집합이 외측 루프로써 먼저 수행되어야 하면 내측 루프가 수행될 때 연결에 실패를 하더라도 외측 루프의 로우를 탈락시키지 않는 방식으로 수행된다. Hash Outer Join 이방식으로는 아우터 조인이 수행되는 경우는 오티마이져가 Nested Loops 조인으로는 부담이 되는 대량의 데이터이거나 인덱스 등의 문제로 Nested Loops 조인으로 수행에 문제가 있을때 선택될 수있다. 이방식에서도 기준 집합은 무조건 빌드입력 (Build Input)을 담당하게 되고, 내측 조인 집합이 해쉬 테이블로 생성되어 연결작업이 수행된다. SELECT last_name, nvl(sum(ord_amt), 0) FROM customers c, orders o WHERE c.credit_limit > 1000 AND c.cust_id = o.cust_id(+) GROUP BY last_naem; ----------------------------------------------------------------------------------------------------- HASH (GROUP BY) HASH JOIN (OUTER) TABLE ACCESS (FULL) OF ‘CUSTOMERS’-------------------outer table INDEX (RANGE SCAN) OF ‘ORD_CUST_IDX1’------------inner talbe

아우터 조인(Outer Join) 3.2.2.6 Sort Merge Outer Join 전체 아우터 조인 이 방식의 아우터 조인은 옵티마이져가 Nested Loops 조인으로는 부담이 되는 대량의 데이터이거나 인덱스 등의 문제로 Nested Loops 조인으로 수행에 문제가 있을 때 선택할 수 있다. 또한 조건 연산자로 인해 해쉬조인이 불가능할 때이거나 이미 다른 처리에 의해 조인을 위한 정렬이 선행되어 있어서 더 유리해질 때 적용한다. 내부적으로 처리 절차는 정상적인 Sort Merg 조인과 동일하지만 머지를 수행하면서 기준 집합에 대응하는 집합이 존재하지 않더라도 결과를 리턴하는 부분만 다를 뿐이다. 전체 아우터 조인 전체 아이터 조인이란 양쪽 집합이 모두 기준집합이면서 대응집합이 되는 아우터 조인을 말한다. 이 조인은 어느 한쪽만을 기준으로 상대를 체크하는 방법으로는 이미 논리적으로 불가능하다. 이러한 조인을 가능하게 하는 방법은 먼저 어느 한쪽을 기준으로 아우터 조인을 수행한 결과와 다른 쪽을 기준으로 부정형 조인을 한 결과를 결합해서 리턴하는 방법이다. Execution Plan -------------------------------------------------------------------------------------- SELECT STATEMENT VIEW UNION-ALL HASH JOIN (OUTER) TABLE ACCESS (FULL) OF ‘EMP’ TABLE ACCESS (FULL) OF ‘EMPLOYEE’ HASH JOIN (ANTI) TABLE ACCESS (FULL) OF ‘EMPLOYEE’ TABLE ACCESS (FULL) OF ‘EMP’ 전체 아우터 조인이라고 해서 위의 예제처럼 전체 테이블 스캔만이 발생하는 것은 결코 아니다. 적절한 인덱스가 있다면 기존의 다양한 액세스 형태가 모두 나타날 수있다. 조건의 형태나 인덱스의 구조에 따라서 아우터 조인을 주관하는 테이블과 부정형 조인을 주관하는 테이블의 순서가 달라지면 수행속도가 좋아질 수도 있다.

3.2.2.7 인덱스 조인 하나이상의 인덱스들을 결합하여 모든 처리를 할 수 있을 때 테이블을 액세스하지 않고 인덱스들로만 처리하는 방법 SELECT /*+ INDEX_JOIN(t) */ A2, B3, C1 FROM table1 t WHERE A1 = ’10’ AND B1 LIKE ‘AB%’ AND C2 >100 VIEW OF ‘index$_join$_001’ HASH JOIN INDEX (RANGE SCAN) OF ‘IDX1’ (NON-UNIQUE) INDEX (RANGE SCAN) OF ‘IDX2’ (NON-UNIQUE) INDEX (FAST FULL SCAN) OF ‘IDX3’ (NON-UNIQUE) IDX1 : A1 + A2 + A3 IDX2 : B1 + B3 IDX3 : C1 + C2 + C3 IDX4 : D1 ㅁ 사용된 모든 컬럼이 어떤 인덱스에라도 존재해야한다. ㅁ 비교 연산자가 ‘=‘이 아니어도 인덱스 조인에 참여한다. ㅁ 반드시 인덱스의 선두 컬럼이 아니어도 인데스 조인에 참여 할 수 있다. 상기 예에 있는 마지막 조건인 ‘C2>100’을 보면 두 번째 인덱스 컬럼이지만 조인에 참여하고 있다. 물론 인덱스 고속 전체 스캔 방식으로 처리된다. ㅁ 조건절을 기준으로 인덱스 조인을 결정한다. 즉 조건절에 기술 된 컬럼을 기준으로 인덱스 적용 여부를 결정한다는 것이다. 만약 조건절에 D1=’10’이란 조건을 줄 때는 인덱스 조인을 하지 만 SELECT-List에만 D1을 주면 인덱스 조인은 수행되지 않는다. ㅁ 현재 상태가 ‘_INDEX_JOIN_ENABLED = TRYE’ 로 지정되어 있지만 굳이 힌트를 적용해야 인덱스 조인으로 수행되고있다. 이것은 인덱스 조인이 논리적으로 가능하다고 해서 항상 발생 되는 것은 아니라는 것을 의미한다. 비용 산정을 통해 충분히 효율적이라고 판단할 때만 인덱스 조인으로 수행한다. ㅁ 실행계획을 살펴보면 해쉬조인으로 수행되고 있음을 알 수 있다 ㅁ 만약 선두 컬럼이 비교되지 않았다면 기술된 위치에 상관없이 최대한 나중에 조인이 수행된다 . ‘IDX3’가 맨 나중에 조인된 것을 확인하기 바란다. ㅁ 인덱스 조인으로 수행된 것을 확인하는 방법은 실행 계획에 VIEW OF ‘index$_JOIN$_001’을 보고 알 수 있다. 이것은 인덱스 조인으로 수행한 결과를 표현한 것이다.

인덱스 조인 3.2.2.7 ㅁ 조인 연결고리로 사용된 x.D1 은 상수 값을 조건으로 가지지 않지만 테이블 액세스를 하지 않고 y.D1에 결 과를 제공해주기 위해 인덱스 조인에 참여하였다. 이때 인덱스 고속 전체 스캔을 하는 것을 주목하기 바란다. SELECT /*+ INDEX_JOIN(x) */ x.A2, x.B3, y.D2 FROM table1 x, table2 y WHERE x.D1 = y.D1 ------------------INDX4 AND x.A1 = ’10’ ------------------INDX1 AND x.B1 LIKE ‘AB%’ -----------INDX2 -------------------------------------------------------------------------------------- TABLE ACCESS (BY INDEX ROWID) OF ‘TABLE2’ NESTED LOOPS VIEW OF ‘index$_join$_001’ HASH JOIN HAJH JOIN INDEX (RANGE SCAN) OF ‘IDX1’ (NON-UNIQUE) INDEX (RANGE SCAN) OF ‘IDX2’ (NON-UNIQUE) INDEX (FAST FULL SCAN) OF ‘IDX4’ (NON-UNIQUE) INDEX (UNIQUE SCAN) OF ‘PK_TABLE2’ (UNIQUE)

감사합니다.