특정 한달 간의 날짜 출력

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;

Posted by 빨강토끼

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


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

Posted by 빨강토끼

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

 

예)

신입사원의 입사정보조회

게시판의 최신 글 내용 조회

로그성 데이터의 정보조회


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

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


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

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

EMPNO FROM BIG_EMP ROWNUM<10 ;



Posted by 빨강토끼

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

   …

}


rownum을 사용하여 부분처리(하나만 조회)를 함으로서 성능 향상을 할 수 있다.

SELECT COUNT(*) CNT FROM EMP

WHERE DEPARTMENT='SALES' 

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

 

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

   …

}


MS-SQL 인 경우 Top N 사용




조건절을 만족하면 첫번째 레코드를 만나는 순간 True를 반환하고 서브쿼리를 종료하는 EXISTS 서브쿼리를
사용하면 성능 향상을 하는 방법도 있다.

 

SELECT COUNT(*) FROM DUAL

WHERE EXISTS(

  SELECT ‘x’ FROM BIG_EMP 

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

);


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

    …

}



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

Posted by 빨강토끼

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

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


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 가 감소되게 된다.


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

Posted by 빨강토끼

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

    );


더 있으면 알려주시요^^

Posted by 빨강토끼
TAG SQL, 쿼리

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


  • 인덱스를 만들거나 수정시 DB 성능 지연 발생
실제 구동하며 서비스하고 있는 DB에 아무리 작은 테이블이라도 인덱스를 생성하는 명령을 실행하면
0.001 초라도 DB서버는 버벅이게 된다.
이것은 no logging 등 별의별 옵션을 사용하도라도 기존 서비스에 영향도가 0% 라고 전혀 말 할 수 없다.
인덱스를 추가하는 것을 100% 반대하는 것은 아니지만 운영중에 인덱스를 추가하거나 수정하는 것은
가급적 피해야한다.
  • 분포도가 좋지 않은 컬럼에 인덱스를 생성하더라도 성능에 영향이 적음.
분포도가 좋지 않다는것은 해당 컬럼의 값이 특정 값에 편중되어 있는 비율이 커서 차라리 Full Scan 을 통하여
조회를 하는 것이 유리하다는 것이다.(인덱스 손익분기점)
인덱스컬럼을 추가하던지(복합인덱스),
다른 컬럼을 사용하여 조회하도록 쿼리 튜닝하던지,
Full Scan을 사용하는 방법을 고려해야 한다.
  • INSERT, UPDATE, DELETE 시에 성능 저하
인덱스를 추가하게되면 당장 SELECT 시에 성능향상을 기대할 수 있겠지만,
INSERT, UPDATE, DELETE 시에 오히려 성능이 저하된다.
일일히 해당 인덱스에서도 INSERT, UPDATE, DELETE 가 수행되기 때문이다.


  • 저장공간의 낭비

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

Posted by 빨강토끼

만일 총 직원수가 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';




Posted by 빨강토끼

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

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

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

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

쿼리를 만들려 한다면,


SELECT * FROM BIG_EMP 

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


개발자는 당연히 부서번호(DEPTNO)가 60 인 직원을 먼저 조회한 후에 
메니져사원번호(MGR)가 7698 인 직원인지
판단하는 로직으로 조회쿼리를 만들고 싶어 할 것이다.

물론 DEPARTMENT와 LOCATION 둘중하나의 컬럼에만 인덱스가 생성되어있다면 그 컬럼을 먼저
사용할것이므로 순서를 고민하지 않겠지만 두컬럼이 동일한 조건이라면
  • MGR='7698' AND DEPTNO='60'
  • DEPTNO='60' AND MGR='7698'
위에 두가지중에 무엇이 더 나을까 고민을 할 수도 있다.

규칙(RULE) 기반 옵티마이져라면 
  1. 가장 나중에 생성한 인덱스가 있는 컬럼부터 파싱
  2. AND 조건이면 WHERE 절에서 가까운 순서대로, OR 조건이면 맨오른쪽에서 부터 순서대로

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







비용(COST) 기반 옵티마이져(대부분의 DB가 이것을 사용함)라면 고민 할 필요가 없다.
옵티마이져가 알아서 순서를 정하기 때문이다. 


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

Posted by 빨강토끼
벨랜튜레이즈 알고리즘

인덱스 구조
blockA + blockL + fileA
8 + 4 + 4

같은 값이면 로우아이디순으로 소팅되어있음

인덱스사용못하는 경우
  • 인덱스를 가공 하는 경우 ( 인덱스는 like, <>, in 등 다 사용할수 있음) 
    •  좌변을 가공하지마라
    • 함수기반 인덱스 가용가능
  • NOT 부정형 조건 사용
    • bitmap index에서는 가능
  • NULL, NOT NULL
  • 옵티마이져가 취사선택
    • 의도적으로 인덱스 사용못하도록 하기
인덱스 머지 방식 실행계획

최근에 만들어진것 -> SQL 파싱할때 뒤에서 부터 파싱

힌트 / suppressing / 옵티마이저




컬럼의 형변환

NLS_DATE_FORMAT = ‘DD-MM-YY’ => ‘yyyyMMdd'



nul = chr ==> num = TO_NUM(chr)

6개컬럼 이상일때
분포도가 10~15% 이내일때
분포도가 이내가 아니여도 절대량이 많으면 클러스트링
손익분기점

인덱스 머지 보다 결합인덱스를 잘 사용하자.

인덱스머지가 불리한경우 . 분포도차이가 클때
인덱스머지가 유리한경우. 분포도차이가 적을때, 부분범위처리


Posted by 빨강토끼
TAG SQL