필자는 가끔 분석함수의 기능에 관해 질문을 받는다. 그때마다 대답을 하지 않고 대신에 질문에 부합하는 Analytic SQL을 보여주고 결과를 분석하게 한다. 바로 답을 주게 되면 개발자가 의존적이 되고 분석함수 각각의 기능도 금방 잊어버리기 때문이다.

 

개발자만 모르는 것이 아니다

얼마 전에 어느 DBA로 부터 요청이 왔다. 자신을 포함한 개발자들이 분석함수를 어려워하니 블로그에 분석함수의 모든 것을 정리한 문서를 올려달라는 것이었다. 물론 오라클 매뉴얼이나 Tomas Kyte Expert One on One 등의 책에 이 함수들의 기능이 있지만 페이지 수가 너무 많고 영문이라는 단점이 있다는 것이었다. 이것은 놀라운 일이다. 개발자뿐 아니라 DBA, 심지어 컨설턴트까지 Analytic Function에 관해 필자에게 질문을 던지곤 한다. Oracle8i 부터 기능이 구현되었으니 기간으로 따지자면 10년 이상 존재했던 함수인데 아직까지......

 

도대체 Analytic Function이 얼마나 어려우면 전문가 까지도 개념이 서지 않는 걸까? 고민 끝에 핵심만 정리한 문서를 올리기로 했다.

 

핵심은 3가지다

분석함수의 기능이 복잡한 것 같지만 사실은 3가지만 알면 90%를 이해한 것이다.

1) Over 절에서 사용하는 Order by의 기능

2) Over 절에서 사용하는 Partition by의 기능

3) Over 절에서 사용하는 Windowing 기능

 

이것이 90% 이다. 대부분의 개발자와 DBA들은 1)번과 2)번에 대해서 많이 알고 있지만 이상하게도 3)번에 대해서 개념이 서질 않는다고 하였다. 따라서 아래의 문서를 다운받아서 공부할 때 3)번을 집중적으로 보기 바란다.

 

그럼 나머지 10%?

나머지는 아래와 같다. 위의 3가지를 안다면 아래의 함수들은 쉽게 이해할 수 있다. 그저 종류가 많을 뿐이다.

 

      RANK, DENSE_RANK, and ROW_NUMBER --> 3가지 함수의 차이점

      FIRST/LAST

      NTILE, WIDTH_BUCKET, CUME_DIST and PERCENT_RANK

      Hypothetical Functions

      FIRST_VALUE/LAST_VALUE , LAG/LEAD

      Reporting Functions/RATIO_TO_REPORT

      Handling null

 

빨강색 부분은 개발자들이 많이 질문하는 것들이다. 참고하기 바란다.

 

Paper라고 다 같은 것은 아니다

매뉴얼이나 관련서적의 문제점은 페이지 수가 많다는 것이다. 예를 들어 분석함수 부분이 60페이지가 넘어간다면 기능을 익히는데 며칠 혹은 몇 주가 걸릴 수 있다. 필자는 페이지 수가 많은 것을 아주 싫어한다. 아래의 문서는 앞쪽의 목차와 중요성, 그리고 뒤쪽의 마무리 부분을 제외하면 9(18 페이지)으로 모든 기능과 개념을 설명하였다. 아마 한 두 시간 이내에 다 볼 수 있을 것이다.

 

Mastering Oracle Analytic Function.pdf

Mastering Oracle Analytic Function ppt 파일



PS

분석함수를 문법이라고 치부해 버리는 사람들이 있다. 그렇지 않다. 많은 경우에 분석함수를 쓰는 것이 SQL 튜닝이 된다. 오죽하면 옵티마이져가 평범한 SQL을 분석함수를 사용하는 것으로 바꾸겠는가?
이제부터 필자에게 분석함수를 질문하는 개발자가 없기를 바란다. ^^


Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2010.03.29 09:38  댓글주소  수정/삭제  댓글쓰기

    안녕하세요? ^^
    이번에도 좋은 포스팅을 올리셨군요. 감사합니다.

    제 경우도 개발할때 분석함수를 사용해서 유용하게 써먹었었고
    튜닝으로 넘어와서도 분석함수는 여전히 성능을 향상시키는데 아주 좋은 기능으로 자리잡고 있습니다.

    예를들어, GROUP BY -> MAX를 사용하는 인라인 뷰 또는 스칼라 서브쿼리가 PUSH_PRED가 안되서 성능이 안좋아진 경우를 분석함수를 사용하여 PUSH_PRED가 되도록 하는 경우를 들 수 있죠...

    분석함수에 대해서 이런 비교도 재밌는 포스팅이 될 것 같은데요.
    즉, 그룹함수와 분석함수에 대한 비교죠. 이런 그룹함수는 분석함수로 변경이 가능한 Case... 또는 불가능한 Case에 대해서요 ^^

    항상 좋은 내용을 공유해 주셔서 감사드립니다.

    ps. 책은 출판됐나요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.03.29 17:30 신고  댓글주소  수정/삭제

      좋은 주제인것 같습니다.
      책은 메켄토시로 변환하는 과정에서 조금 지체가 되었습니다. 그리고 칠레의 지진 때문에 인쇄용 종이 수입이 중단되어 2~3주 정도 늦춰진다고 하는군요. 조금만 기다려 주시기 바랍니다.

  2. 혈기린 2010.03.29 09:44  댓글주소  수정/삭제  댓글쓰기

    오라클분석함수의 정수가 모인 자료네요 감사합니다~~ 테스트 스크립트까지 ^^
    책 너무 기다리고 있습니다 ㅎㅎ

  3. feelie 2010.03.29 10:29  댓글주소  수정/삭제  댓글쓰기

    오늘도 간지러운곳을 글거주시네요..

  4. 알 수 없는 사용자 2010.03.30 11:44  댓글주소  수정/삭제  댓글쓰기

    아.. 정말 대단하십니다... 그리고 감사합니다.

  5. 김시연 2010.04.01 10:24  댓글주소  수정/삭제  댓글쓰기

    안녕하세요~! 출간하실 책명이 "The Logical Optimizer" 군요. 좋은 글 잘 읽고 갑니다~!

  6. 익명 2010.04.01 16:12  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  7. baind 2010.04.02 14:22  댓글주소  수정/삭제  댓글쓰기

    오늘도 즐겁게 공부합니다~
    감사드려요~^^ ㅎ_ㅎ
    매번 좋은 글에 진심으로 감사드립니다~

    꼬리 : 오동규님 책 기다리다가 제 목 2Cm늘어났어요~~

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.04.02 15:17 신고  댓글주소  수정/삭제

      너무 걱정하지 마세요.
      내일이 인쇄용 필름이 마감되는 날 입니다. 책은
      15일 정도에 인쇄가 완료될 예정이고 20일 정도에 교보문고나 yes 24 등에서 주문이 가능할 겁니다.
      감사합니다.

  8. 서상서 2010.08.03 23:34  댓글주소  수정/삭제  댓글쓰기

    분석함수 올리신 쿼리중에 KEEP이라는 명령어를 쓰셨는데...오라클 메뉴얼을 찾아보아도 나오지를 않네요.
    그리고 SQL Rerence에 봐도 나와있지 않네요.

    무슨 역할을 하는 놈인가요?

    정말 많은 채찍질을 하게 하는 좋은 글들을 많이 올려주셔서 감사합니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.08.04 12:54 신고  댓글주소  수정/삭제

      분석함수는 OLTP에서도 사용하지만 태생은 DW용이기 때문에 Oracle 10g Data Warehausing Guide의 21장에 자세히 설명되어 있습니다.

      그리고 KEEP의 용도는 분석함수의 OVER와 같다고 생각하시면 됩니다. 다만 FIRST/LAST 함수에서만 사용한다는 것이 다르긴 합니다.

      예를들면 부서별로 급여를 가장 많이 받는 직원의 LIST를 뽑는다면 한부서에 두명 이상이 나올 수 있겠죠. 즉 부서별 최고연봉자는 한명이 아니라 두명(김연아와 박태환이 둘 다 1억원을 받음)이 라고 가정하면
      MIN 값을 적용하면 김연아가 나올것이고 MAX를 적용하면 박태환이 나올것 입니다.

      PDF파일의 FIRST/LAST 예제를 직접 실행하시고 결과를 분석하시면 아실 것 입니다.

  9. 김봉호 2011.08.30 10:45  댓글주소  수정/삭제  댓글쓰기

    검색으로도 여기 들어올 줄이야 ^^;;

  10. Favicon of http://www.abercrombiefitch-saleuk.org.uk BlogIcon abercrombie and fitch uk 2011.09.26 19:30  댓글주소  수정/삭제  댓글쓰기

    예를들면 부서별로 급여를 가장 많이 받는 직원의 LIST를 뽑는다면 한부서에 두명 이상이 나올 수 있겠죠. 즉 부서별 최고연봉자는 한명이 아니라 두명(김연아와 박태환이 둘 다 1억원을 받음)이 라고 가정하면
    MIN 값을 적용하면 김연아가 나올것이고 MAX를 적용하면 박태환이 나올것 입니다.

  11. Favicon of http://www.cheapburberryshop.org BlogIcon burberry outlet 2011.09.26 19:30  댓글주소  수정/삭제  댓글쓰기

    PDF파일의 FIRST/LAST 예제를 직접 실행하시고 결과를 분석하시면 아실 것 입니다.

  12. Favicon of http://www.perfectreplicawatches.org.uk/ BlogIcon replica watches uk 2011.10.13 13:30  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽고 갑니다~!