꿈꾸는 개발자, DBA 커뮤니티 구루비

==================================================
Chapter 2. 조인원리와 활용
==================================================

--
01. Nested Loops 조인

--
(1) 기본 메커니즘

NL조인은 중첩 루프문의 수행구조와 동일하다.
소트머지 조인, 해시조인도 각각 Sort Area와 Hash Area에 가공해둔 데이터를 이용하는 것 외에 기본적인 조인절차는 동일하다.

(2) 힌트를 이용해 NL조인을 제어하는 방법

SELECT /*+ ordered use_nl(e) */ *
FROM dept d, emp e
WHERE d.deptno = d.deptno

(설명)

ordered : FROM절에 기술된 순서대로 조인하라.
use_nl : NL방식으로 조인하라
즉, dept테이블(Outer Table)을 기준으로 emp테이블(Inner Table)을 NL방식으로 조인하라.

---

세 개 이상을 조인할 때는 힌트를 아래처럼 사용한다.
SELECT /*+ ordered use_nl(B), use_nl©, use_hash(D) */ *
FROM A, B, C, D
WHERE .....

(설명)
A->B->C->D 순으로 조인하되, B와 조인할 때는 NL로, 이어서 C로 조인할 때도 NL로, D와 조인할 때는 Hash방식으로 조인하라.

---
SELECT /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */ *
FROM A, B, C, D
WHERE ....

(설명)

ordered 대신 leading 힌트를 사용해 조인 순서를 제어할 수도 있다.
9i에서는 leading힌트에 인자를 하나만 줄 수 있어서 세밀한 제어를 할 수 없었다.
따라서, oredered 힌트를 주고, FROM절의 테이블 순서를 일일이 바꿔줘가며 제어했다.
하지만, 10g부터는 leading 힌트에 2개이상의 테이블을 기술할 수 있도록 개선되어, 자유롭게 순서 제어가 가능하다.

(3) NL조인 수행과정 분석
SELECT /* ordered use_nl(e) */
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 테이블
pk_dept : dept.deptno
dept_loc_idx : dept.loc (사용)
emp 테이블
pk_emp : emp.empno
emp_deptno_idx : emp.deptno (사용)
emp_sal_idx : emp.sal

(설명)

d -> e 순서로 NL조인한다.
조인수행 순서
dept_loc_idx 인덱스를 이용하여, 'SEOUL'인 데이터의 ROWID를 얻는다.
ROWID를 이용해서 dept테이블을 찾아가서, gb가 '2'인 데이터를 필터링 한다.
dept테이블에서 deptno를 받아서, emp_deptno_idx 인덱스를 이용하여, ROWID를 얻는다.
ROWID를 이용해서 emp테이블을 찾아가서, sal>=1500인 데이터를 필터링 한다.
위 과정을 통과한 레코드들을 sal컬럼 기준으로 내림차순(desc) 정렬하여, 결과를 리턴한다

--
02. 소트 머지 조인

--

--
03. 해시 조인

--

--
04. 조인 순서의 중요성

--

--
05. Outer 조인

--

--
06. 스칼라 서브쿼리를 이용한 조인

--

--
07. 조인을 내포한 DML 튜닝

--

--
08. 고급 조인 테크닉

--
(1) 누적 매출 구하기

(2) 선분이력 끊기

(3) 데이터 복제를 통한 소계 구하기

(4) 상호배타적 관계의 조인

(5) 최종 출력 건에 대해서만 조인하기

(6) 징검다리 테이블 조인을 이용한 튜닝

(7) 점이력 조회

(8) 선분이력 조인

많은 개발자들이 2개이상의 선분이력을 함께 조회할 때 어려움을 느낀다.

과거/현재/미래의 임의 시점 조회
고객등급과 전화번호 변경이력을 관리하는 두 선분이력 테이블과 조인하는 경우
SELECT c.고객번호, c.고객명, c1.고객등급, c2.전화번호
FROM 고객 c
, 고객등급변경이력 c1
, 전화번호변경이력 c2
WHERE c.고객번호 = 123
AND c1.고객번호 = c.고객번호
AND c2.고객번호 = c.고객번호
AND :dt BETWEEN c1.시작일자 AND c1.종료일자
AND :dt BETWEEN c2.시작일자 AND c2.종료일자

현재 시점 조회

미래 시점 데이터를 미리 입력하는 예약기능이 없는 경우, '='조건으로 만들어주는 것이 효과적이다.
SELECT c.고객번호, c.고객명, c1.고객등급, c2.전화번호
FROM 고객 c
, 고객등급변경이력 c1
, 전화번호변경이력 c2
WHERE c.고객번호 = 123
AND c1.고객번호 = c.고객번호
AND c2.고객번호 = c.고객번호
AND c1.종료일자 = '99991231'
AND c2.종료일자 = '99991231'

미래 시점 데이터를 미리 입력하는 예약기능이 있는 경우, sysdate와 BETWEEN을 사용해야 한다.
SELECT c.고객번호, c.고객명, c1.고객등급, c2.전화번호
FROM 고객 c
, 고객등급변경이력 c1
, 전화번호변경이력 c2
WHERE c.고객번호 = 123
AND c1.고객번호 = c.고객번호
AND c2.고객번호 = c.고객번호
AND TO_CHAR(SYSDATE, 'yyyymmdd') BETWEEN c1.시작일자 AND c1.종료일자
AND TO_CHAR(SYSDATE, 'yyyymmdd') BETWEEN c2.시작일자 AND c2.종료일자

Between

선분이력 조건이 상수가 아니라, 특정 테이블로부터 읽히는 미지의 일자인 경우는 Between 조인을 사용한다.
ex) 과거 20년동안 당일 최고가로 장을 마친 종목을 조회
SELECT a.거래일자, a.종목코드, b.종목한글명, b.상장주식수 ...
FROM 일별종목거래및시세 a
, 종목이력 b
WHERE a.거래일자 BETWEEN TO_CHAR(ADD_MONTHS(sysdate, -20*12), 'yyyymmdd') AND TO_CHAR(sysdate-1, 'yyyymmdd')
AND a.종가 = a.최고가
AND b.종목코드 = a.종목코드
AND a.거래일자 BETWEEN b.시작일자 AND b.종료일자
=> 이 경우, 거래가 일어난 시점의 종목명과 상장주식수를 가져옴

SELECT a.거래일자, a.종목코드, b.종목한글명, b.상장주식수 ...
FROM 일별종목거래및시세 a
, 종목이력 b
WHERE a.거래일자 BETWEEN TO_CHAR(ADD_MONTHS(sysdate, -20*12), 'yyyymmdd') AND TO_CHAR(sysdate-1, 'yyyymmdd')
AND a.종가 = a.최고가
AND b.종목코드 = a.종목코드
AND TO_CHAR(sysdate, 'yyyymmdd') BETWEEN b.시작일자 AND b.종료일자
=> 현재시점의 종목명과 상장주식수를 가져옴

(9) 선분이력 조인 튜닝
정해진 시점을 기준으로 선분이력과 단순 조인할 때

특정 회사를 통해 가입한 모든 고객의 연체금액 조회
SELECT *
FROM 고객 a
,고객별연체이력 b
WHERE a.가입회사 = 'C70'
AND b.고객번호 = a.고객번호
AND '20050131' BETWEEN b.시작일 AND b.종료일 -- 정해진 시점을 기준으로 선분이력과 단순조인
=> 인덱스 구성상 시작일이 종료일보다 선행인 경우, 2005년 1월 31일보다 작거나 같은 이력을 모두 스캔함
종료일이 시작일보다 선행인 경우, 2005년 1월 31일보다 크거나 같은 이력을 모두 스캔함
인덱스를 고객번호+종료일+시작일로 구성하는 경우
샘플테이터의 시작일의 MIN값은 20050103, MAX값은 25520801이므로,
종료일이 선행되도록 인덱스를 구성하면, 종료일이 20050131보다 크거나 같은 데이터가 많아 비효율적
인덱스를 고객번호+시작일+종료일로 구성하는 경우
시작일이 선행되도록 인덱스를 구성하면, 시작일이 20050131보다 작거나 같은 데이터가 소량이므로 효율적
특정 회사를 통해 가입한 고객만 조회하는 것이 아니라, 전체 고객을 대상으로 한다면, NL조인보다 해시조인이 유리함
고객별연체이력을 해시테이블로 빌드하더라도, 각 고객별로 한건의 이력만 해시테이블에 담으므로 비효율이 없음
Between 조인일 때는 큰 비효율을 초래하기도 함.

Between조인 튜닝 - 조회대상이 많지 않을 때
SELECT *
FROM 고객 a
,고객별연체이력 b
WHERE a.가입회사 = 'C70'
AND b.고객번호 = a.고객번호
AND a.서비스만료일 BETWEEN b.시작일 AND b.종료일

고객테이블에는 서비스만료일이 20591005부터 25520801까지 10개의 값밖에 없으므로, 시작일자가 선행인 인덱스인 경우는 스캔을 끝가지 하고서야 조건을 만족하는 이력 데이터를 찾을 수 있다.
인덱스를 종료일+시작일로 하더라도 별반 나아지지 않는다.
rownum과 index힌트를 적절히 사용하면, 인덱스 구성에 상관없이 한건만 읽을 수 있는데, 위의 쿼리로는 가능하지 않다.
스칼라 서브쿼리나 중첩된 서브쿼리형태로 바꾼다면 가능하다.
SELECT a.고객명, a.거주지역, a.주소, a.연락처
,(SELECT /*+ index_desc(b 고객별연체이력_IDX01) */ 연체금액
FROM 고객별연체이력 b
WHERE b.고객번호 = a.고객번호
AND a.서비스만료일 BETWEEN 시작일 AND 종료일
AND rownum <= 1) 연체금액
FROM 고객 a
WHERE 가입회사 = 'C70'
만약, 연체금액과 연체개월수 두컬럼을 읽을 경우, 컬럼을 문자열로 연결후 SUBSTR하거나, 스칼라서브쿼리에서 rowid만 취하고, 고객별연체이력을 한 번 더 조인하는 방법을 사용할 수 있다.

Between 조인 튜닝 - 조회대상이 많지만, 대상별 이력이 적을 때

Random 액세스 위주의 NL조인보다 해시 조인을 이용한다.

Between 조인 튜닝 - 대상별 이력이 많을 때

두 개 이상 월에 걸치는 이력이 생기지 않도록 매월 말일 시점에 강제로 이력을 끊어준다.
두 개 이상 월에 걸치는 이력이 없도록 쿼리시점에 선분이력을 변환해준다.

요약

대상별 이력 레코드가 많을 때의 between조인은 좋은 성능을 내기 쉽지 않다.
마스터 테이블의 이력이라면, 월말로 선분이력을 강제로 끊어주는 방식을 고려한다.
쿼리수행시점에 실시간으로 선분을 끊는 방법도 있지만, 복제되는 양에 따라 만족할 만한 성능이 안나올 수도 있고, 쿼리가 복잡해지는 단점도 있다.

(10) 조인에 실패한 레코드 읽기

조인에 실패했을 경우, 정해진 특정 레코드에서 가져온 값을 보여주는 방법
SELECT /*+ ordered use_nl® */
c.통화시간, c.국가코드, c.지역, r.요금
FROM cdr c
, cdr_rating r
WHERE c.통화시간 LIKE '20050315%'
AND (r.국가코드, r.지역) =
(
SELECT c.국가코드, MAX(지역)
FROM cdr_rating
WHERE 국가코드 = c.국가코드
AND 지역 IN (' ', c.지역)
);

인덱스를 두 번 액세스하지 않고 서브쿼리에서 얻은 ROWID로 테이블을 직접 엑세서 하도록 최종적으로 튜닝
(ordered_predicates 힌트를 사용하여 IN-List 뒤쪽에 있는 값을 먼저 실행하도록 제어하여, 튜닝)
SELECT /*+ ordered use_nl® rowid® */
c.통화시간, c.국가코드, c.지역, r.요금
FROM cdr c
, cdr_rating r
WHERE c.통화시간 LIKE '20050315%'
AND r.rowid =
(
SELECT /*+ use_concat(@subq 1) qb_name(subq) ordered_predicates */ rowid
FROM cdr_rating
WHERE 국가코드 = c.국가코드
AND 지역 IN (' ', c.지역)
);

. (by iamheeya97) [2013.01.21]