인덱스 기본 사용법

인덱스를 사용한다는 것

• 인덱스 컬럼을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다. 즉, 인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다.
• 여기서 ‘인덱스를 정상적으로 사용한다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것, 즉 리프 블록 일부만 index Range Scan을 의미한다.
• 인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 한다. 즉 일부가 아닌 전체를 스캔하는 Index full scan 방식으로 작동한다.

 

 

인덱스를 Range Scan 할 수 없는 이유

• 인덱스 스캔 시작점을 찾을 수 없기 때문이다.
• 일정 범위를 스캔하려면 ‘시작'과 ‘끝' 지점이 있어야함.

 

가공되지 않은 값이 저장되어 있는 상황

where substr(생년월일, 5, 2)='05' //생년월일을 substr 함수를 사용해서 가공함

• 가공된 값을 기준으로 검색하려면 스캔 시작점과 스캔 끝지점을 찾을 수 없다.
• 데이터 타입이 서로 다른 비교 (타입을 변환해야 비교가 가능한 경우)도 같은 이유로 불가능하다.

가공하지 않은 주문수량으로 인덱스를 만든 상황

where nvl(주문수량,0) < 100 // 값이 NULL이면 0으로 치환한 값 즉, 가공함

• 역시나 인덱스 스캔 시작지점을 찾을 수 없다.

LIKE로 중간값 / 뒷부분 을 검색할 때

where 업체명 like '%대한%'

• ‘대한'으로 시작하는 값은 특정 구간에 모여 있으므로 가능하지만, ‘대한'을 포함하는 값은 전체 구간에 걸쳐 흩어져있으므로 range scan이 불가능하다.
• 뒷부분을 비교하는 경우도 동일함!

or 조건으로 검색할 경우

where (전화번호 =: tel_no OR 고객명 =: cust_nm)

• 수직적 탐색을 통해 전화번호가 01012345678 이거나 고객명이 홍길동인 어느 한 시작지점을 바로 찾을 수 없다.

 

 

OR 조건으로 인덱스 range 스캔 가능하게 하려면?

OR EXPANSION

select *
from 고객
where 고객명 =: cust_nm--고객명이 선두 컬럼인 인덱스 Range Scanunion all
select *
from 고객
where 고객명 =: tel_no--전화번호가 선두 컬럼인 인덱스 Range Scanand (고객명<> : cust_nm or 고객명 is null)

• OR 조건식을 SQL 옵티마이저가 위와 같은 형태로 변환 가능
• /use_concat/ 힌트를 이용해서 OR EXPANSION 을 유도 가능

 

IN 조건으로 검색할 경우

where 전화번호 in (:tel_no1, :tel_no2)

• IN과 OR은 다른 표현 방식일 뿐 같다.
• 이전의 예시처럼 union all 방식으로 작성하면 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있다. (range scan이 가능) OR 과 IN 조건절은 옵티마이저의 쿼리 변환 기능을 통해 Index Range Scan(IS) 로 처리되기도 한다.
• IN 조건절에 대해서는 옵티마이저가 IN-List Iterator 방식을 사용한다. IN-List 갯수만큼 Index Range Scan을 반복하는 것이다. 이를 통해 UNION ALL 방식으로 변환한 것과 같은 효과를 얻을 수 있다.

 

더 중요한 인덱스 사용 조건

• Index Range Scan이 가능하게 하기 위해 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 한다. 당연히 가공하지 않은 상태로!
• 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan 은 무조건 가능하다!

nono

select     사원번호, 소속팀, 연령, 입사일자, 전화번호
from     사원
where     사원명 = ‘홍길동＇

 

good

• 하지만 문제는, 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은 건 아니다.
• 인덱스를 정말 잘 타는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다.
• 스캔 범위를 줄이는데 전혀 역할을 하지 못하는 선두 컬럼일 수 있기 때문에, 인덱스 스캔 자체를 효율화 할 필요가 있다. 우리의 현 ptl_id처럼....

 

 

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

• Index Range Scan 과정을 거쳐 생성된 결과집합은 인덱스 컬럼 순으로 정렬된 상태이므로 sort order by, min/max 값을 빠르게 추출 할 수 있다. (즉, 소트 연산 생략 효과도 얻게 된다.)
• 예시

• PK를 위 처럼 [장비번호 + 변경일자 + 변경순번] 순으로 구성한 상태변경이력 테이블이 있다고 가정
• 장비번호와 변경일자를 모두 '='조건으로 검색할 때 PK 인덱스를 사용하면 결과집합은 변경순번 순으로 출력됨.
• 옵티마이저는 이런 속성을 활용해 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않는다.
• PK 인덱스를 스캔하면서 출력한 결과집합은 어차피 변경순번 순으로 정렬되기 때문!
• 만약 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있지 않다면, 아래와 같이 SORT ORDER BY 연산 단계가 추가된다.

SELECT * 
FROM 상태변경이력 
WHERE 장비번호 = 'C' 
AND 변경일자 = '20180316' 
ORDER BY 변경순번 

Execution Plan -------------------- 
SELECT STATEMENT Optimizer = ALL_ROWS 
TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력' 
INDEX (RANGE SCAN) OF '상태변경이력_PK' 

Execution Plan (SORT) -------------------- 
SELECT STATEMENT Optimizer = ALL_ROWS SORT (ORDER BY)
TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력' 
INDEX (RANGE SCAN) OF '상태변경이력_PK

• 내림차순(Desc) 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색.
• 오름차순(Asc) 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색.

 

ORDER BY 절에서 컬럼 가공

• 모든 SQL 튜닝 책이 다루는 "인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다"에서 말하는 '인덱스 컬럼'은 대게 조건절에서 사용한 컬럼을 말한다.
• 그런데 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있음!!!
• 예시1.
  • 당연히 가공하지 않은 값을 가공한 값 기준으로 정렬해달라고 했으니 인덱스 안탐.

  SELECT 
  FROM 상태변경이력
  WHERE 장비번호 ='C'
  ORDER BY 변경일자, 변경순번 // 변경일자 + 변경 순번 순으로 정렬되기에 생략
  
  SELECT 
  FROM 상태변경이력
  WHERE 장비번호 ='C'
  ORDER BY 변경일자 || 변경순번 //OR 절로 인해 생략이 불가능 하다
  

• 예시2.
  • 주문_PK 인덱스는 [주문일자 + 주문번호] 순
  • ORDER BY 절이 있어도 정렬 연산을 생략할 수 있는 상태인데도 아래 실행계획에 SORT ORDER BY 연산이 나타난 이유는?

  SELECT *
  FROM (
  	SELECT TO_CHAR(A.주문번호, 'FM00000') AS 주문번호, A.업체번호, A.주문금액
      FROM 주문 A
      WHERE A.주문일자 = :dt
      AND A.주문번호 > NVL(:next_ord_no, 0)
      ORDER BY 주문번호
      )
  WHERE ROWNUM <= 30
  
  >>>> SORT ORDER BY STOPKEY
  

  • ORDER BY 절에 기술한 '주문번호'는 순수한 주문번호가 아니라 TO_CHAR 함수로 가공한 주문번호를 가리키기 때문!!
  • ORDER BY 절 주문번호에 A(주문 테이블 Alias)를 붙여주기만 하면 된다.
  • 추가로, 애초에 저자가 발견한 SQL의 ORDER BY 절에는 '주문번호'가 아니라 '1'이라고 적혀 있었다고 한다. '1'은 SELECT-LIST에 나열된 첫 번째 컬럼을 의미하니, 그것 역시 순수한 주문번호가 아닌 가공한 주문번호를 가리키고 있었을 것.
  • FM00000?
    • TO_CHAR 함수에 'FM00000' 옵션을 사용하면, 첫 번째 인자에 입력한 숫자 값을 '0'으로 시작하는 여섯자리 문자 값으로 변환해 준다. 예를 들어, 숫자 1234를 입력하면 , 문자 '001234'으로 변환해 준다.

 

 

SELECT-LIST 에서 컬럼 가공

MIN/MAX

• 인덱스를 [장비번호 + 변경일자 + 변경순번] 순으로 구성하면, 아래와 같이 변경순번 최소 값을 구할 때도 옵티마이저는 정렬 연산을 따로 수행하지 않는다.
• 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최소값이기 때문.

SELECT MIN(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'

• MAX(변경순번)도마 찬가지로 정렬 연산을 수행하지 않는다. 최소값을 찾아 수직적 탐색할 때 왼쪽으로 내려갔지만, 최대값을 찾을 때는 오른쪽으로 내려가는 점만 다르다.
• 인덱스를 이용해 이처럼 정렬 연산 없이 최소 또는 최대값을 빠르게 찾을 때 아래와 같은 실행계획이 나옴
• ROWS Row Source Operation ---- ---------------------------- 0 STATEMENT 1 SORT AGGRANGE 1 FIRST ROW 1 INDEX RANGE SCAN (MIN/MAX) 상태변경이력_PK
• 인덱스의 리프 블록의 왼쪽(MIN) 또는 오른쪽(MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다!

 

 

MIN/MAX 형변환

• 인덱스에는 문자열 기준으로 정렬돼 있는데, 이를 숫자값으로 바꾼 값 기준으로 최종 변경순번을 요구하면 정렬 연산을 생략할 수 없다.
• SQL을 아래와 같이 바꾸면 정렬 연산 없이 최종 변경순번을 쉽게 찾을 수 있음.
• 물론 이렇게 변환하려면 변경순번 값이 고정너비로 입력돼있어야 한다. ⇒ 문자열이니까
• 애초에 변경순번 데이터타입을 숫자형으로 설계했다면 튜닝할 일이 없음!!!

// 바꾸기 전
SELECT NVL(MAX(TO_NUMBER(변경순서)),0)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'

ROWS   Row Source Operation
------ --------------------0	 STATEMENT
1	 SORT AGGREGATE
131577 INDEX RANGE SCAN 상태변경이력_PK

// 바꾼 후
SELECT NVL(TO_NUMBER(MAX(변경순서)),0)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'

ROWS   Row Source Operation
------ --------------------0	   STATEMENT
1	   SORT AGGREGATE
1	   FIRST ROW
1	   INDEX RANGE (MIN/MAX) SCAN 상태변경이력_PK

• 하나의 예가 더 있으나 5장 3절 3항에 나오는 Top N 알고리즘을 이해해야하므로 스킵...

 

 

자동 형변환

• 타입체크를 엄격히 함으로써 컴파일 시점에 에러를 내는 DBMS도 있지만, 오라클은 자동 형변환 처리를 하는 DBMS이다.
• 생년월일이 선두 컬럼인 인덱스 + 조건절에 가공하지 않은 생년월일을 걸어도, 옵티마이저는 테이블 전체 스캔을 선택한다. 바로 인텍스 컬럼이 자동 형변환에 의해 가공되기 때문이다.

- 숫자형과 문자형이 만나면 숫자형이 이긴다.
    - 예외) LIKE는 자체가 문자열 비교 연산자 이므로 문자형 기준으로 숫자형 컬럼이 변환된다.
- 날짜형과 문자형이 만나면 날짜형이 이긴다.
    - 주의) WHERE 가입일자 = TO_DATE('01-JAN-2018','DD-MON-YYYY') 처럼 날짜 포맷을 정확히 지정해주는 코딩 습관 중요. 파라미터가 다르게 설정된 환경에서 수행하면 컴파일 오류가 나거나 결과집합이 틀려질 수 있기 때문!

예시

• 자동 형변환의 성능에 관련한 내용은 3장 3절에서 자세히 다루지만, 한가지 예시만 살짝 짚고 넘어감.
• 아래 쿼리는 사용자가 계좌 번호를 입력하지 않았을 때와 입력했을 때를 처리하기위한 쿼리이다. 사용자가 계좌번호를 입력하지 않으면 :acnt_no 변수에 NULL 값을 입력함으로써 모든 계좌번호가 조회되도록 하는 것이다.

SELECT * FROM 거래
WHERE 계좌번호 LIKE :acnt_no || '%'
AND   거래일자 between :trd_dt1 and :trd_dt2

• 하지만, 이 구조는 숫자형 컬럼을 LIKE 조건으로 검색하면 자동 형변환이 발생해 계좌번호가 아예 인덱스 액세스 조건으로 사용되지 못한다.

 

자동형변환 주의

1. 숫자형 컬럼과 문자형 컬럼을 비교하면 문자형 컬럼이 숫자형으로 변환되는데, 만약 변환할 수 없는 문자열이 입력되었다면 쿼리 수행 도중 에러가 발생한다.
2. 오라클이 decode 함수를 처리할 때 내부에서 사용하는 자동 형변환 규칙이 존재한다
   • 또한, 인자 값이 null이라면 varchar2로 취급된다.
     • decode(a, b, c, d) 를 처리할때 ‘a=b’이면 c를 반환하고, 아니면 d를 반환한다.
     • 즉, 반환 값의 데이터 타입은 세 번째 인자 c에 의해 결정되는 상황임.
     • 여기서 문제점 : 세 번째 반환값이 문자형이고 네 번재 반환값이 숫자형이면 네번째를 반환할 때 문자형 기준으로 반환하기 때문에 잘못된 결과값을 반환 할 수 있다.
     • decode(job, 'PRESIDENT', to_number(null), sal)로 3번째 값을 숫자형으로 변경 해주어야한다.

• SQL 튜닝은 TO_CHAR, TO_DATE, TO_NUMBER 같은 연산횟수를 줄인다고 성능이 높아지는게 아니라, 결국 블록 I/O를 줄이는 것이 관건이다. (형변환 함수를 생략한다고 해서 연산 횟수가 줄어드는 것도 아님. 어짜피 개발자가 형변환함수 생략해도 옵티마이저가 자동으로 생선함.)
• 결론은, 이 기능에 의존하지 말고, 정확히 형변환 해서 써라. + postgresql 에는 어짜피 없음 ㅠㅠ