h2.1.1 개괄설명
h4.1.2.1 단계적 조인
- 주로 1:M의 관계에 있는 테이블간 조인시 M쪽의 테이블을 적절히 가공하고 필요한 칼럼으로 GROUP BY한 결과를 인라인뷰로 만들고난 후
1쪽 테이블과 조인을 하여 조인횟수를 줄이고자 할 때 사용하는 기법이다. - 인라인뷰는 대단히 편리한 기법이긴 하나 이를 남용할 경우 비절차적 개념의 SQL을 프로그램 코딩처럼 절차적으로 구성하게 되어
SQL에 대한 집합감각을 잃어버리게 하는 부작용이 있다. - 똑같은 결과가 나오는 쿼리문인데도 하나는 단일의 SQL로 만들어져 있으나 다른 하나는 여러 번의 인라인뷰로 감싸져 있다면 두번째 쿼리문은
되도록 많은 요구사항을 한번에 처리해야 한다는 집합성을 잃어버리고 여러 단계에 걸쳐 처리하도록 만들어져 있을 가능성이 많다.
h4.1.2.2 예시테이블
- DEPT:부서테이블(DEPT_CD,DEPT_NM)
- SALE:판매테이블(DEPT_CD,YYMM,SALE_QTY,SALE_AMT)
- DETP:SALE=1:M
- 연결키:DEPT_CD
h2.1.2 문제1
판매테이블에서 특정 월과 특정 사업장의 판매수량과 판매금액을 부서별로 구하라
h4.1.2.1 불합리한 방식
SELECT
Y.DEPT_NM,SUM(X.SALE_QTY),SUM(X.SALE_AMT)
FROM SALE X,DEPT Y
WHERE X.DEPT_CD=Y.DEPT_CD
AND X.YYMM=:IN_DATE
AND X.SAUP=:SAUP
GROUP BY Y.DEPT_NM
- SALE 테이블에서 조건을 만족하는 로우가 10,000건이며 회사에 총 20개의 부서가 있다면 최대 200,000번의 조인이 발생한다.
- 즉 불필요한 연결이 많이 발생하여 시스템에 부하를 줄 우려가 있다.
h4.1.2.2 개선된 방식
SELECT
Y.DEPT_NM,S_QTY,S_AMT
FROM
(
SELECT
DEPT_CD,SUM(SALE_QTY) S_QTY,SUM(SALE_AMT) S_AMT
FROM SALE
WHERE YYMM=:IN_DATE
AND SAUP=:SAUP
GROUP BY DEPT_CD
) X,DEPT Y
WHERE X.DEPT_CD=Y.DEPT_CD
- 먼저 SALE테이블에서 DEPT_CD별로 GROUP BY한 후 인라인뷰로 만든다.
- 가공처리된 인라인뷰와 DEPT테이블이 조인하므로 최대 20번의 조인만 발생한다.
- 그런데 간혹 실행계획을 보면 GROUP BY를 한 결과와 DEPT테이블을 조인하는 것이 아니라 조인 후 GROUP BY하는 실행계획이 나오는 경우가 있다 (불합리한 방식과 동일).
이런 경우 인라인뷰에 ROWNUM>0 코드를 추가하면 확실한 실행계획을 보장받을 수 있다.
h2.1.3 문제2
판매테이블에서 특정 월과 특정 사업장의 판매수량을 구하여 일자별로 나열하라
h4.1.2.1 불합리한 방식
SELECT
DEPT_CD,SUM(SALE_QTY) TOTAL
,SUM(DECODE(SUBSTR(SALE_DATE,7,2),'01',SAL_QTY)) S_01
....................................................
,SUM(DECODE(SUBSTR(SALE_DATE,7,2),'31',SAL_QTY)) S_31
FROM SALE
WHERE YYMM=:IN_DATE
AND SAUP=:SAUP
GROUP BY DEPT_CD
- SUM() 함수내에서의 어떤 가공은 조건을 만족하는 모든 로우에 대해 각각 실행된다.
- 따라서 조건을 만족하는 로우가 10,000건이라면 10000*31을 하여 총 31만번의 DECODE(),SUBSTR() 함수가 실행된다.
- 이는 대용량을 처리하는 프로그램일 경우 속도에 큰 영향을 미칠 수 있다.
h4.1.2.2 개선된 방식
SELECT
DEPT_CD,SUM(S_QTY) TOTAL
,SUM(DECODE(DD,'01',S_QTY)) S_01
...............................
,SUM(DECODE(DD,'31',S_QTY)) S_31
FROM
(
SELECT
DEPT_CD,SUBSTR(SALE_DATE,7,2) DD
,SUM(SALE_QTY) S_QTY
FROM SALE
WHERE YYMM=:IN_DATE
AND SAUP=:SAUP
GROUP BY DEPT_CD,SUBSTR(SALE_DATE,7,2)
)
GROUP BY DEPT_CD
- 조건을 만족하는 로우이 수가 10,000건이고 20개 부서라면 인라인뷰는 GROUP BY DEPT_CD,SUBSTR(SALE_DATE,7,2)에 의해 20개부서*31일=620개의 로우로 줄어든다 .
- 비록 바깥 쿼리에서 다시한번 GROUP BY를 실행하지만 620건에 대한 GROUP BY 이므로 큰 부담이 없다.
- 무엇보다 고무적인 것은 인라인뷰에서 필요한 가공처리를 하여 SUBSTR(),DECODE()함수의 사용횟수가 현격히 줄었다는 것이다.
- 만일 SALE테이블에 매일마다의 판매수량이 있는 것은 아닌데 END USER가 말일까지의 데이타를 화면에 보고 싶다고 해서 해당 월의 말일만큼
데이타를 복제할 필요는 없다. NVL(SUM(DECODE(DD,'31',S_QTY)),0) S_31로 충분하다.
문서에 대하여
- 최초작성자 : 김강환
- 최초작성일 : 2007년 12월 08일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션2 를 참고했습니다.