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

10.1.실행계획의 분리

• 다양한 형태를 수용할 수 있는 SQL
  • 동적 SQL 생성 : 불필요한 파싱증가 (비추)
  • 처리주관 인덱스 종류로 기준을 잡음 : 처리주관 인덱스에 속하지 않는 조건들은 체크족건일 뿐(강추)

예시)

• 사용자 부여 : '관리부서', '기준일자', '계약번호','계약구분'
• 사용자 부여 조건
  1. 이 조건들은 입력될수도 안될수도 있다,
  2. '계약번호','계약구분' 중 하나는 반드시 입력
  3. '기준일자' 일력되지 않으면 현재 월을 기준
  4. ','계약구분' NULL 허용, 나머지컬럼은 NOT NULL

• 문제쿼리

SELECT 계약번호, 관리부서명, 계약일, 고객형태, 계약구분, 고객주소
  FROM 계약 X, 부서 Y
 WHERE Y.부소코드 = X.관리부서
   AND 계약번호 LIKE :계약번호||'%'
   AND 관리부서 LIKE :관리부서||'%'
   AND 계약일 LIKE NVL(:기준일, TO_CHAR(SYSDATE,'YYYYMMDD')||'%')
   AND NVL(계약구분'X') = NVL(:계약구분,'X'); -- NULL 허용 컬럼의 비교

• 옵티마이져는 입력된 값을 먼저 결합하지 않고 변수값을 그대로 파싱 \-> 실행계획 고정
• 만약 '계약번호'가 선행컬럼인 인덱스를 사용하는 실행계획에서 '계약번호'값이 들어오지 않으면 인덱스를 처음부터 모두 엑세스 하는 최악의 상황이 나타남
• 아래 해결쿼리의 인덱스는 계약번호, 관리부서 각각 인덱스가 따로 있다는 가정

• 해결쿼리

SELECT 계약번호, 관리부서명, 계약일, 고객형태, 계약구분, 고객주소
  FROM (SELECT *
          FROM 계약
         WHERE :계약번호 IS NOT NULL -- 필터링조건
           AND 계약번호 = :계약번호
           AND 관리부서 LIKE :관리부서||'%'
           AND 계약일 LIKE NVL(:기준일, TO_CHAR(SYSDATE,'YYYYMMDD')||'%')
           AND NVL(계약구분'X') = NVL(:계약구분,'X')
         UNION ALL
        SELECT *
          FROM 계약
         WHERE :계약번호 IS NULL    -- 필터링조건
           AND 관리부서 = :관리부서
           AND 계약일 LIKE NVL(:기준일, TO_CHAR(SYSDATE,'YYYYMMDD')||'%')
           AND NVL(계약구분'X') = NVL(:계약구분,'X')
           AND ROWNUM <= 300 -- 처리범위가 너무 넓어지는것 방지위한 방호벽
       ) X, 부서 Y
 WHERE Y.부서코드 = X.부서코드

• 처리주관 인덱스의 유형에 따라 UNION ALL로 분류
• 필터링 조건으로 어떠한 상황에서도 한가지 SELECT만 수행되고 나머지는 공집합이 된다. (공집합은 수행속도에 전혀 영향을 주지 않음)

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

: 다량의 데이터들을 이용해 고도로 가공된 정보를 추출하고자 할 때 엑세스의 효율화 만으로는 원하는 속도를 얻을 수 없는경우(추출데이터가 많을경우) - 집계테이블, 진행관리용 테이블 추가

• 집계테이블을 추가했을경우 (현재까지의 집계정보를 계속 업데이트 함)
  • 넓은 범위의 처리를 현격한 수행속도로 감소시킴 - 장점
  • 온라인 처리의 수행속도 감소(추가 업데이트해야하기 때문에)
  • 데이터가 처리되는 모든 경우에 대해 일일히 다양한 처리를 해야함 - 애써 처리한 로직이 어디가 잘못되었는지 일정시간이 지나고 나면 일관성이 깨져 있곤함.
  • 배치처리로 처리할 경우 전일까지만 반영됨 등등...

• 집계 테이블의 형태
  
• 집계일자 : 해당월의 시작일 ~ 집계처리일자 (집계일자 19980407은 19980401~19980407까지의 합계임)
• 전일까지의 집계에 당일 데이터를 집계하여 이미존재하면 UPDATE, 아니면 INSERT

• 상황설정
• 오늘이 4/9이고 어제 마침 H/W에 이상이 발생하여 '집계일자'는 '19980407'까지만 생성하여 아래와 같이 데이터 추출함

위의 SQL에서 '현행테이블'에서 마지막으로 집계된 일자를 찾아야 한다. 그러기 위해 아래 쿼리가 먼저 수행되어 있어야 할 것이다.

SELECT /*+ INDEX_DESC(A 집계일자_인덱스) */
            집계일자 INTO:시작일
  FROM 집계테이블 A
 WHERE 집계일자 <= '99991231'
    AND ROWNUM = 1;

'집계 테이블'과 '현행 테이블'을 마치 하나의 테이블처럼 만들어 사용 - 뷰로 만들기

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


-- 이뷰를 엑세스 하는 쿼리
SELECT *
  FROM 집계_뷰
 WHERE 발생일자 BETWEEN '19980101' AND '19980409'

-- 이 엑세스쿼리와 뷰쿼리가 병합되어 아래 SQL이 만들어져 수행됨
SELECT 집계일자, 제품코드, 집계수량
  FROM 집계테이블
 WHERE 집계일자BETWEEN '19980101' AND '19980409'
UNION ALL
SELECT 처리일자, 제품코드, 처리수량
  FROM 현행테이블
 WHERE 처리일자BETWEEN '19980101' AND '19980409'

• '현행테이블'에서 불필요한 엑세스로 우리의 목적을 달성 할 수 없다.
• 엑세스쿼리는 부여한 조건은 항상 양쪽에 모두 공급되므로 자신이 부여한 조건이 각각 다른 범위를 가지도록 할 수 있는 방법이 필요하다.
• 이러한 문제 해결을 위해 부등식의 연산에 대해 알아본다

• AND : 교집합이 되며 반드시 필요한 만큼으로 줄어듬
• OR : 합집합이 되며 처리범위가 크게 증가함.

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

-- 이뷰를 엑세스 하는 쿼리
SELECT *
  FROM 집계_뷰
 WHERE 발생일자1 BETWEEN '19980101' AND '19980409'
      AND 발생일자2 >= '19980408' -- INDEX_DESC를 이용하여 먼저 처리되어 나온일자

-- 이 엑세스쿼리와 뷰쿼리가 병합되어 아래 SQL이 만들어져 수행됨
SELECT 집계일자, 제품코드, 집계수량
  FROM 집계테이블
 WHERE 집계일자BETWEEN '19980101' AND '19980409'
      AMD '99999999' >= '19980408'  -- 영원한 참
UNION ALL
SELECT 처리일자, 제품코드, 처리수량
  FROM 현행테이블
 WHERE 처리일자BETWEEN '19980101' AND '19980409'   -- ⓐ
     AMD 처리일자 >= '19980408'  -- 일정 범위를 가지게 됨 -- ⓑ

• 동일한 컬럼이 하나 이상 조건을 가지면 먼저 논리연산을 하여 하나의 조건으로 만든 후 실행계획을 수립하는 것은 아니다.
• 두개 조건중 하나는 '여당'이 되고, 다른 하나는 '야당'이 된다.
• 위의 쿼리는 ⓑ가 '여당'이 된다는 가정하에서 목표가 달성된다
• ⓐ가 '여당'이 된다면 우리의 목표는 물거품이 된다. 즉, 처리범위를 줄여주지 못한다.
• 책에서는 규칙기준일때를 기준으로 쿼리가 나와있으나.. 현재는 비용기준이기 때문에... 힌트를 주는 방식으로 해결해야 할 것 같다.

문서에 대하여

• 최초작성자 : 한남주
• 최초작성일 : 2009년 5월 26일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 대용량 데이터베이스 솔루션 2 (관계형 데이터베이스 실무활용편) 서적을 스터디하면서 정리한 내용 입니다.