[SQLD][책없이 공부]2과목 03. SQL 최적화의 원리_03 옵티마이저 조인

1. Nested Loop (NL) 조인

- 하나의 테이블에서 데이터를 먼저 찾고 그 다음 테이블을 조인하는 방식

- 외부 테이블(outer table) : 먼저 조회되는 테이블

  내부 테이블(inner table) : 외부 테이블 다음 조회되는 테이블

- 외부테이블의 크기가 작은 것을 먼저 찾아야 데이터가 스캔되는 범위를 줄일 수 있음

- NL조인은 랜덤방식으로 데이터에 엑세스 -> RANDOM ACCESS가 발생 -> 많이 발생하면 성능 지연 발생

① 선행테이블에서 조건을 만족하는 첫 번째 행 찾음 

② 선행테이블 조인 키 가지고 후행 테이블에 조인키가 존재하는지 찾으러 감(random access)

③ 후행테이블 인덱스에 선행 테이블 조인키 존재하는지 확인

④ 인덱스에서 추출한 레코드 식별자를 이용하여 후행테이블에 엑세스

⑤ 반복 수행 후 만족하는 행을 추출 버퍼에 넣음. -> 반환

SELECT /*+ ORDERED USE_NL*/* FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 10;

2. Sort Merge 조인

- 칼럼을 기준으로 조인 수행하며 스캔 방식으로 데이터를 읽음

- 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩하고 SORT 수행 -> SORT완료되면 두 테이블을 병합함

- 정렬이 발생하므로 데이터양 많아지면 성능 저하(데이터가 많아지면 임시 영역인 디스크를 사용하게 됨)

- use_merge 힌트를 사용하여 SORT MERGE 조인을 할 수 있음 /*+orderd use_merge*/

① 선행테이블에서 주어진 조건을 만족하는 행을 찾음

② 선행 테이블의 조인 키를 기준으로 정렬 작업 수행

③ 선행 테이블의 조건을 만족하는 모든 행에 대해 탐색과 정렬 반복

④ 후행 테이블의 조인 키를 기준으로 정렬작업 수행

⑤ 후행 테이블의 조건을 만족하는 모든 행에 대해 탐색과 정렬 반복

⑥ 정렬된 결과를 이용하여 조인 수행

SELECT /*+ ORDERED USE_MERGE*/* FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 10;

3. Hash 조인

- 해슁기법을 사용하여 조인

- 조인 수행할 테이블의 조인 칼럼을 기준으로 해쉬 함수를 수행하여 서로 동일한 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지 비교하며 조인 수행

- 두 테이블 중 작은 테이블을 hash메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해 해시 테이블 생성

- 해시 함수를 사용해서 주소 계산하고 해당 주소를 사용해 테이블을 조인 -> CPU연산 많음

- 해시 함수를 사용하므로 EQUI JOIN에서만 사용 가능

- 해시 조인 시 선행 테이블이 충분히 메모리에 로딩되는 크기여야 함

  -> 생성된 해쉬테이블 크기가 메모리에 적재할 수 있는 크기보다 더 커지면 임시영역(디스크)에 해쉬 테이블 저장...

① 선행 테이블에서 주어진 조건을 만족하는 행을 찾음

② 선행 테이블의 조인 키를 기준으로 해쉬 함수 적용해 해시 테이블 생성 -> 조인 칼럼과 SELECT 절에서 필요로 하는 칼럼도 함께 저장

③ 후행 테이블에서 주어진 조건을 만족하는 행을 찾음

④ 후행 테이블의 조인 키를 기준으로 해시 함수를 적용해 해당 버킷을 찾음 -> 조인키로 실제 조인될 데이터 찾음

⑤ 조인에 성공하면 추출 버퍼에 넣음

⑥ 반복수행

SELECT /*+ ORDERED USE_HASH*/* FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 10;