이 자료는 O'Reilly에서 나온 " Mastering Oracle SQL, 2nd Edition"을 공부하면서 몰랐던 부분을 정리하고 있습니다. 
"Mastering Oracle SQL, 2nd Edition"의 저자는 Alan Beaulieu, Sanjay Mishra 이며 2004년에 나온 책이네요. 
알라딘에서 구해 볼 수 있습니다. 

HAVING 절은 항상 쓰면서 따로 메뉴얼을 읽어본적은 없다.
그래서 아마 group 함수의 결과에 대해 조건을 주는거? 이렇게 생각하고 있었는데, 
역시 메뉴얼을 읽어야해 ~ 

HAVING 절은 GROUP BY 절에 의해 나오는 나올수 있는 결과에 대해 filtering 해주는 역활을 해준다. 
즉 내가 지금까지 생각하던 거와는 달리 
group 함수의 결과 컬럼에 대해서만 조건을 주는 게 아니라 
결과 SET group by 절에 의해 나올 수 있는 결과 set 에 대해 조건을 줄 수 있다. 

다음의 예를 보면 .. 
부서별 인원수를 구하는 SQL이다.
SQL> select deptno,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

이 중인 인원수가 4이상인 부서를 출력한다. 즉 group 함수에 의해 계산된 값에 대한 조건이다. 
SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5

아래의 SQL은 group by 에 의해 만들어진 결과에서 deptno가 20 이상인 부서만 출력한다. 
SQL> select deptno,count(*) from emp group by deptno having deptno > 20;

    DEPTNO   COUNT(*)
---------- ----------
        30          6

그러면 group by 절에 의한 결과에 대한 having과 select 절의 where 절은 바꿔 쓸 수 있을까?
아래의 결과를 보면 having deptno > 20 과 where deptno > 20의 결과는 같다. (당연한가?)

SQL> select deptno,count(*) from emp where deptno > 20 group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6

그러나 having 절에 의한 조건은 filter 처리로 계산된 결과에서 특정 조건만을 걸러내고 있으나, 
where 절의 경우 access 처리로 scan의 범위를 줄이고 있음을 알 수 있다. 

SQL> select deptno,count(*) from emp group by deptno having deptno > 20;

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     2 |     6 |     3  (34)| 00:00:01 |
|*  1 |  FILTER                |           |       |       |            |          |
|   2 |   HASH GROUP BY        |           |     2 |     6 |     3  (34)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| EMP_IDX04 |   449 |  1347 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO">20)

SQL> select deptno,count(*) from emp where deptno > 20 group by deptno;

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     2 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|           |     2 |     6 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | EMP_IDX03 |   193 |   579 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO">20)

queyr의 결과는 동일하나 access 하는 범위 자체가 틀리니 이에 대한 주의가 필요하다. 
당연히 access 범위를 줄일 수 있는 조건은 where 절에 기술하는 것이 유리할 듯 !!

근데, 테스트 하다보니 아래처럼 수행해도 결과가 잘 나오더군요. 
select는 count를 했으나 having에는 sum으로 조건을 주어도 결과가 잘 나오네요. 

SQL> select deptno,count(*) from emp group by deptno having sum(sal) > 10000;

    DEPTNO   COUNT(*)
---------- ----------
        20          5

Execution Plan
----------------------------------------------------------
Plan hash value: 3110987654

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |    28 |    13   (8)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     4 |    28 |    13   (8)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |   449 |  3143 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUM("SAL")>10000)

그럼 group by에 의한 수행 결과에 대해 조건을 주는 것이 아니라 
group by에 의해 나올 수 있는 결과에 대해 조건을 줄 수 있다는 거군요..

count(*)와 sum(sal)은 operation 자체가 다른데, 이걸 다 계산하고 있다는 건가? 
아니면 having에 있는 sum()이 query 수행시 참조되는 건가? 

"The HAVING clause is closely associated with the GROUP BY clause. The HAVING clause is used to put a filter on the groups created by the GROUP BY clause. If a query has a HAVING clause along with a GROUP BY clause, the result set will include only the groups that satisfy the condition specified in the HAVING clause."

네, having 절에 있는 group operation도 참조되는 게 맞습니다.. ^^

SQL> select deptno from emp group by deptno having sum(sal) > 10000;

    DEPTNO
----------
        20



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



이 자료는 O'Reilly에서 나온 " Mastering Oracle SQL, 2nd Edition"을 공부하면서 몰랐던 부분만을 정리하고 있습니다.
"Mastering Oracle SQL, 2nd Edition"의 저자는 Alan Beaulieu, Sanjay Mishra 이며 2004년에 나온 책이네요. 
알라딘에서 구해 볼 수 있습니다. 

Oracle GROUP 함수는 다중 사용을 지원한다. 몰랐다...
간단히 말하면 GROUP 함수 안에 GROUP 함수를 사용할 수 있다. 

예를 들자면 ..

부서별 연봉 합계이다. 
SQL> select deptno,sum(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

이 연봉 합계가 가장 높은 연봉 합은 다음처럼 구할 수 있다..

SQL> select max(salary) from (
SQL> select deptno,sum(sal) salary from emp group by deptno;

MAX(SALARY)
-----------
      10875

이걸 중첩 GROUP operation을 쓰면 다음과 같이 쓰일 수 있다. 

SQL> select max(sum(sal)) from emp group by deptno;

MAX(SUM(SAL))
-------------
        10875

SQL> select max(sum(sal)),min(sum(sal)),avg(sum(sal)) from emp group by deptno;

MAX(SUM(SAL)) MIN(SUM(SAL)) AVG(SUM(SAL))
------------- ------------- -------------
        10875          8750          9675

그럼 중첩 GROUP operation은 몇개까지 중첩될 수 있을까? 
아래 SQL이 어떤 의미인지는 모르겠지만..

일단 3개 중첩!!

SQL> select max(sum(count(sal))) from emp group by deptno;
select max(sum(count(sal))) from emp group by deptno
               *
ERROR at line 1:
ORA-00935: group function is nested too deeply

SQL> !oerr ora 935
00935, 00000, "group function is nested too deeply"
// *Cause:
// *Action:

GROUP function에 dept의 제한이 있는게 확인 된다. 
그럼 SUBQUERY를 사용하게 되면.. 

SQL> select max(a) from (
SQL>   select sum(b) a from  (
SQL>    select count(sal) b from emp group by deptno ));

    MAX(A)
----------
        14

잘 되는 군요.

다들 아시는 내용이겠지만, 저는 몰랐기 때문에 정리합니다 
-,.-



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



서점에서 몇번 오라클 관련 서적을 샀었는데, 많이 실망한 적이 많이 있었습니다.
그때는 오라클에 대해 잘 모를 때였던 거 같은데,
나중에 보니 tablespace를 작업공간이라고 해석해 놓았던 책도 있더군요.. --;

다음의 리스트는 오라클 전문가가 되기 위해 읽을만한 추천책입니다.

물론 이제 막 십년 밖에 안된 야매인 제가 추천해드리는 책은 아니고,
http://oracledoug.com 블로그를 운영하는 "Douglas Ian Burns"라는 사람이 추천한 책입니다.
이분은 자그마치 18년의 경력을 갖고 있다시는 군요..
저도 18년 정도 하면 이분 정도의 공력을 갖을 수 있을까..

걱정입니다.. --;
1. Chris Date's Database In Depth from O'Reilly
2. Oracle Concepts manual
3. Expert One-on One: Oracle Written by Tom Kyte
4. Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
5. Practical Oracle 8i
6. Cost Based Oracle: Fundamentals
7. Optimizing Oracle Performance
8. Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning
 

원문: http://oracledoug.com/books.html

이분이 한국인이 아니기 때문에 당연히 외국 원서 입니다. ㅋ
국내에 번역 된 책도 많이 있는데, 알라딘에서 찾아 링크 좀 걸까 했는데, 국내에서는 절판이나 품절이 많네요.




오라클 9i online documents 입니다.

Oracle 9i release 1 online documents
Oracle 9i release 2 online documents




오라클 10g documents library 입니다.

* oracle 10g release 2 (10.2) documents library 
* oracle 10g release 1 (10.1) documents library




+ Recent posts