[친절한SQL튜닝 요약 정리] 2-2. 인덱스 기본 사용법

친절한SQL튜닝 책 요약 정리

 

 

1. 인덱스를 사용한다는 것

  : 인덱스의 시작점을 정확히 지정해주고 원하는 만큼 스캔하는 것. 

    이것을 Index Range Scan이라고 한다.

 

 

2. Range Scan을 할 수 없는 이유

  : 인덱스의 시작점을 찾을 수 없기 때문에. 아래와 같은 SQL은 시작점을 찾을 수 없다. *WHERE절 LIKE 사용

Ex) WHERE SUBSTR(생년월일, 5, 2) = '05'

      WHERE NVL(주문수량, 0) < 100

      WHERE 업체명 LIKE '%대한%'

 

 

3. 더 중요한 인덱스 사용 조건

  :  <소속팀 + 사원명 + 연령> 순으로 인덱스를 구성할경우

    소속팀 순으로 정렬하고, 소속팀이 같으면 사원명순, 사원명이 같으면 연령순으로 정렬된다

 

     Ex) SELECT * FROM 사원 WHERE 사원명 = '홍길동'

    경우 만족하는 행을 leaf 전 구간에 흩어져 있다. 인덱스의 시작점을 찾을 수 없다.

    즉, Range Scan을 하기 위해서는 가공되지 않은 인덱스 선두 컬럼이 조건절에 있어야 한다.

 

     Ex) SELECT * FROM 사원 WHERE 소속팀 = '%인사%' 

    의 경우 선두 컬럼이 가공 되어 인덱스를 사용하지만, 인덱스 전체를 full scan하므로 비효율 적이다.

 

 

4. 인덱스를 이용한 소트 연산 생략

  : 인덱스는 원래 정렬돼 있기 때문에 index를 기준으로 order by를 할경우 따로 정렬 연산을 수행하지 않는다.

    내림차순, 오름차순 둘다 가능하다.

 

 

5. order by 절에서 컬럼 가공

  : Ex) SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호 

          FROM 주문A

          ORDER BY 주문번호

    > 가공된 인덱스로 ORDER BY 할 경우 소트 연산 생략을 할 수 없다. 아래와 같이 변경

   Ex) SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호 

         FROM 주문A B

         ORDER BY B.주문번호

 

 

6. SELECT-LIST에서 컬럼 가공

  : Ex) 인덱스는 정렬돼 있으므로 연산 생략. / 인덱스 : 장비번호 + 변경일자 + 변경순번

     (1) SELECT MIN(변경순번)

           FROM 상태변경이력

           WHERE 장비번호 = 'C' AND 변경일자 = '20180316'

 

     (2) SELECT MAX(변경순번)

           FROM 상태변경이력

           WHERE 장비번호 = 'C' AND 변경일자 = '20180316'

 

   Ex) 자료형 변경으로 인한 연산 생략 불가 /인덱스 : 장비번호 + 변경일자 + 변경순번

     (1) SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)

           FROM 상태변경이력

           WHERE 장비번호 = 'C' AND 변경일자 = '20180316'

 

   Ex) 아래와 같이 변경. 연산 생략 가능

     (2) SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)

           FROM 상태변경이력

           WHERE 장비번호 = 'C' AND 변경일자 = '20180316'

 

 

7. 자동 형변환

  : 아래와 같은 SQL은 전체 스캔을 실행한다.

    Ex) SELECT * FROM 고객

          WHERE 생년월일 = 19821225

 

    왜냐하면 생년월일은 문자열컬럼이고 19821225 는 숫자이기 때문이다.

    물론 옵티마이저가 자동으로 변환해 주기도 한다.

    하지만 설정 환경이 다르면 컴파일 오류가 나거나 결과집합이 틀려질 수 있므로 포맷을 정확히 지정한다.   

    Ex) SELECT * FROM 고객

         WHERE 생년월일 = '19821225'

    Ex) SELECT * FROM 고객

         WHERE 가입일자 = TO_DATE('01-JAN-2018', 'DD-MON-YYYY')

  

   * 참고

     숫자형 vs 문자형 - 숫자형 승리

     날짜형 vs 문자형 - 날짜형 승리