좀더 나은 나를 위하여

데이터의 존재여부만 확인하면 되는데 불필요하게 전체 건수를 count 하는 경우다.

SELECT COUNT(*) AS CNT FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR='7698';

If(rs.getString("CNT") > 0){

    …

}

Or

SELECT * FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR='7698';

If(rs.size() > 0){

   …

}

SELECT COUNT(*) CNT FROM EMP

WHERE DEPARTMENT='SALES' 

  AND LOCATION='SEOUL'  AND ROWNUM <= 1;

If(rs.getString("CNT") > 0){

   …

}

MS-SQL 인 경우 Top N 사용

SELECT COUNT(*) FROM DUAL

WHERE EXISTS(

  SELECT ‘x’ FROM BIG_EMP 

  WHERE DEPT='60' AND MGR='7698'

);

If(rs.getString("CNT") > 0){

    …

}

여러가지 DB에 유연하도록 하기위해서 EXISTS 를 추천한다.

한테이블에 모든 정보를 모아놓으면(반정규화) 조인에 의한 부하가 줄어들거라는 기대를 하게된다.

하지만 고려해야되는 것들이 있다.

1. 저장공간의 낭비가 생긴다.

2. I/O가 증가된다.(특히 블럭단위 조회시)

아래 예가 있다.

만일 사원이 40만명이라면 

40만  레코드 X 168 Byte 

= 67,200,000 Byte

= 64Mbyte

64Mbyte 의 저장공간이 필요하게 된다.

그리고 만일 DB에서 한번에 읽어들이는 블록이 8Kbyte 라면 

한번 I/O 때 48개의 레코드를 읽게 된다.

정규화를 한 위의 사원테이블과 부서테이블은 각각

사원 테이블

40만 레코드 X 46 Byte

= 18,400,000 Byte

= 17.5Mbyte

부서 테이블

100 레코드X 130Byte

= 12Kbyte

약 17.6Mbyte 의 저장공간이면 되고

DB에서 한번에 읽어들이는 블록이 대략 178개 레코드를 읽을 수 있다.

즉

저장공간에서는 3배가 줄어들게 되고

풀 스캔시 4배의 I/O 가 감소되게 된다.

실제 필드에서는 수천만 수억, 수십억의 레코드가 있는 테이블도 있으므로 성능의 차이는 더 커진다.

--1 DESTINCT

SELECT

  DISTINCT A.DEPARTMENT_NAME

FROM

  DEPARTMENTS A

  , EMPLOYEES B

WHERE B.HIRE_DATE >= '20060101'

  AND B.DEPARTMENT_ID = A.DEPARTMENT_ID;

--2 INNER JOIN (ON)

SELECT

  DISTINCT A.DEPARTMENT_NAME

FROM

  DEPARTMENTS A INNER JOIN EMPLOYEES B

  ON B.DEPARTMENT_ID = A.DEPARTMENT_ID

WHERE B.HIRE_DATE >= '20060101';

--3 NATURAL JOIN

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS A NATURAL JOIN EMPLOYEES B

WHERE B.HIRE_DATE >= '20060101';

--4 NATURAL JOIN (USING)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS A JOIN EMPLOYEES B

USING (DEPARTMENT_ID)

WHERE B.HIRE_DATE >= '20060101';

--5 SUBQUERY (IN)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');

--6 SUBQUERY (IN - GROUP)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101'

                       GROUP BY DEPARTMENT_ID

                       );

--7 SUBQUERY (ANY)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = ANY (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');

--8 SUBQUERY (SOME)

SELECT

  DISTINCT DEPARTMENT_NAME

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = SOME (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');

--9 SUBQUERY (EXITST)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE EXISTS(

  SELECT 'X' FROM EMPLOYEES

  WHERE HIRE_DATE >= '20060101'

    AND DEPARTMENT_ID = A.DEPARTMENT_ID

    );

--10 SUBQUERY (연관서브쿼리)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE DEPARTMENT_ID IN (

  SELECT DEPARTMENT_ID FROM EMPLOYEES

  WHERE HIRE_DATE >= '20060101'

    AND DEPARTMENT_ID = A.DEPARTMENT_ID

    );

--11 Scalar subquery

SELECT

  DISTINCT

(SELECT DEPARTMENT_NAME FROM  DEPARTMENTS A WHERE  A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME

FROM EMPLOYEES B

WHERE  B.HIRE_DATE >= '20060101'

AND B.DEPARTMENT_ID IS NOT NULL;

--12 Scalar subquery + FROM 절 (GROUP BY)

SELECT

  (SELECT DEPARTMENT_NAME FROM  DEPARTMENTS A WHERE  A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME

FROM (SELECT DEPARTMENT_ID

      FROM EMPLOYEES

      WHERE HIRE_DATE >= '20060101'

      GROUP BY DEPARTMENT_ID) B

WHERE  B.DEPARTMENT_ID IS NOT NULL;

--13 Scalar subquery + FROM 절 (DISTINCT)

SELECT

  (SELECT DEPARTMENT_NAME FROM  DEPARTMENTS A WHERE  A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEPARTMENT_NAME

FROM (SELECT DISTINCT DEPARTMENT_ID

      FROM EMPLOYEES

      WHERE HIRE_DATE >= '20060101'

      ) B

WHERE  B.DEPARTMENT_ID IS NOT NULL;

--14 FROM절

SELECT

  DISTINCT A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101') B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;

--15 FROM절 (DISTINCT)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DISTINCT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101') B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;

--16 FROM절 (GROUP BY)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101'

                       GROUP BY DEPARTMENT_ID) B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID ;

--17 FROM절 (GROUP BY)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A , (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101') B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID

GROUP BY A.DEPARTMENT_NAME;

--18 HAVING절

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

GROUP BY A.DEPARTMENT_NAME, A.DEPARTMENT_ID

HAVING A.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID

                       FROM EMPLOYEES

                       WHERE HIRE_DATE >= '20060101');

--19 GROUP BY

SELECT

   A.DEPARTMENT_NAME

FROM

  DEPARTMENTS A

  , EMPLOYEES B

WHERE B.HIRE_DATE >= '20060101'

  AND B.DEPARTMENT_ID = A.DEPARTMENT_ID

GROUP BY A.DEPARTMENT_NAME;

--20 ROWNUM         (젤빠름)

SELECT

  A.DEPARTMENT_NAME

FROM DEPARTMENTS A

WHERE DEPARTMENT_ID IN (

  SELECT DEPARTMENT_ID FROM EMPLOYEES

  WHERE HIRE_DATE >= '20060101'

    AND DEPARTMENT_ID = A.DEPARTMENT_ID

    AND ROWNUM <= 1

    );

더 있으면 알려주시요^^