좀더 나은 나를 위하여

특정 한달 간의 날짜 출력

SELECT TO_CHAR(TO_DATE('20160701','yyyymmdd') + LEVEL - 1, 'yyyymmdd') AS DAY  
FROM DUAL  
CONNECT BY LEVEL <= ADD_MONTHS(TO_DATE('201607','yyyymm'),1) - TO_DATE('201607','yyyymm');  

1~30 까지 출력 
SELECT LEVEL 
FROM DUAL 
CONNECT BY LEVEL <= 30;

이미 테이블이 생성된 테이블이 없을 경우에만 테이블이 생성되도록 하는 쿼리

CREATE TABLE IF NOT EXISTS customers (id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(30), last_name VARCHAR(30));

조회하고자 하는 데이터의 특성상 가장 최근의 데이터부터 역순으로 인덱스를 조회하는 것이 유리할 경우가 있다.

예)

신입사원의 입사정보조회

게시판의 최신 글 내용 조회

로그성 데이터의 정보조회

내림차순인덱스 생성하는 방법

CREATE INDEX 인덱스명 ON BIG_EMP (EMPNO DESC)

조회시 힌트를 사용하는 방법

SELECT /*+ INDEX_DESC  (BIG_EMP 인덱스명) */

EMPNO FROM BIG_EMP ROWNUM<10 ;

데이터의 존재여부만 확인하면 되는데 불필요하게 전체 건수를 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

    );

더 있으면 알려주시요^^

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

• 인덱스를 만들거나 수정시 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';

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

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

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

위의 조건에 만족하는 직원의 리스트를 조회하려고 아래와 같은

쿼리를 만들려 한다면,

SELECT * FROM BIG_EMP 

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

• MGR='7698' AND DEPTNO='60'
• DEPTNO='60' AND MGR='7698'

1. 가장 나중에 생성한 인덱스가 있는 컬럼부터 파싱
2. AND 조건이면 WHERE 절에서 가까운 순서대로, OR 조건이면 맨오른쪽에서 부터 순서대로

위의 순서대로 파싱을 한다.

즉. 대부분의 경우 컬럼의 순서에 신경쓸 필요가 없으며 구지 신경을 쓴다면 맨뒤에서 부터 WHERE 조건을 나열하면 된다.