[SQL 고수되기] 열한번째 이야기

출처 : 네이버지식인 노하우
http://kin.naver.com/knowhow/detail.nhn?d1id=8&dirId=8&docId=553699&qb=U1FMIOqzoOyImOuQmOq4sCDsl7TtlZzrsojsp7gg7J207JW86riw&enc=utf8&section=kin&rank=1&search_sort=0&spq=0&pid=go1ZH35Y7vwsstEJfvZssc--411835&sid=TozeLPJwjE4AABooIow

안녕하세요. 오랜만에 이야기를 시작합니다. 오늘 할 이야기는 지난번 이야기에서 잠깐 이야기 나누웠던

통계현황 조회에 대해 구체적으로 이야기해볼까합니다.

이거 기억하시죠?

┌────┬───┬───┬───┬───┬───┬───┬───┬────┐
│　사번　│　일　│　월　│　화　│　수　│　목　│　금　│　토　│　전체　│
├────┼───┼───┼───┴───┴───┴───┴───┴────┤
│10010001│10시간│10시간│10시간│10시간│10시간│10시간│10시간│1000시간│
조금 간단한 내용일수도 있지만 오늘 구체적으로 3가지 방법론 적인 접근을 통해 이야기하려고 합니다.

먼저 결과부터 보여드리고 시작하겠습니다.

 

 

파란색 Block은 사원(아르바이트생)별 2010년 01월 근무시간과 추가근무수당입니다.

빨간색 Block은 월합계입니다.

간단하지만 월통계 처리시 많이 볼수있는 내용입니다. 항목별 소계/합계 등...

그럼 이런식으로 결과를 어떤방법이있을까요. 수많은 방식이 있지만 I/O기준으로 구분하여

3가지 정도로 이야기하려고 합니다.

 

그 첫번째는 부분 범위 처리방식입니다. 쉽계 설명드리면 위처럼 Block으로 구분하여 부분적으로 처리하는 방식입니다. 이는 아주 오랜전붜 활용되어 왔던 방식입니다. 물론 그당시엔 집계함수가 없어서 일수도 있습니다.

파랑색 Block 질의)

SELECT max(b.sname)||'('||a.emp_no||')' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6),a.emp_no;

 

빨간색 Block 질의)

SELECT substr(attn_ymd,1,6)||'-합계' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
WHERE a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6);

 

파랑색 Block + 빨간색 Block)

SELECT max(b.sname)||'('||a.emp_no||')' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6),a.emp_no
UNION ALL

SELECT substr(attn_ymd,1,6)||'-합계' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
WHERE a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6)

;

 

위 질의에 문제점은 무엇일까요? 없나요? 음... 또 생각중이시죠.... 제가 첨에 기준을 멀로 한다고 했지요? I/O기준이라 말씀드렸습니다. 즉, WORK_INF라는 테이블의 똑같은 정보(201001)를 두번 I/O하고 있습니다.

불필요한 I/O Scan인 것입니다. 하지만 애석하게도 지금도 이 방법이 활용되는 곳이 굉장히 많습니다. 만약 대상 건수가 100건이라면? 아니 1000만건이라면 어떨까요.. 물론 서버가 좋다면 병렬(Pararell)방식으로 할수있지만 이 또한 서버가 제한적이라면 함부로 쓸수도 없는 노릇입니다. 그래서, 나온 기법이 Catecian조인 기법입니다. 일명 묻지마조인입니다.

 

그럼 다음으로 Catecian 기법에 대해 알아보겟습니다. 일명 복제기법이라고도 하는데요 다음 쿼리를 보시면 아하 하시는 분들도 계실꺼고, 이미 알고 계신분들도 계실겁니다.

원시정보가공)
SELECT div
      ,substr(attn_ymd,1,6) attn_ym
      ,emp_no
      ,ceil((check_out-check_in)*24) work_time
      ,time_allow allows
FROM  WORK_INF a
     ,(SELECT rownum div FROM ALL_OBJECTS WHERE rownum < 3)
WHERE a.attn_ymd like '201001%'
ORDER BY check_in,emp_no,div;

보시면 똑같은 정보가 두개씩 표시되어 있는것을 보실수 있을겁니다. 일명 복제입니다.

1은 사원별 2는 전체합계 이렇게 구분하기 위해 2개로 복제하엿습니다. 하지만 WORK_INF는 한번만 I/O Scan 하였습니다.

 

집계하기)

SELECT decode(div,1,a.emp_no,substr(attn_ymd,1,6)) emp_no
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,(SELECT rownum div FROM ALL_OBJECTS WHERE rownum < 3)
WHERE a.attn_ymd like '201001%'
GROUP BY decode(div,1,a.emp_no,substr(attn_ymd,1,6))

보시면 식별자가눈에 뜨을 것입니다. 일면 내가 정의한 Entity입니다.

 

결과만들기)

SELECT decode(b.sname,'',a.emp_no||'-합계',b.sname||'('||a.emp_no||')') sname
      ,work_time
      ,allows
FROM  (
        SELECT decode(div,1,a.emp_no,substr(attn_ymd,1,6)) emp_no
              ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
              ,trim(to_char(sum(time_allow),'99,999,990')) allows
        FROM  WORK_INF a
             ,(SELECT rownum div FROM ALL_OBJECTS WHERE rownum < 3)
        WHERE a.attn_ymd like '201001%'
        GROUP BY decode(div,1,a.emp_no,substr(attn_ymd,1,6))
      ) a
     ,EMP_INF b
WHERE b.emp_no(+) = a.emp_no

결과는 동일하지만 WORK_INF를 한번만 I/O하여 처리한게 보이실겁니다. 하지만 이것도 불필요한 ALL_OBJECTS라는 테이블을 사용하였습니다. 물론 DUAL이라는 Dummy테이블을 사용해도 되지만 이또만 테이블이기에 불필요한 I/O가 발생한것과 같습니다. 이 방법 역시 아주 많은 곳에서 활용되는 방식입니다.

 

다음 세번째는 위 두가지 방식을 Oracle DB에서 내부적으로 처리하는 방식에 대해 이야기하겠습니다.

일명 집계함수하는 방식입니다. ROLLUP,CUBE...사용해 보신분들도 계시고 들어보신 분들도 계실겁니다. 이것이 처리되는 방식은 앞서 두가지 방식을 내가 하는게 아니라 Oracle이 해준다고 보시면 됩니다.

SELECT decode(GROUPING(a.emp_no),1,substr(attn_ymd,1,6)||'-합계'
,max(b.sname)||'('||a.emp_no||')') sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6),rollup(a.emp_no);

 

간단하죠. 단순하게 내가 합치려는 항목에 대해 Rollup을 걸어주는겁니다. 지금은 사원별로 합계니까 사번에 rollup을 걸었습니다. 이떄 합계와 사원별을 구분할수 있게 하는 키워드가 있습니다. GROUPING이라는 키워드입니다. 이것이 1이면 합계고 0이면 원시값입니다. 위에서는 GROUPING(a.emp_no)값이 1이면 201001 합계이고 0이면 사원별 정보가 되는 것입니다. 실행계획을 보면 훨씬 간단한 것을 보실수 있을겁니다.

 

오늘은 간단하지만 많이 활용되는 통계기법 3가지를 설명드렸습니다. ORACLE 7,8,9....11

각 버젼별로 활용가능한 기법들을 적용하시면 유용하게 활용될수 있는기법입니다.

 

그럼 이야기를 마무리하는 김에 한가지 문제를 내보겠습니다. 다음 그림처럼 쿼리를 만들어보세요.꼭!!!! 그냥 생각만 해보셔도 괜찮습니다.^^ 월별년통계입니다.

 

시간 되시는 분들은 꼭 해보세요
오늘 수고하셨습니다