좀더 나은 나를 위하여

--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

    );

더 있으면 알려주시요^^

테이블에 무조건 인덱스를 만드는 것은 비효율적이다.

• 인덱스를 만들거나 수정시 DB 성능 지연 발생

• 분포도가 좋지 않은 컬럼에 인덱스를 생성하더라도 성능에 영향이 적음.

• INSERT, UPDATE, DELETE 시에 성능 저하

• 저장공간의 낭비

과다한 인덱스 생성으로 실제 테이블공간과 비슷하거나 더 많은 저장소 공간을 차지하는 배보다 배꼽이 더 큰 경우도 발생되고 있다.

만일 총 직원수가 3만명인 사원테이블(BIG_EMP)에

메니져사원번호(MGR )가 7698 인 직원이 10,000 명이고,

부서번호(DEPTNO )가 60 인 직원이 1,200 명인 상황에서 

SELECT PAYMENT FROM BIT_EMP 

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

위와 같은 쿼리에서 만일 메니져사원번호(MGR) 컬럼만 인덱스가 생성되어있다면

DB는 인덱스를 통하여 메니져사원번호가 '7698' 인 레코드를 검색후 

해당 레코드의 부서번호가 60인지 판단하는 식으로

조회할 것이다.

하지만 이러한 인덱스컬럼의 조건절이 대부분의(15%이상) 레코드의 조건에 매칭되는 경우에서의

인덱스 스캔방식의 조회는 비효율적이다.

이유는 인덱스 스캔방식에서는 실제테이블 레코드를 조회할때 Single Block I/O 를 사용하기 때문이다.

차라리 인덱스컬럼을 무시하고 전체 테이블을 읽는 풀 스캔이 더 효율적이다.

풀스캔은 Multi Block I/O 를 사용한다.

Single Block I/O

인덱스를 사용하여 검색(인덱스 스캔)을 하게 되면 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

1. 인덱스 테이블에서 조건절 검색 MGR='7698' 
2. ROWID 로 테이블 검색 (1만 ROW , 1만번 I/O)
3. 조건절 검색 ( DEPTNO='60')

Multi Block I/O

전체 데이블 검색(풀 스캔)은 데이터를 읽을 때 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽는다.

1. 3만 / 150 = 2백 번 IO
2. 조건절 검색 ( DEPTNO='SALES')

풀 스캔을 유도하는 방법

인덱스가 걸린 컬럼을 가공

SELECT SAL FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR || ‘’ ='7698‘;

힌트를 사용

SELECT /*+ FULL(BIG_EMP) */

  SAL FROM BIG_EMP 

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