제3장 인라인뷰의 활용

8. SQL 기능확장을 위한 중간집합 생성

1. SQL의 적절한 활용
   • Application에서 많은 절차를 기술하여 처리하는 것을 대부분 하나의 SQL로 처리 가능
   • Optimizer는 SQL 단위로 최적화 지원 - 실행계획을 잘 제어하면 훨씬 생산적이고 효율적
2. 합계나 소계를 구하는 온라인 화면
   • 조인을 이용한 소계처리 - 하나의 SQL로 처리할 수 있음
   • 인라인뷰를 활용한 중간집합을 활용하여 처리
3. Example)
   
   • TAB1
     • 기본키 : ITEM+SEQ
   • TAB2
     • 월별로 ITEM으로 집계
   • 일관성이 깨지는 경우 자주 발생
     • 일관성이 흐트러진 항목들을 찾아 차이 표시

실습용 테이블 생성

CREATE TABLE COPY_T
 AS
  SELECT LEVEL AS NO,To_char(LEVEL,'09') AS NO2
  FROM   DUAL
  CONNECT BY LEVEL <= 31;

CREATE TABLE TAB1 (
  ITEM CHAR(1)   NOT NULL,SEQ NUMBER   NOT NULL,AMT NUMBER   NULL,YMD VARCHAR(8)   NULL);
CREATE UNIQUE INDEX XPKTAB1 ON TAB1 (
      ITEM ASC,
      SEQ ASC);

ALTER TABLE TAB1
ADD CONSTRAINT XPKTAB1 PRIMARY KEY ( ITEM,SEQ );

CREATE TABLE TAB2 (
  ITEM CHAR(1)   NULL,AMT NUMBER   NULL,YM VARCHAR(6)   NULL);

INSERT INTO TAB1
(SELECT 'A',1,100,'20090301' FROM   DUAL
 UNION ALL
 SELECT 'A',2,200,'20090305' FROM   DUAL
 UNION ALL
 SELECT 'A',3,150,'20090301' FROM   DUAL
 UNION ALL
 SELECT 'B',1,100,'20090302' FROM   DUAL
 UNION ALL
 SELECT 'B',2,120,'20090305' FROM   DUAL
 UNION ALL
 SELECT 'B',3,200,'20090312' FROM   DUAL
 UNION ALL
 SELECT 'D',1,100,'20090303' FROM   DUAL
 UNION ALL
 SELECT 'D',2,300,'20090307' FROM   DUAL
 UNION ALL
 SELECT 'D',3,200,'20090311' FROM   DUAL);

INSERT INTO TAB2
(SELECT 'A',250,'200903' FROM   DUAL
 UNION ALL
 SELECT 'C',300,'200903' FROM   DUAL
 UNION ALL
 SELECT 'D',500,'200903' FROM   DUAL
 UNION ALL
 SELECT 'A',200,'200903' FROM   DUAL);

원하는 결과

조회 쿼리

SELECT min(decode(No, 1, item))       ITEM,
       min(decode(No, 1, 'TOT', seq)) SEQ,
       sum(decode(sw, 0, amt))        TAB1_AMT,
       sum(decode(sw, 2, amt))        TAB2_AMT,
       sum((1-sw)*decode(No, 1, amt)) 차이
FROM (SELECT item, No, decode(No, 1, 'TOT', seq) seq, sum(amt) amt, 0 sw
      FROM (SELECT item, seq, amt
            FROM TAB1
            WHERE ymd like '200903%') x, COPY_T y
      WHERE y.No <= 2
      GROUP BY item, No, decode(No, 1, 'TOT', seq)
    UNION ALL
      SELECT item, 1 No, 'TOT' seq, amt, 2 sw
      FROM TAB2
      WHERE ym = '200903') a
GROUP BY item, No, seq
ORDER BY a.item, No, seq

• 인라인뷰를 사용하여 특수 목적을 위한 임의의 중간 집합을 다양하게 생성 가능

문서에 대하여

• 최초작성자 : 김원식
• 최초작성일 : 2009년 3월 14일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션2 를 참고했습니다.*