3과목 SQL 고급 활용 및 Tuning
4장 Index와 Join
4.4 고급 Join 기법
Inline View 활용
대부분의 JOIN은 1:M 관계이다.
M쪽 집합을 1쪽 집합 단위로 Grouping해야 한다면 미리 Grouping한 뒤 JOIN하는 것이 유리하다.
M쪽 집합을 1쪽 집합 단위로 Grouping해야 한다면 미리 Grouping한 뒤 JOIN하는 것이 유리하다.
SELECT MIN(b.상품명) 상품명, SUM(a.판매수량) 판매수량, SUM(a.판매금액) 판매금액
FROM 일병상품판매 a, 상품 b
WHERE a.상품코드 = b.상품코드
AND a.판매일자 BETWEEN '20090101' AND '20091231'
GROUP BY b.상품코드;
SELECT STATEMENT OPTIMIZER=ALL_ROWS
SORT GROUP BY
NESTED LOOPS
TABLE ACCESS FULL 일별상품판매 (Table)
TABLE ACCESS BY INDEX ROWID 상품 (Table)
INDEX UNIQUE SCAN 상품_PK (Index)
위 상황에서 일별상품판매에서 조건에 맞는 모든 record에 대해서 상품 과 JOIN을 수행한 후에 마지막에 SORT GROUP BY를 수행하였다. 미리 상품코드 별로 GROUP BY를 한 후에 JOIN을 하는 것으로 수정해 보자.
SELECT b.상품명, a.판매수량, a.판매금액
FROM (SELECT 상품코드, SUM(판매수량) 판매수량, SUM(판매금액) 판매금액
FROM 일별상품판매
WHERE 판매일자 BETWEEN '20090101' AND '20091231'
GROUP BY 상품코드) a, 상품 b
WHERE a.상품코드 = b.상품코드;
SELECT STATEMENT OPTIMIZER=ALL_ROWS
NESTED LOOPS
VIEW
SORT GROUP BY
TABLE ACCESS FULL 일별상품판매 (Table)
TABLE ACCESS BY INDEX ROWID 상품 (Table)
INDEX UNIQUE SCAN 상품_PK (Index)
베타적 관계의 JOIN
- 상호배타적 관계란 ?
- 어떤 엔터티가 두 개 이상의 다른 엔터티의 합집합과 관계를 갖는 것
- e.g. 작업지시 Table의 작업일련번호 가 개통신청 Table의 개통신청번호 와도 관계를 가지고, 장애접수 Table의 장애접수번호 와도 관계를 가지는 경우
- 이 경우 3가지로 구현이 가능하다.
- 작업일련번호 1개의 컬럼으로 2개의 Table로 연결하여 관련있는 레코드만 각 테이블에 입력 (#1)
- 개통신청번호, 장애접수번호 두 칼럼을 따로 두고 각 레코드별로 둘 중 하나에만 값을 입력 (#2)
- 접수번호 와 작업구분 칼럽을 두고 작업구분의 값으로 개통신청, 장애접수를 구분 (#3)
- 예제 SQL (#1)
SELECT /*+ ordered use_nl(b) use_nl(c) */
a.작업일련번호, a.작업자ID,
NVL(b.고객번호, c.고객번호) 고객번호,
NVL(b.주소, c.주소) 주소
FROM 작업지시 a, 개통신청 b, 장애접수 c
WHERE a.작업일련번호 = b.작업지시번호(+)
AND a.작업일련번호 = c.장애접수번호(+)
AND a.접수일자 BETWEEN :fromDate AND :toDate;
- 예제 SQL (#2)
SELECT /*+ ordered use_nl(b) use_nl(c) */
NVL(a.개통신청번호, b.장애접수번호) 작업일련번호,
a.작업자ID,
DECODE(a.개통신청번호, NULL, c.고객번호, b.고객번호) 고객번호,
DECODE(a.개통신청번호, NULL, c.주소, b.주소) 주소
FROM 작업지시 a, 개통신청 b, 장애접수 c
WHERE a.개통신청번호 = b.개통신청번호(+)
AND a.장애접수번호 = c.장애접수번호(+)
AND a.접수일자 BETWEEN :fromDate AND :toDate;
-- 위의 경우 필요없는 JOIN 시도 횟수가 많아지기 때문에 2가지 경우를 나누어서 UNION ALL하는게 효율이 좋다.
SELECT a.개통신청번호 작업일련번호,
a.작업자ID, b.고객번호, b.주소
FROM 작업지시 a, 개통신청 b
WHERE a.개통신청번호 = b.개통신청번호
AND a.개통신청번호 IS NOT NULL
AND a.접수일자 BETWEEN :fromDate AND :toDate
UNION ALL
SELECT a.장애접수번호 작업일련번호,
a.작업자ID, b.고객번호, b.주소
FROM 작업지시 a, 장애접수 b
WHERE a.장애접수번호 = b.장애접수번호
AND a.장애접수번호 IS NOT NULL
AND a.접수일자 BETWEEN :fromDate AND :toDate
- 예제 SQL (#3)
SELECT a.작업일련번호,
a.작업자ID, b.고객번호, b.주소
FROM 작업지시 a, 개통신청 b
WHERE a.작업일련번호 = b.개통신청번호
AND a.작업구분 = 1
AND a.접수일자 BETWEEN :fromDate AND :toDate
UNION ALL
SELECT a.작업일련번호,
a.작업자ID, b.고객번호, b.주소
FROM 작업지시 a, 장애접수 b
WHERE a.작업일련번호 = b.장애접수번호
AND a.작업구분 = 2
AND a.접수일자 BETWEEN :fromDate AND :toDate
마지막 예제의 경우 Index가 [작업구분 + 접수일자]로 구성되었다면 읽는 범위의 중복이 없겠지만,
[접수일자 + 작업구분]일 경우 중복해서 읽어야 하며,
[접수일자]만으로 구성된 Index를 사용한다면 작업구분을 필터링하기위해 Random 액세스까지 중복으로 발생한다.
그럴 경우 아래와 같이 수정을 하면 중복 액세스의 비효율을 해소할 수 있다.
[접수일자 + 작업구분]일 경우 중복해서 읽어야 하며,
[접수일자]만으로 구성된 Index를 사용한다면 작업구분을 필터링하기위해 Random 액세스까지 중복으로 발생한다.
그럴 경우 아래와 같이 수정을 하면 중복 액세스의 비효율을 해소할 수 있다.
SELECT /*+ ordered use_nl(b) use_nl(c) */
a.작업일련번호, a.작업자ID,
NVL(b.고객번호, c.고객번호) 고객번호,
NVL(b.주소, c.주소) 주소
FROM 작업지시 a, 개통신청 b, 장애접수 c
WHERE b.개통신청번호(+) = DECODE(a.작업구분, 1,a.작업일련번호)
AND c.장애접수번호(+) = DECODE(a.작업구분, 2,a.작업일련번호)
AND a.접수일자 BETWEEN :fromDate AND :toDate;
부등호 JOIN
'=' 조건이 아닌 BETWEEN, LIKE 등과 같은 부등호 연산과 JOIN해야 할 때도 있다.
아래 그림의 왼쪽 그림과 같은 월별지점매출 Table을 이용하여 오른쪽 그림의 누적매출을 구해보자.
Oracle의 Window 함수를 이용하면 간단하다.
SELECT 지점, 판매월, 매출,
SUM(매출) OVER
(PARTITION BY 지점 ORDER BY 판매월
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적매출
FROM 월별지점매출
Window 함수를 사용하지 않고 BETWEEN을 JOIN조건으로 넣어서도 가능하다.
SELECT a.지점, a.판매월, MIN(a.매출) 매출, SUM(b.매출) 누적매출
FROM 월별지점매출 a, 월별지점매출 b
WHERE a.지점 = b.지점
AND a.판매월 >= b.판매월
GROUP BY a.지점, a.판매월
ORDER BY a.지점, a.판매월
BETWEEN JOIN
- 이력관리 방식
- 선분이력
- 시작일자, 종료일자 형식으로 2개의 일자 컬럼으로 이력을 관리
- 특정시점 이력을 조회할때 BETWEEN 조건으로 간편하게 가능
:dt BETWEEN 시작일자 AND 종료일자- 현재시점 :
종료일자 = '99991231'
- PK값 변경이 자주 발생함
- PK를 [Master Key + 시작일자 + 종료일자]로 관리해야 하는데, 새로운 이력이 들어오면 기존의 마지막 종료일자가 변경되어야 함
- 점이력
- 주로 시작일자만을 저장하여 관리
- 특정시점 이력을 조회할때 Subquery를 이용하여 그 시점의 마지막 이력일자를 먼저 구한뒤에 조회
- 선분이력
-- 선분이력으로 관리할때의 특정시점 조회
SELECT 고객번호, 연체금액, 연체기간
FROM 고객별연체금액
WHERE 고객번호 = :no
AND :date BETWEEN 시작일자 AND 종료일자;
-- 점이력
SELECT a.고객번호, a.연체금액, a.연체기간
FROM 고객별연체금액 a,
(SELECT 고객번호, MAX(a.시작일자) 시작일자
FROM 고객별연체금액
WHERE 고객번호 = :no
AND 시작일자 <= :date) b
WHERE a.고객번호 = b.고객번호
AND a.시작일자 = b.시작일자;
-- 또는 체크조건으로
SELECT a.고객번호, a.연체금액, a.연체기간
FROM 고객별연체금액 a
WHERE a.고객번호 = :no
AND a.시작일자 = (SELECT MAX(b.시작일자) FROM 고객별연체금액 b
WHERE a.고객번호 = b.고객번호
AND b.시작일자 <= :date);
- 선분이력 JOIN
- 고객 Table, 등급변경이력 Table, 전화번호변경이력 Table이 있고, 각각의 이력Table에서 선분이력으로 관리하고 있는 경우 특정시점의 등급과 전화번호를 조회할 경우
SELECT a.고객명, b.등급, c.전화번호
FROM 고객 a, 등급변경이력 b, 전화번호이력 c
WHERE a.고객ID = :id
AND a.고객ID = b.고객ID
AND a.고객ID = c.고객ID
AND :dt BETWEEN b.시작일자 AND b.종료일자
AND :dt BETWEEN c.시작일자 AND c.종료일자;
점이력관리에서 ROWID 이용
- 앞서 살펴본 점이력관리의 경우 동일한 Table을 2번 액세스해야 한다.
SELECT a.고객번호, a.연체금액, a.연체기간
FROM 고객별연체금액 a
WHERE a.고객번호 = :no
AND a.시작일자 = (SELECT MAX(b.시작일자) FROM 고객별연체금액 b
WHERE a.고객번호 = b.고객번호
AND b.시작일자 <= :date);
SELECT STATEMENT OPTIMIZER=ALL_ROWS
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF '고객별연체금액'
INDEX (RANGE SCAN) OF 'IDX_고객별연체금액_고객_일자' (NON-UNIQUE)
SORT
FIRST ROW
INDEX (RANGE SCAN (MIN/MAX) OF 'IDX_고객별연체금액_고객_일자' (NON-UNIQUE)
이 경우 Subquery에서 rowid를 구해서 Main Query로 전달해주면 조금 더 성능을 향상 시킬 수 있다.
SELECT a.고객번호, a.연체금액, a.연체기간
FROM 고객별연체금액 a
WHERE a.고객번호 = :no
AND a.rowid = (SELECT /*+ index_desc(b IDX_고객별연체금액_고객_일자) */ rowid
FROM 고객별연체금액 b
WHERE a.고객번호 = b.고객번호
AND b.시작일자 <= :date
AND rownum <= 1);
SELECT STATEMENT OPTIMZER=ALL_ROWS
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF '고객별연체금액'
INDEX (RANGE SCAN) OF 'IDX_고객별연체금액_고객_일자' (NON-UNIQUE)
TABLE ACCESS (BY USER ROWID) OF '고객별연체금액'
COUNT (STOPKEY)
INDEX (RANGE SCAN) OF 'IDX_고객별연체금액_고객_일자' (NON-UNIQUE)
댓글 없음:
댓글 쓰기