9. 상이한 집합의 통일

• 상이한 부분만 보정하면 통일된 방식으로 처리할 수 있는 경우
  • ① 우리의 목표는 복잡하게 얽혀있는 것을 분석하여 핵심을 꿰뚫어 보고, 거기에 상큼한 아이디어를 가미하여 단순/명확하게 해결할 수 있는 능력을 갖추는것
  • ② 그것을 위한 인라인뷰 사용법을 알아보자

9.1 유사한 집합의 통일

• ① 상이한 집합을 인라인뷰안에서 묶어 하나의 집합으로 처리할 수 있다.
• ② 서로 상이한 집합이지만 적절하게 컬럼을 가공하거나, 상수값 혹은 치환한 값을 컬럼으로 만들거나, 필요한 다른 집합을 조인, GROUP BY 등을 이용하여 통일시킴으로써 인라인뷰는 아주 명확한 하나의 집합이 된다.
  • 단 WHERE절에 부여한 조건은 실행계획을 수립할때 UNION 된 각각의 SELECT 문으로 파고든다

• ③ 인라인뷰 내의 컬럼에 함부로 연산이나 함수 등을 사용하여 가공을 하였다면 메인 SELECT 문의 WHERE절에서 아무리 정상적으로 사용하더라도 인덱스를 사용할 수 없으므로 주의해야 한다.

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

9.2 UNION 을 사용한 인라인뷰의 주의사항

• ① 인라인뷰의 사용은 일반적으로 GROUP BY나 UNION 과 같이 사용하는 경우가 많고 이중 UNION을 사용하는 경우 비효율적인 처리가 발생할 확률이 높다

? 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 ;

• 인덱스를 상수화 해서 메인조건이 파고들지 못하기 때문에 잘못된 것이라고 말하는 건지? 잘모르겠다
  -*? 불필요한 UNION을 사용했다는 것
  -*? UNION보다 UNION ALL을 사용해야한다는 것
  -*? 불필요한 GROUP함수를 사용한 것

• 각집합의 범위가 서로 중복된 부분이 있다면 불필요한 액세스가 추가로 발생될 것임
  -*? DAT1의 조건이 서로 같기 때문에 SCAN되는 범위가 중복될 것
  -*? 단 인덱스의 구성에 따라 중복될 수 도 아닐 수도 있다
  • COL2만으로 구성된 인덱스라면 중복이 안될 수 있다
  • DAT1으로만 구성된 인덱스라면 반드시 중복될 것이다
  • DAT1 + COL2로 구성된 인덱스도 중복될 것이다. 같은 순위의 RANGE 인덱스의 사용이라면 뒤에 오는 컬럼은 인덱스로서의 역할이 아닌 체크조건으로 사용되기 때문이다

• 개선 SQL

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의 범위도 한번에 액세스하므로 많이 개선된 형태이다.
-* 인덱스와 상황에 따라 더 개선될 여지가 있다

다른 집합과의 연결 대상이 다르다고 무조건 SELECT를 분리하는 것이 과연 바람직한 것인가?

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