기획팀에서 이대리가 전산실에 와서 부탁을 한다.

기획실 이대리:"월별 부서별로 2002년도 실적을 좀 뽑아주실수 있나요?"

전산실 김대리:"네 오늘저녁 6시쯤 오시면 실적 보고서를 드릴수 있습니다."

기획실 이대리:"실적을 만드실때 주의사항이 있습니다.
                     월별 부서별로 실적을 뽑을때 만약 20번 부서에서 5월, 7월에
                     실적이 없다고 하더라고 5월,7월 실적을 0 으로 표시해주세요."

전산실 김대리:"네 알겠습니다. 그것은 별로 어렵지 않습니다."   

년월만 들어있는 테이블과 월별부서별실적 테이블의 구조는 아래와 같다.

사용자 삽입 이미지

















월별 부서별 실적테이블의 2002년 실적은 다음 그림과 같다.
모든 월에 실적이 있는것은 아니다.(예를 들면 10번 부서는 2002년도에 1,3,6,7,8,10,11 월에 실적이 없다. )
사용자 삽입 이미지














30번 부서부터는 지면관계상 그림에서 생략하였다.




기획실 이대리의 요구사항은 아래그림과 같다.
(실적이 없는달은 실적을 0 으로 표시함)
사용자 삽입 이미지
































30번 부서의 실적부터는 지면관계상 그림에서 생략하였다.


다행히 월별, 부서별 실적 테이블이 존재하기 때문에 김대리는 묵묵히 월별 실적 SQL을 아래처럼 작성하였다.
아래처럼 작성한 이유는 부서가 20개(10번부터 200번까지) 있기 때문에 부서별로 무조건 12건(1월~12월)을 만들기 위해서 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 10     --> 10번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 20     --> 20번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 30     --> 30번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'
Union all
...........................................................................................중간생략
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
  AND e.DEPTNO(+) = 200     --> 200번부서에 대해서 1월~12월 실적을 만듬.
  AND m.yymm like '2002%'


우연히 김대리의 작업을 지켜보던 전산실 박과장이 한마디 한다.
전산실 박과장 :"김대리 그작업할때 200번이나 노가다(Union All) 할생각이냐?
                     "SQL 공부좀해라"
              
김대리에게 호통을 친 박과장은 자신감 있게 아래의 SQL을 1분만에 만들었다.    
           

SELECT dept_month.deptno, dept_month.yymm, NVL(e.sale_amt,0)
   FROM (SELECT d.deptno, m.yymm
                 FROM ( SELECT c.deptno
                                FROM DEPT c
                               WHERE EXISTS (SELECT 1
                                                          FROM dept_sale_history d
                                                        WHERE d.deptno = c.deptno
                                                            AND d.yymm like '2002%')) d,
                             ( SELECT m.yymm
                                  FROM year_month m         
                                 WHERE m.yymm like '2002%' ) m 
             ) dept_month,                                          --> 월별 부서별 집합을 먼저 만든다.
            dept_sale_history e        
 WHERE dept_month.deptno(+) = e.deptno
      AND dept_month.yymm(+) = e.yymm              

위의 SQL 의 핵심은 모든 부서에 대하여 1월~12월 까지 와꾸?(틀)를 만들어 놓고
부서별 월별실적 테이블과 아우터 조인을 하기위해서 이다.
위의 SQL 에서 EXISTS 를 사용한 이유는 2002 년도에 실적이 있는 부서만 뽑기 위해서다.
하지만 위의 SQL 도 비효율이 있다.
부서별 월별 실적테이블을 2번이나 ACCESS 하였다.

박과장의 작업을 옆에서 지켜보던 신입사원이 고개를 기우뚱 하며 박과장에게 말을 건낸다.
전산실 신입사원:"dept_sale_history" 테이블을 2번 사용하지 않고도 같은 실적을 뽑을수 있습니다."
전산실 박과장 :"그래? 그럼 한번해봐"

신입사원을 지켜보던 박과장은 경악을 금치 못한다.
신입사원이 20초만에 SQL 을 작성하고도 성능은 신입사원의 SQL이 우수했기 때문이다.
단 4줄의 SQL 로 기획팀 이대리의 요구사항을 해결하였다.
박과장은 SQL 을 사용한지 10년이 넘는 배테랑 개발자 이지만 10g 신기능은 써보지 못한 상태였다.
아래의 SQL이 신입사원의 SQL 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM  year_month m LEFT OUTER JOIN dept_sale_history e
           PARTITION BY (e.deptno) ON (m.yymm = e.yymm )
WHERE m.yymm like '2002%';

신입사원이 위의 SQL 을 사용할수 있었던건 처음 배운 SQL 문법이 Oracle 10g 기준이었고
박과장은 Oracle 8 버젼의 SQL을 공부 해었기 때문이다.

위의 Partition Outer Join 은 10g 의 새기능이다.
Partition Outer Join 의 기능을 요약하면 부서별로 중간중에 빠진 월의 실적을 생성해주는 기능이다.     

결론 :
Partition Outer Join 은 10g 의 신기능중 일부에 불과하다.
버전별로 New Features의 중요성을 다시한번 강조하지만 위의 경우와 같이
신기능을 모르면 작업량이 늘어날수 밖에 없고 대부분 성능도 느리다.
또한 Oracle 8.0 시절에 최적화된 SQL 이 항상 Oracle 10g 에서 최적화된 SQL 이라고 볼수 없다.
Oracle 9i 가 나온지는 10년이 됬으며 Oralce 10g 가 나온지도 6년이 지났고 2년전에 Oracle 11g 가 나왔다.
신버젼이 나올때 마다 알라딘의 요술램프처럼 주인님이 사용해주기를 기다리는 마술 같은 여러가지 신기능이 숨어있다는 점을 기억하자.

신고
Posted by extremedb

댓글을 달아 주세요

  1. 금땡이 2008.08.31 10:44 신고  댓글주소  수정/삭제  댓글쓰기

    100% 공감하는 내용입니다. 공부 좀 해야겠네요.

  2. 투덜이 2011.02.22 18:30 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요... 항상 좋은글을 읽고있는 애독자입니다...
    현재는 오동규님께서 추천해주신 Mastering Oracle SQL 2nd Edition 을 잘 보고있습니다.
    근데 한가지 궁금한게 있어 질문을 드립니다.
    ansi 표준의 sql을 이용하여 partition outer join을 구현하셨는데(또 제가 보는 책에서도) 이 문장을 Oracle표준 문장으로
    바꿀 수는 없는지요...
    예로 dept a LEFT OUTER JOIN EMP b ON a.dept_id = b.dept_id를 a.dept_id = b.dept_id(+) 이렇게요...
    아니면 partition outer join은 ansi 표준으로만 구현이 가능한가요??

    제가 아직 초보수준을 벗어나지 못해 무지한 질문을 드린게 아닌지 모르겠네요...

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2011.02.23 10:01 신고  댓글주소  수정/삭제

      투덜이님 반갑습니다.
      투덜이님 말씀대로 partition outer join은 ANSI 표준만 사용할 수 있습니다.

      보시고 계시는 Mastering Oracle SQL 2nd Edition 은 좋은 책입니다. 꼭 정복하시기 바랍니다.

  3. 투덜이 2011.02.23 10:34 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다...
    꼭 정복하도록 노력하겠습니다...
    그리고 항상 좋은내용 잘 보고있습니다..