[DataBase] 조인의 원리 & 튜닝

** 데이터 진흥원 - SQL 전문가 가이드의 [과목3-4장 : 조인 튜닝] 내용

---

1) NL 조인

실제 쿼리	실행계획
select /*+ ordered use_nl(e) */            e.empno, e.ename, d.dname, e.job, e.sal    from dept d, emp e  where e.deptno = d.deptno -------- (1)      and d.loc = 'SEOUL'          -------- (2)      and d.gb = '2'                    -------- (3)      and e.sal >= 1500             -------- (4)    order by sal desc ** dept idx = pd_dept : deptno, dept_loc_idx = loc ** emp idx = pk.emp : empno, emp_deptno_idx : deptno	Excution Plan ---------------------------------------------------------- 0       SELECT STATEMENT 1   0     SORT ORDER BY 2   1        NESTED LOOPS 3   2          TABLE ACCESS BY INDEX ROWID DEPT 4   3              INDEX RANGE SCAN DEPT_LOC_IDX 5   2          TABLE ACCESS BY INDEX ROWID_EMP 6   5             INDEX RANGE SCAN EMP_DEPTNO_IDX
(2) -> (3) -> (1) -> (4) 순서로 실행되었으며, 각 단계를 하나의 행이 여행하는 순서라고 본다. 하나의 단계가 끝나면, 다음 단계로 넘어가는 것이 아니라, 하나의 로우가 여행을 끝내면, 다음 로우가 여행을 시작하는 방식이다. (NL 조인의 경우, 인덱스를 잘 설정하면 로우를 줄어들어 성능이 극적으로 개선된다)

 

-> 일정 부분을 조회하는 조건 값을 가진 데이터에 적합하겠다. NL 조인을 보조하기 위해 아래와 같은 기능들이 구현되었다.

     NL 조인의 성능은 Outer (몸통) 테이블의 read 량에 달려있다.

** Prefatch : 다음 읽을 행을 미리 버퍼캐시에 올려두는 기능
** 배치 I/O : I/O 콜을 미뤘다가 일정량 쌓이면 한 번에 처리하는 기능

 

 

---

2) 소트 머지 조인

-> NL 조인의 선두 컬럼이 효과적이지 않은 경우, 고려된다.

      어차피 대부분 읽을 거, 테이블들을 Sorting 해두고 읽겠다는 것이다. (다음 값이 나타나면 해당 행의 여행이 끝난다.)

실제 쿼리 (Oracle)	실행계획
select /*+ ordered use_merge(e) */            d.deptno, d.dname, e.empno, e.ename    from dept d, emp e  where d.deptno = e.deptno ** dept idx = pd_dept : deptno, dept_loc_idx = loc ** emp idx = pk.emp : empno, emp_deptno_idx : deptno	Excution Plan ---------------------------------------------------------- 0       SELECT STATEMENT Optimize=CHOOSE 1   0     MERGE JOIN 2   1        SORT (JOIN) 3   2          TABLE ACCESS (FULL) OF 'DEPT' 4   1       SORT (JOIN) 5   4          TABLE ACCESS (FULL) OF 'EMP'

실제 쿼리 (SQL SERVER)	실행계획
select d.deptno, d.dname, e.empno, e.ename    from dept d, emp e  where d.deptno = e.deptno  option (force order, merge join) ** dept idx = pd_dept : deptno, dept_loc_idx = loc ** emp idx = pk.emp : empno, emp_deptno_idx : deptno	StmtText ---------------------------------------------------------- | -- Merge Join (Inner Join, MANY TO MANY MERGE)     | -- SORT (ORDER BY:([d].[deptno] ASC))     |.   | -- Table Scan (OBJ:[SQLPRO].[dbo].[dept] AS [d])     | -- SORT (ORDER BY:([e].[deptno] ASC))          | -- Table Scan (OBJ:[SQLPRO].[dbo].[emp] AS [d])

-> 어차피 모든 테이블을 sorting하다 보니, 각 테이블의 크기가 성능에 주요한 영향을 미친다.

 

---

3) 해시 조인

(1) 조인 컬럼에 적당한 인덱스가 없어 NL조인이 비효율일 때,

(2) 인덱스가 있어도 NL 조인 드라이빙 집합에서 inner 쪽으로 조인 엑세스 량이 많아 랜덤 엑세스 부하가 심할 때

(3) 소트 머지 조인을 하기엔 두 테이블이 너무 커 소트 부하가 심할 때

(4) 수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때

-> 수행 빈도가 낮고, 쿼리 수행 시간이 길고, 대용량인 테이블을 조인할 때 사용한다.

실제 쿼리 (Oracle)	실행계획
select /*+ ordered use_hash(e) */            d.deptno, d.dname, e.empno, e.ename    from dept d, emp e  where d.deptno = e.deptno ** dept idx = pd_dept : deptno, dept_loc_idx = loc ** emp idx = pk.emp : empno, emp_deptno_idx : deptno	Excution Plan ---------------------------------------------------------- 0       SELECT STATEMENT Optimize=CHOOSE 1   0     HASH JOIN 2   1          TABLE ACCESS (FULL) OF 'DEPT' 3   1          TABLE ACCESS (FULL) OF 'EMP'

실제 쿼리 (SQL Server)	실행계획
select d.deptno, d.dname, e.empno, e.ename   from dept d, emp e where d.deptno = e.deptno option (force order, hash join) ** dept idx = pd_dept : deptno, dept_loc_idx = loc ** emp idx = pk.emp : empno, emp_deptno_idx : deptno	StmtText ---------------------------------------------------------- | -- Merge Join (Inner Join, MANY TO MANY MERGE)     | -- Table Scan (OBJ:[SQLPRO].[dbo].[dept] AS [d])     | -- Table Scan (OBJ:[SQLPRO].[dbo].[emp] AS [d])

 

-> 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 하고, Build Input 해시 키 컬럼에 중복 값이 거의 없으면 개선된다.

 

---

4) 스칼라 서브 쿼리

-> 하나의 값을 반환하는 select 절에 사용되는 서브 쿼리를 의미한다.

쿼리

select empno, ename, sal, hiredate,             (select d.dname from dept d where d.deptno = e.deptno) dname   from emp e where sal >= 2000

여러 값을 반환할 수는 없는데 보완책으로 아래와 같은 방법을 쓴다.

select deptno, dname            , to_number(substr(sal,1,7)) avg_sal            , to_number(substr(sal,8,7)) min_sal            , to_number(substr(sal,15)) max_sal  from(       select d.deptno, d.dname                   ,(select lpad(avg(sal),7) || lpad(min(sal),7) || max(sal)                        from emp where deptno = d.deptno) sal         from dept d      where d.loc = 'CHICAGO'     )

---

5) 고급 조인 기법

-> 인라인 뷰 활용하여 1:M 을 1:1로 조인하기

select t2.상품명, t1.판매수량, t1.판매금액,    from (select 상품코드, sum(판매금액) 판매금액, sum(판매수량) 판매수량                 from 일별상품판매               where 판매일자 between '20240101' and '20241001'               group by 상품코드) t1, 상품 t2   where t1.상품코드 = t2.상품코드

 

-> 배타적 관계의 조인은 Union All로 대채하기

-> 부등호 사이의 집계가 필요할 때는, 윈도우 함수를 사용해본다.

-> ROWID (rownum)을 통해 top 1 쿼리를 사용할 수도 있다.