CREATE TABLE 집계테이블 (
집계일자 VARCHAR2(8), --DAT2
제품코드 VARCHAR2(4), --KEY2
집계수량 NUMBER, --QTY
비지니스번호 NUMBER --BUSINESS_NO
);
CREATE TABLE 현행테이블 (
처리일자 VARCHAR2(8), --DAT1
제품코드 VARCHAR2(4), --KEY1
처리수량 NUMBER, --QTY
비지니스번호 NUMBER, --BUSINESS_NO
수불구분 VARCHAR2(1) --수불구분
);
CREATE INDEX 현행테이블_IND ON 현행테이블(제품코드);
CREATE INDEX 집계테이블_IND ON 집계테이블(제품코드);
-* 인라인뷰의 컬럼을 가공하지 않았을 경우
SELECT 제품코드, SUM(처리수량)
FROM (SELECT 제품코드 제품코드, 처리수량
FROM 현행테이블
WHERE 처리일자 BETWEEN '20071001' AND '20071130'
AND 수불구분 = '1'
UNION ALL
SELECT X.제품코드, 처리수량
FROM 현행테이블 X, 집계테이블 Y
WHERE X.제품코드 = Y.제품코드
AND X.처리일자 BETWEEN '20071001' AND '20071130'
AND X.수불구분 = '2')
WHERE 제품코드 > ' '
GROUP BY 제품코드
CREATE INDEX STATEMENT 100K 195K 34
INDEX BUILD NON UNIQUE 현행테이블_IND
SORT CREATE INDEX 100K 195K
INDEX FAST FULL SCAN 현행테이블_IND 100K 195K
//힌트 RULE
SELECT STATEMENT
SORT GROUP BY
VIEW
UNION-ALL
TABLE ACCESS BY INDEX ROWID 현행테이블
INDEX RANGE SCAN 현행테이블_IND
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID 현행테이블
INDEX RANGE SCAN 현행테이블_IND
INDEX RANGE SCAN 집계테이블_IND
-* 인라인뷰의 컬럼을 가공하였을 경우
SELECT 제품코드, SUM(처리수량)
FROM (SELECT 제품코드||'' 제품코드, 처리수량
FROM 현행테이블
WHERE 처리일자 BETWEEN '20071001' AND '20071130'
AND 수불구분 = '1'
UNION ALL
SELECT X.제품코드||'', 처리수량
FROM 현행테이블 X, 집계테이블 Y
WHERE X.제품코드 = Y.제품코드
AND X.처리일자 BETWEEN '20071001' AND '20071130'
AND X.수불구분 = '2')
WHERE 제품코드 > ' '
GROUP BY 제품코드
SELECT STATEMENT
SORT GROUP BY
VIEW
UNION-ALL
TABLE ACCESS FULL 현행테이블
TABLE ACCESS BY INDEX ROWID 현행테이블
NESTED LOOPS
TABLE ACCESS FULL 집계테이블
INDEX RANGE SCAN 현행테이블_IND
? SELECT COL3, SUM(RESULT1), SUM(RESULT2)
FROM (SELECT COL3, SUM(QTY), AS RESULT1, 0 AS RESULT2
FROM TAB1
WHERE DAT1 BETWEEN :VAL1 AND :VAL2
AND COL2 IN ('1','2')
GROUP BY COL3
UNION
SELECT COL3, 0 AS RESULT1, SUM(QTY) AS RESULT2
FROM TAB1
WHERE DAT1 BETWEEN :VAL1 AND :VAL2
AND COL2 = '5' AND COL4 = 'A'
GROUP BY COL3 )
WHERE OTHER_CONDITION GROUP BY COL3 ;
SELECT COL3, SUM(DECODE(COL2,'5',NULL,QTY)) RESULT1, SUM(DECODE(COL2\|\|COL4,'5A',QTY)) RESULT2
FROM TAB1
WHERE DAT1 BETWEEN :VAL1 AND :VAL2
AND COL2 IN('1','2','5')
GROUP BY COL3
HAVING OTHER_CONDITION
-* DAT1의 범위도 한번에 액세스하므로 많이 개선된 형태이다.
-* 인덱스와 상황에 따라 더 개선될 여지가 있다
UNION과 UNION ALL
{section}
{column:width=50%}
SELECT /*+ RULE */
제품코드, SUM(처리수량)
FROM (SELECT 제품코드, 처리수량
FROM 현행테이블
WHERE 처리일자 BETWEEN '20071001' AND '20071130'
AND 수불구분 = '1'
UNION ALL
SELECT X.제품코드, 처리수량
FROM 현행테이블 X, 집계테이블 Y
WHERE X.제품코드 = Y.제품코드
AND X.처리일자 BETWEEN '20071001' AND '20071130'
AND X.수불구분 = '2')
WHERE 제품코드 LIKE '1%'
GROUP BY 제품코드
SELECT STATEMENT
SORT GROUP BY
VIEW
UNION-ALL
TABLE ACCESS BY INDEX ROWID 현행테이블
INDEX RANGE SCAN 현행테이블_IND
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID 현행테이블
INDEX RANGE SCAN 현행테이블_IND
INDEX RANGE SCAN 집계테이블_IND
--중복된 조건이 있네...하나로 만들어야지
SELECT DECODE(X.수불구분, '1', X.제품코드, Y.제품코드), SUM(처리수량)
FROM 현행테이블 X, 집계테이블 Y
WHERE (X.수불구분 = '1' OR
X.수불구분 = '2' AND Y.제품코드 = X.제품코드)
AND X.처리일자 BETWEEN '20071001' AND '20071130'
AND X.제품코드 LIKE '1%'
GROUP BY DECODE(X.수불구분, '1', X.제품코드, Y.제품코드);
SELECT STATEMENT
SORT GROUP BY
CONCATENATION
HASH JOIN
TABLE ACCESS BY INDEX ROWID 현행테이블
INDEX RANGE SCAN 현행테이블_IND
INDEX FAST FULL SCAN 집계테이블_IND
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID 현행테이블
INDEX RANGE SCAN 현행테이블_IND
OR조건에 의해 아래처럼 실행계획이 수립될 가능성이 높당
중복된 처리를 발생
SELECT DECODE(X.수불구분, '1', X.제품코드, Y.비지니스번호), SUM(처리수량)
FROM 현행테이블 X, 집계테이블 Y
WHERE X.수불구분 = '1'
AND X.처리일자 BETWEEN '20071001' AND '20071130'
AND X.제품코드 LIKE '1%'
GROUP BY DECODE(X.수불구분, '1', X.제품코드, Y.비지니스번호)
UNION ALL
SELECT DECODE(X.수불구분, '1', X.제품코드, Y.비지니스번호), SUM(처리수량)
FROM 현행테이블 X, 집계테이블 Y
WHERE X.수불구분 = '2' AND Y.제품코드 = X.제품코드
AND X.처리일자 BETWEEN '20071001' AND '20071130'
AND X.제품코드 LIKE '1%'
GROUP BY DECODE(X.수불구분, '1', X.제품코드, Y.비지니스번호)
효율적인 실행계획을 위해 수정된 SQL
SELECT NVL(Y.비지니스번호, X.제품코드), SUM(처리수량)
FROM 현행테이블 X, 집계테이블 Y
WHERE Y.제품코드(+) = DECODE(X.수불구분,'2',X.제품코드)
AND X.처리일자 BETWEEN '20071001' AND '20071130'
AND X.제품코드 LIKE '1%'
GROUP BY NVL(Y.비지니스번호, X.제품코드)
SELECT STATEMENT 573 10K 69
SORT GROUP BY 573 10K 69
HASH JOIN OUTER 15K 280K 23
TABLE ACCESS BY INDEX ROWID 현행테이블 190 2K 18
INDEX RANGE SCAN 현행테이블_IND 1K 2
TABLE ACCESS FULL 집계테이블 7K 29K 4