10. 기타 특이한 형태의 활용사례

10.1. 실행계획의 분리

• ① 우리의 현실과 고민
  • 다양한 형태를 수용할 수 있는 SQL 요구
  • 모든 형태마다 최적의 실행계획이 수립되도록 처리
• ② 동적SQL을 사용한다면 실행순간마다 최적의 실행계획을 새로 수립할 수 있다
  • 전략적으로 잘 구성된 인덱스가 있다는 전제
  • 동적SQL은 사용하기가 번거롭고 불필요한 파싱을 증가시키기 때문에 꼭 필요한 경우에만 사용하는 것이 바람직하다

SELECT 계약번호, 관리부서명, 계약일, 고객형태, 계약구분, 고객주소    
  FROM 계약 X, 부서 Y  
 WHERE Y.부서코드 = X.관리부서    
   AND 계약번호 LIKE :계약번호 || '%'
   AND 관리부서 LIKE :관리부서 || '%'      
   AND 계약일 LIKE NVL(:기준일, TO_CHAR(SYSDATE,'YYYYMM')||'%'      
   AND NVL(계약구분,'X') = NVL(:계약구분,'X')

-- 옵티마이져는 입력된 값을 먼저 바인딩하지 않고 변수값 그대로 파싱을 하기 때문에 부여된 값에 따라 실행계획이 달라지지 않는다
-- 즉, 한가지 실행계획만 수립된다
-- 위 SQL의 전제로 계약번호(NULL허용)가 선행컬럼인 결합인덱스가 있다고 했을때 부여한 조건중 계약번호가 NULL인경우는 전체 인덱스를 액세스를 해야하는 최악의 상황
-- 실행계획의 분리로 해결할 수 있다.

 SELECT 계약번호, 관리부서명, 계약일, 고객형태, 계약구분, 고객주소    
   FROM (SELECT *                   
           FROM 계약                 
            WHERE :계약번호 > ' '       // NOT NULL 
            AND 계약번호 = :계약번호    // 분포도 좋은 계약번호 결합인덱스선행컬럼으로 사용            
              AND 관리부서 LIKE :관리부서 || '%'                  
            AND 계약일 LIKE NVL(:기준일, TO_CHAR(SYSDATE,'YYYYMM')||'%'        
            AND NVL(계약구분,'X') = NVL(:계약구분,'X')               
     
          UNION ALL     
         SELECT *                 
           FROM 계약             
            WHERE :계약번호 IS NULL     // NULL 전체범위 처리          
              AND 관리부서 LIKE :관리부서 || '%'         
            AND 계약일 LIKE NVL(:기준일, TO_CHAR(SYSDATE,'YYYYMM')||'%'            
            AND NVL(계약구분,'X') = NVL(:계약구분,'X')            
            AND ROWNUM <= 300  //처리범위가 너무 넓을때 방호벽으로 사용한다는데 이해가..   
         ) X, 부서 Y  WHERE Y.부서코드 = X.부서코드;

-- 처리주관 인덱스의 유형에 따라 UNION ALL로 부류
-- 계약번호컬럼으로 어느 한 집합은 반드시 공집합
-- 공집합이 된 경우는 액세스가 발생되지 않음
-- OR를 이용하여 비슷한 결과를 낼 수도 있으나 비효율적인 실행계획이 수립됨
--- 논리합연산자의 이해-실행계획 분할방법

10.2. 배치 집계처리로 온라인 액세스

• ① 집계테이블 추가
  • 장점
    • 매우 넓은 범위의 처리 시 현격한 수행속도 감소보장
  • 단점
    • 온라인처리의 수행속도 감소
    • 데이터가 처리되는 모든 경우에 대해 일일이 다양한 처리를 해야한다
    • 데이터의 일관성이 쉽게 깨질 수 있다
    • 배치처리를 하게 되면 전일까지의 데이터만 유효하다
• ② 데이터집계는 배치로 하고 결과는 현재까지의 데이터를 참조할 수 있도록 하자
• ③ ex)저자가 반정규화 대신 알려주는 비법이랍니다

CREATE INDEX 현행테이블_IND ON 현행테이블(처리일자);
CREATE INDEX 집계테이블_IND ON 집계테이블(집계일자);

SELECT 제품코드, SUM(처리수량)
  FROM (SELECT 제품코드, 집계수량 처리수량
          FROM 집계테이블
         WHERE 집계일자 BETWEEN '20071103' AND '20071203'
        UNION ALL
       SELECT 제품코드 AS 제품코드, 처리수량 AS 처리수량
          FROM 현행테이블
         WHERE 처리일자 BETWEEN '20071203' AND '20071204'
       )
 GROUP BY 제품코드

SELECT STATEMENT	
  SORT GROUP BY	
    VIEW	
      UNION-ALL	
        TABLE ACCESS BY INDEX ROWID	집계테이블
          INDEX RANGE SCAN	집계테이블_IND
        TABLE ACCESS BY INDEX ROWID	현행테이블
          INDEX RANGE SCAN	현행테이블_IND



--마지막집계일자 구하기
SELECT /*+ INDEX_DESC(A 집계테이블_IND ) */
       집계일자
  FROM 집계테이블 A
 WHERE 집계일자 <= '99991231'
   AND ROWNUM = 1

--효율적인 SQL을 위해 뷰를 생성
CREATE VIEW 집계_뷰(발생일자, 제품코드, 수량) AS
SELECT 집계일자, 제품코드, 집계수량
  FROM 집계테이블
 UNION ALL
SELECT 처리일자, 제품코드, 처리수량
  FROM 현행테이블

--뷰를 이용한 쿼리
SELECT *
  FROM 집계_뷰
 WHERE 발생일자 BETWEEN '20071103' AND '20071203'

SELECT STATEMENT	
  VIEW	집계_뷰
    UNION-ALL	
      TABLE ACCESS BY INDEX ROWID	집계테이블
        INDEX RANGE SCAN	집계테이블_IND
      TABLE ACCESS BY INDEX ROWID	현행테이블
        INDEX RANGE SCAN	현행테이블_IND

--위 SQL는 아래 SQL과 같은 실행계획을 수립한다.>>

SELECT 집계일자, 제품코드, 집계수량
  FROM 집계테이블
 WHERE 집계일자 BETWEEN '20071103' AND '20071203'
 UNION ALL
SELECT 처리일자, 제품코드, 처리수량
  FROM 현행테이블
 WHERE 처리일자 BETWEEN '20071103' AND '20071203'


--다른 처리범위를 가질 수 있도록 처리

CREATE OR REPLACE VIEW 집계_뷰(발생일자1, 발생일자2, 제품코드, 수량) AS
SELECT 집계일자, '99999999', 제품코드, 집계수량
  FROM 집계테이블
 UNION ALL
SELECT 처리일자, 처리일자, 제품코드, 처리수량
  FROM 현행테이블

SELECT *
  FROM 집계_뷰
 WHERE 발생일자1 BETWEEN '20071103' AND '20071203'
   AND 발생일자2 >= '20071203'

>>

SELECT 집계일자, 제품코드, 집계수량
  FROM 집계테이블
 WHERE 집계일자 BETWEEN '20071103' AND '20071203'
   AND '99999999' >= '20071203'
 UNION ALL
SELECT 처리일자, 제품코드, 처리수량
  FROM 현행테이블
 WHERE 처리일자 BETWEEN '20071103' AND '20071203'
   AND 처리일자 >= '20071203'

--규칙기준 옵티마이져인 경우 동일한 컬럼이 하나이 상의 조건을 가질때 순위의 차이가 있다면 보다 양호한 순위를 가진 조건이 선택되고 
--같은 순위를 가진 다면 먼저 사용된 조건을 사용한다

SELECT *
  FROM 집계_뷰
 WHERE 발생일자2 >= '20071203'
   AND 발생일자1 BETWEEN '20071103' AND '20071203'