[SQLP] 2-2. 인덱스 기본 사용법

인덱스 기본 사용법 : 인덱스를 Range Scan 하는 방법

인덱스 확장기능 사용법 : Index Range Scan 이외의 다양한 스캔 방식

 

---

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

수직적 탐색이 가능한 이유

: 색인이 가나다 순으로 정렬되어 찾고자 하는 단어들이 서로 모여있기 때문

 

시작하는 단어가 아닌 포함하는 단어 찾기

:  색인이 정렬되어 있음에도 불구하고, 시작점을 찾을 수 없고, 스캔하다가 중간에 멈출 수도 없다.

-> 찾고자 하는 단어들이 흩어져 있기 때문

-> 따라서,색인을 사용하지 못하는 것이 아니라, 시작점을 찾을 수 없고 멈출 수 없기 때문에 색인 전체를 스캔해야 한다.

 

인덱스의 사용

: 인덱스 칼럼 [선두 칼럼]을 가공하지 않아야 사용 가능

= 리프 블록에서 스캔 시작점을 찾아 스캔하다가 중간에 멈추는 것을 의미

즉, 리프 블록 일부만 스캔하는 Index Range Scan을 의미

 

인덱스를 가공할 경우 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾지 모하고, 멈출 수도 없어 리프 블록 전체를 스캔해야 한다. [일부가 아닌 전체를 스캔하는 Index Full Scan방식으로 동작]

 

---

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

 

[인덱스 칼럼을 가공하면 인덱스를 정상적으로 사용 불가]

-> 인덱스 스캔 시작점을 찾을 수 없기 때문

 

Index Range Scan은 인덱스에서 일정 범위를 스캔하기 때문에, '시작 지점'과 '끝 지점'이 필요하다.

-> 수직적 탐색을 통해 시작 지점을 찾아야 한다.

 

#SQL에서 인덱스 스캔 시작점을 찾기 위해

-> UNION ALL 방식으로 작성해, 각 브랜치 별로 인덱스 스캔 시작점을 찾아서, Range Scan을 한다.

[IN 조건절의 경우 SQL 옵티마이저가 IN-List Iterator 방식 사용, 즉 IN_List 개수만큼 Index Range Scan 반복]

 

일반적으로 함수 사용한 where 조건절에서는 인덱스를 정상적으로 사용할 수 없어 Index Range Scan이 불가능하지만,

-> OR 또는 IN 조건절은 옵티마이저 쿼리변환 기능을 통해 Index Range Scan으로 처리 가능하다.

 

---

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

: 인덱스의 정상적 사용을 위해 필요한 선행조건

 

[소속팀 + 사원명 + 연령]으로 구성한 인덱스에서

사원명이 '홍길동'인 조건절에 대해 인덱스를 정상적으로 Range Scan 가능 여부

-> 소속팀 순으로 정렬해서, 소속팀이 같으면 사원명 순으로 정렬, 사원명까지 같으면 연령 순으로 정렬

 

[이름이 같은 사원이라도, 소속팀이 다를 경우 멀리 떨어진다.]

 

-> 이 조건으로 검색시 인덱스 스캔 시작점을 찾을 수 없고, 끝 지점을 찾을 수 없어 인덱스 리프 블록을 처음부터 끝까지 모두 스캔해야 한다.

 

[즉, 인덱스를 Range Scan하기 위한 첫번째 선행조건은 인덱스 선두 칼럼이 가공하지 않은 상태로 조건절에 있어야 한다.]

 

-> 인덱스 선두 칼럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능하다.

 

인덱스를 잘 타는 경우

: 인덱스를 탄다 = 인덱스를 Range Scan 한다.

하지만, 성능에 문제없다고 단언할 수 없다.

Execution Plan
-----------------------------------------------------------
SELECT STATEMENT Optimizer = ALL_ROWS
 TABLE ACCESS (BY INDEX ROWID) OF '주문상품' (TABLE)
  INDEX(RANGE SCAN) OF '주문상품_N1' (INDEX)
-----------------------------------------------------------

 

[주문일자 + 상품번호]로 구성된 인덱스에서,

1. 인덱스 선두 칼럼인 주문일자가 조건절에 있고,

2. 가공하지 않은 상태이므로 인덱스를 Range Scan 하는 데 문제가 없다.

 

따라서 인덱스를 잘 탄다?

-> 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다.

 

 

스캔 범위를 줄이는 데 도움을 주지 못하는 경우

SELECT	*
FROM	주문상품
WHERE	주문일자 = :ord_dt
AND		상품 번호 LIKE '%PING%';

SELECT 	*
FROM 	주문상품
WHERE 	주문일자=:ord_dt
AND 	SUBSTR(상품번호, 1, 4) = 'PING';

상품번호를 이용해 스캔 범위를 줄일 수 없다.

1. 중간 값 검색이기 때문에

2. 칼럼을 가공했기 때문에

 

-> 위의 조건절을 처리할 때 인덱스에서 처리하는 데이터양은 주문일자 조건을 만족하는 경우인데, 조건절을 처리하는데도 불구하고 매우 많은 데이터양을 가지므로 효율성 측면에서 떨어진다.

 

---

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

: 인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬되어 있기 때문인데

찾고자 하는 데이터가 정렬된 상태로 모여있어서 전체가 아닌 일정 부분만 읽다가 멈출 수 있다.

 

인덱스 칼럼을 가공해도 인덱스를 사용할 수 있지만, 찾고자 하는 데이터가 전체구간에 흩어져있기 떄문에 Range Scan이 불가능하거나 비효율이 발생한다.

 

-> 인덱스는 정렬되어있어서 소트 연산 생략 효과를 얻는다.

 

[장비번호+변경일자+변경순번] 순으로 구성한 상태변경이력 테이블

PK 인덱스에서 장비번호, 변경일자가 같은 레코드는 변경순번 순으로 정렬

-> 장비번호와 변경일자를 모두 '='조건으로 검색시 PK 인덱스를 사용하면 결과집합은 변경순번 순으로 출력된다.

 

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

Execution Plan
----------------------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=85 Card=81 Bytes=5K)
 TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력' (TABLE) (Cost=85 ...)
   INDEX (RANGE SCAN) OF '상태변경이력_PK' (INDEX (UNIQUE)) (Cost=3 ...)

-> 옵티마이저는 이런 속성에 따라 ORDER BY가 있어도 정렬연산을 수행하지 않는다.

[PK 인덱스를 스캔하면서 출력한 결과집합은 변경순번 순으로 정렬되기 때문에]

 

인덱스가 정렬연산 생략할 수 있도록 정렬되어 있지 않은 경우

Execution Plan
----------------------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=85 Card=81 Bytes=5K)
 SORT (ORDER BY) (Cost=86 Card=81 Bytes=5K)
  TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력' (TABLE) (Cost=85 ...)
   INDEX (RANGE SCAN) OF '상태변경이력_PK' (INDEX (UNIQUE)) (Cost=3 ...)

 

또한 내림차순 정렬에서도 인덱스 활용 가능 [인덱스 리프 블록이 양방향 연결 리스트 구조이기 때문]

오름차순 정렬인 경우	조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색 후 수평적 탐색한다.
내림차순 정렬인 경우	조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색 후 수평적 탐색한다.

 

ORDER BY에서 내림차순 정렬시

 

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

Execution Plan
----------------------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=85 Card=81 Bytes=5K)
 TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력' (TABLE) (Cost=85 ...)
   INDEX (RANGE SCAN DESCENDING) OF '상태변경이력_PK' (INDEX (UNIQUE))

-> SORT ORDER BY 연산이 없다.

-> INDEX RANGE SCAN 단계에 DESCENDING [ASCENDING이 기본값이므로 DESCENDING인 경우에만 표시]

---

5. ORDER BY 절에서 칼럼 가공

[인덱스 칼럼을 가공하면 인덱스를 정상적으로 사용할 수 없다.]

-> 일반적으로 조건절에서 사용한 칼럼을 가리키는데, 조건절이 아닌 ORDER BY나 SELECT-LIST에서 칼럼 가공해서 정상적으로 사용할 수 없는 경우도 존재

 

상태변경이력 PK 인덱스를 [장비번호 + 변경일자 + 변경순번]순으로 구성했다면, 정렬 연산 생략 가능

[수직적 탐색을 통해 장비번호가 ' C'인 첫 번째 레코드를 찾아 인덱스 리프 블록 스캔시,

-> 자동으로 [변경일자 + 변경순번]순으로 정렬]

 

 

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

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

-> 정렬연산 생략 불가 [인덱스에는 가공되지 않은 상태로 저장했지만, 가공한 값 기준으로 정렬해 달라고 요청하므로]

 

[주문일자+주문번호] 순으로 구성된 주문_PK 인덱스

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

-> 주문_PK 인덱스를 사용하면, 선두 칼럼인 주문일자가 '='조건이므로 데이터가 주문번호 순으로 출력된다.

-> ORDER BY 절이 있어도 정렬 연산을 생략할 수 있지만, 실행계획에 SORT ORDER BY 연산이 나타남

 

ORDER BY절에 기술한 '주문번호'가 TO_CHAR 함수로 가공한 주문번호이기 때문

['FX000000'옵션을 이용해 첫 번쨰 인자에 입력한 숫자 값을 '0'으로 시작하는 여섯 자리 문자 값으로 변환]

 

->1이라고 적혀있는 것을 [SELECT-LIST에 나열된 첫 번째 칼럼] ORDER BY절 주문번호에 A를 붙여서 해결

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

---

6. SELECT-LIST에서 칼럼 가공

 

 

변경순번 최솟값을 구할 때

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

인덱스를 [장비번호+변경일자+변경순번]순으로 구성하는 경우, 변경순번 최솟값을 구할 때도 정렬연산 생략

[수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드가 최솟값이기 때문]

 

 

변경순번 최댓값을 구할 때

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

인덱스를 [장비번호+변경일자+변경순번]순으로 구성하는 경우, 변경순번 최댓값을 구할 때도 정렬연산 생략

[수직적 탐색을 통해 조건을 만족하는 가장 오른쪽 지점으로 내려가서 첫 번째 읽는 레코드가 최댓값이기 때문]

 

인덱스를 이용해 정렬 연산없이 최솟값 또는 최댓값을 찾을때 실행계획

STATEMENT
 SORT AGGREGATE (cr=6 pr=0 pw=0 time=81 us)
  FIRST ROW (cr=6 pr=0 pw=0 time=59 us)
   INDEX RAGNE SCAN (MIN/MAX) 상태변경이력_PK (cr=6 pr=0 pw=0 ...)

-> 인덱스 리프 블록의 왼쪽 또는 오른쪽에서 레코드 하나만 읽고 멈춤

 

 

 

정렬 연산을 생략할 수 없는 경우

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

STATEMENT
 SORT AGGREGATE (cr=1670 pr=0 pw=0 time=101326 us)
  INDEX RAGNE SCAN 상태변경이력_PK (cr=1670 pr=0 pw=0 ...)

-> 인덱스에 문자열 기준으로 정렬되어있는데 이를 숫자값으로 바꾼 값 기준으로 최종 변경순번을 요구했기 때문에

 

 

정렬 연산 생략 가능하게 바꾸기

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

 

STATEMENT
 SORT AGGREGATE (cr=4 pr=0 pw=0 time=59 us)
 FIRST ROW (cr=4 pr=0 pw=0 time=59 us)
  INDEX RAGNE SCAN (MIN/MAX) 상태변경이력_PK (cr=4 pr=0 pw=0 time=59 us)

-> 변경 순번 값이 고정너비로 입력되어 있을 때 MAX와 TO_NUMBER의 순서를 바꾸면 정렬 생략 가능하다.

[처음에 변경순번 데이터타입이 숫자형이라면 튜닝하지 않아도 된다.]

 

 

최종 변경일자를 스칼라 서브쿼리를 이용해 상태변경이력 테이블에서 조회하는데

정렬 연산 없이 MIN/MAX, FIRST ROW 방식으로 실행

SELECT 장비번호, 장비명, 상태코드
	,(SELECT MAX(변경일자)
    FROM 상태변경이력
    WHERE 장비번호=P.장비번호) 최종변경일자
FROM 장비 P
WHERE 장비구분코드 ='A001'

SORT AGGREGATE (cr=4 pr=0 pw=0 time=59 us)
 FIRST ROW (cr=4 pr=0 pw=0 time=59 us)
  INDEX RAGNE SCAN (MIN/MAX) 상태변경이력_PK (cr=4 pr=0 pw=0 time=59 us)
 TABLE ACCESS BY INDEX ROWID 장비
  INDEX RANGE SCAN 장비_N1

 

 

최종 변경일자에 더해 최종 변경순번까지 출력

SELECT 장비번호, 장비명, 상태코드
	,(SELECT MAX(변경일자)
    FROM 상태변경이력
    WHERE 장비번호=P.장비번호) 최종변경일자
    ,(SELECT MAX(변경순번)
    FROM 상태변경이력
    WHERE 장비번호=P.장비번호
    AND 변경일자 = (SELECT MAX(변경일자)
    				FROM 상태변경이력
    				WHERE 장비번호=P.장비번호))최종변경순번
FROM 장비 P
WHERE 장비구분코드 ='A001'

-> 상태변경이력 테이블을 여러번 읽기 때문에 비효율적

-> PK 칼럼이 많아지면 SQL문이 복잡해지므로 성능이 나빠짐

 

SELECT 장비번호, 장비명, 상태코드
	, SUBSTR(최종이력, 1, 8) 최종변경일자
    , SUBSTR(최종이력, 9)	 최종변경순번
FROM(
	SELECT 장비번호, 장비명, 상태코드
    ,(SELECT MAX( 변경일자 || 변경순번)
      FROM 상태변경이력
      WHERE 장비번호=P.장비번호) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)

SORT AGGREGATE (cr=6380 pr=0 pr=6488 time=0 us)
  INDEX RAGNE SCAN 상태변경이력_PK (cr=6300 pr=6488 pw=0 ...)
 TABLE ACCESS BY INDEX ROWID 장비 (cr=4 pr=16 pw=0 time=0 us)
  INDEX RANGE SCAN 장비_N1 (cr=2 pr=8 pw=0 time=90 us)

-> 각 장비당 이력이 많다면 성능에 문제 발생 가능 [인덱스 칼럼을 가공했기 때문에]

-> 각 장비에 속한 과거 이력 데이터를 모두 읽어야 하므로 장비당 이력 레코드가 많다면 성능이 더 안좋을 수 있다.

[메인쿼리 장비 테이블에서 읽은 데이터가 매우 소량이어도, 상태변경이력_PK 인덱스에서 대용량 레코드를 읽는다.]

 

-> Top N 알고리즘을 이용한 해결

 

---

7. 자동 형변환

 

고객 테이블에 생년월일이 선두 칼럼인 인덱스

SELECT 	*
FROM 	고객
WHERE	생년월일=19821225

Execution Plan
---------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=38)
 TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=3 Card=1 Bytes=38)

 

Predicate Information (identified by operation Id):
-------------------------------------------------------------------
-filter(TO_NUMBER("생년월일")=19821225)

생년월일 칼럼을 조건절에서 가공하지 않았는데도 옵티마이저는 테이블 전체 스캔 선택

-> 옵티마이저가 SQL을 변환을 통해 결과적으로 인덱스 칼럼이 가공됐기 때문에 인덱스를 Range Scan 불가

 

SELECT * FROM 고객
WHERE TO_NUMBER(생년월일)=1981225

-> 고객 테이블 생년월일 칼럼이 문자형인데 조건절 비교값을 숫자형으로 표현했기 때문에 발생

[각 조건절에서 양쪽 값의 데이터 타입이 서로 다르면 값을 비교할 수 없다. -> 자동으로 형변환]

 

오라클에서 숫자형과 문자형이 만날경우 숫자형 칼럼 기준으로 문자형 칼럼을 변환

 

 

날짜형과 문자형이 만날 경우

SELECT *FROM 고객
WHERE 가입일자 ='01-JAN-2018';

-> NLS_DATE_FORMAT 파라미터가 다르게 설정된 환경이라면 컴파일 오류가 나거나 결과집합이 잘못될수있다.

-> 날짜 포맷을 정확히 지정 필요

 

 

SELECT *FROM 고객
WHERE 가입일자 =TO_DATE('01-JAN-2018', 'DD-MON-YYYY');

-> LIKE 연산자일 경우 숫자형과 문자형이 만나면 문자형 기준으로 숫자형 칼럼이 변환 [LIKE가 문자열 비교 연산자이므로]

 

 

Execution Plan
---------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=38)
 TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=3 Card=1 Bytes=38)
 
 Predicate Information (identified by operation Id):
-------------------------------------------------------------------
-filter(TO_CHAR("고객번호") LIKE '9410%')

 

 

LIKE 조건을 옵션 조건 처리 목적으로 사용하는 경우

[거래 데이터 조회시 계좌번호는 사용자가 입력할 수도 있고 안 할 수도 있는 옵션 조건]

--SQL1 : 사용자가 계좌번호를 입력할 경우
SELECT * FROM 거래
WHERE 	계좌번호 = :acnt_no
AND		거래일자 between :trd_dt1 and :trd_dt2

--SQL2 : 사용자가 계좌번호를 입력하지 않을 경우
SELECT * FROM 거래
WHERE 	거래일자 between :trd_dt1 and :trd_dt2

 

하나의 sql로 처리할 경우 -> LIKE 조건 사용

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

-> LIKE, BETWEEN 조건을 같이 사용했으므로 인덱스 스캔 효율이 안좋아진다

 

계좌번호 칼럼이 숫자형인 경우 주의 필요

-> 숫자형 칼럼을 LIKE 조건으로 검색하면 자동 형변환 발생해 계좌번호가 인덱스 액세스 조건으로 사용 불가

 

계좌번호가 형변환되는 경우

-> [거래일자 + 계좌번호]순으로 구성된 인덱스는 Range Scan할 수 있지만, 인덱스 스캔 효율이 안좋아진다

[거래일자 조회 범위에 속한 거래 데이터를 모두 읽으면서 계좌번호를 필터링하기 때문]

 

-> [계좌번호 + 거래일자]순으로 구성된 인덱스는 Range Scan할 수 없다.

 

자동 형변환 주의

성능과 애플리케이션 품질에 종종 문제가 생긴다.

 

숫자형 칼럼 (n_col) 문자형 칼럼(v_col) 비교시 문자형 칼럼이 숫자형으로 변환되는데

WHERE n_col=v_col

-> 문자형 칼럼에 숫자로 변환할 수 없는 문자열이 입력되면 쿼리 수행 도중 에러 발생

[실행 오류]

 

EMP 테이블에서 직원들 급여 수준 조회

SELECT round(avg(sal)) avg_sal
		,min(sal) min_sal
        ,max(sal) max_sal
        ,max(decode(job, 'PRESIDENT', NULL, sal)) max_sal2
from emp;

max       : PRESIDENT

max_sal2 : 950

[결과 오류]

 

-> decode 함수 처리시 내부에서 사용하는 자동 형변환 규칙 때문

 

자동 형변환 규칙

: decode(a, b, c, d)를 처리할 때 'a=b'이면 c를 반환하고 아니면 d를 반환

:이때 반환값의 데이터 타입은 세 번째 인자 c에 의해 결정된다.

[c가 문자형이고 d가 숫자형이면, d는 문자형으로 반환된다]

 

또한, 세 번째 인자 (c)가 null값이면 varchar2로 취급

-> 세 번째 인자가 null 값이므로 네 번째 인자 sal을 문자열로 반환하고, 문자열 기준으로 가장 큰 값(950)을 출력

 

해결 : 데이터 타입 명시적으로 일치시키기

SELECT ROUND(AVG(sal)) avg_sal
		,min(sal) min_sal
        ,max(sal) max_sal
        ,max(decode(job, 'PRESIDENT', to_number(NULL), sal)) max_sal2
from emp;

-> 자동 형변환의 기능에 상관없이 인덱스 칼럼 기준으로 반대편 칼럼 또는 값응ㄹ 정확히 형변환 필요

 

형변환 함수를 생략한다고 연산횟수가 주는것도 아닐 뿐더러, 블록 I/O를 줄이는 것이 SQL 성능의 관건

-> 형변환 함수를 생략해도 옵티마이저가 자동으로 생성하기 때문