[ORACLE 튜닝] 2장. 인덱스 기본 (2)

개발/Database

[ORACLE 튜닝] 2장. 인덱스 기본 (2)

hongtaekki 2022. 2. 6. 21:15

2.2. 인덱스 기본 사용법

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

인덱스 컬럼(정확히는 선두컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.
'인덱스를 정상적으로 사용할 수 있다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다. 즉 리프블록 일부만 스캔하는 Index Range Scan을 의미
인덱스를 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수 없어 리프 블록 전체를 스캔하는 Index Full Scan을 해야한다.

2. 인덱스를 Range Scan 할 수 없는 경우

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다. 일정 범위를 스캔(Range Scan)하려면 '시작지점'과 '끝지점'이 있어야한다.
아래의 조건절을 고민해보자
-- 2007년 1월1일에 태어난 학생을 찾고(시작점) 2007년 2월1일에 태어난 학생을 찾으면(끝지점) 스캔을 멈춘다 where 생년월일 between '20070101' and '20070131' -- 스캔 시작지점과 종료지점을 알 수 없다. where substr(생년월일, 5, 2) = '05' -- '대한'으로 시작하는 값은 Range Scan이 가능하지만 '대한'을 포함한 값은 Full Scan 해야한다. where 업체명 like '%대한%'

OR Expansion 방식

다음 OR 조건문은 수직적 탐색을 통해 시작지점을 바로 찾을 수 없어 Range Scan이 불가능하다.

-- 다음 OR 조건문은 수직적 탐색을 통해 시작지점을 바로 찾을 수 없어 Range Scan이 불가능하다.
WHERE (전화번호 =:tel_no OR 고객명 = :cust_nm)

아래와 같이 쿼리하면 고객명, 전화번호 인덱스 각각에 대해 Index Range Scan이 가능하다.

select *
  from 고객
where 고객명 = :cust_nm  /* 고객명이 선두 컬럼인 인덱스 Range Scan */

union all

select *
  from 고객
where 전화번호 = :tel_no   /* 전화번호가 선두 컬럼인 인덱스 Range Scan */
   and (고객명 <> :cust_nm or 고객명 is null)
;

OR 조건식을 SQL 옵티마이저가 위와 같은 형태로 변환할 수 있는데, 이를 'OR Expansion' 이라고 한다.

아래는 use_concat 힌트를 이용해 OR Expansion을 유도했을 때의 실행계획이다.

select /*+ use_concat */ * from 고객
where (전화번호 = :tel_no OR 고객명 = :cust_nm)


Execution Plan
-------------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78) 
1   0  CONCATENATION 
2   1    TABLE ACCESS (BY INDEX ROWID) OF '22' (TABLE) (Cost=2 Card=1 ... )
3   2       INDEX (RANGE SCAN) OF '22_22H0 IDX' (INDEX) (Cost=1 Card=1)
4   3    TABLE ACCESS (BY INDEX ROWID) OF '22' (TABLE) (Cost=2 Card=1 ... )
5   4       INDEX (RANGE SCAN) OF '224_210 IDX' (INDEX) (Cost=1 Card=1)

In-List Iterator 방식

where 전화번호 IN ( :tel_no1, :tel_no2

IN 조건절은 UNION ALL 방식으로 작성하면, 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있다
(Range Scan이 가능)
```
select *
from 고객
where 전화번호 = :tel_no1

union all

select *
from 고객
where 전화번호 = :tel_no2
```
IN 조건절에 대해서 SQL 옵티마이저가 IN-List Iterator 방식을 사용해 IN-List 개수만큼 Index Range Scan을 반복한다.

아래는 IN-List Iterator을 유도했을 때의 실행계획이다.

Execution Plan
-------------------------------------------------------------
0 		SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=39)
1    0     INLIST ITERATOR
2    1        TABLE ACCESS (BY INDEX ROWID) OF '17 (TABLE) (Cost=2 Card=1 ... )
3    2             INDEX (RANGE SCAN) OF '224_217 _IDX' (INDEX) (Cost=1 Card=1)
-------------------------------------------------------------

Predicate information (identified by operation id):
-------------------------------------------------------------
 3 - access("전화번호" =:TEL_NO1 OR "전화번호" =:TEL_NO2)

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

예시1) EMP 테이블의 인덱스를 [소속팀 + 사원명 + 연령] 순으로 구성했다고 가정하자.
- 아래의 조건절애 대해 인덱스를 정상적으로 Range Scan할 수 있을까?
```
select 사원번호, 소속팀, 연령, 입사일자, 전화번호
  from 사원
where 사원명 = '홍길동'
;
```
- 사원명 = '홍길동' 조건을 만족하는 데이터는 리프 블록 전 구간에 흩어지기 때문에 Range Scan이 불가능하다.
- 인덱스 Range Scan을 하기 위한 간장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 것이다.
예시2) 아래 SQL은 인덱스 컬럼을 가공했는데, 어떻게 인덱스를 Range Scan할 수 있는지 생각해보자.
```
TXA1234_IX02 인덱스 : 기준연도 + 과세구분코드 + 보고회차 + 실명확인번호

select * from TXA1234 
where 2295 = :stdr_year 
  and substr(과세구분코드, 1, 4) = :txtn_dcd 
  and 보고회차 = :rpt_tmrd 
  and 실명확인번호 = :rnm_cnfm_no


Execution Plan
-----------------------------------------------------------
0     SELECT STATEMENT Optimizer=ALL_ROWS
1   0   TABLE ACCESS (BY INDEX ROWID) OF 'TXA1234' (TABLE) 
2   1     INDEX (RANGE SCAN) OF ‘TXA1234_IXO2' (INDEX)
```
- 앞서 인덱스를 Range Scan 하려면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 한다. 반대로 말해, 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능하다.
- 위의 쿼리문은 선두 컬럼인 '기준연도'를 조건절에서 가공하지 않았으므로 인덱스 Range Scan이 가능하다.
- 중요한 것은 인덱스를 탄다(Index Range Scan)고 무조건 성능에 문제가 없다고 생각하면 안된다. 인덱스를 잘 타는지 여부는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다.

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

예시1) 그림처럼 [장비번호 + 변경일자 + 변경순번] 순으로 구성한 상태변경이력 테이블이 있다고 가정하자.
- 그림처럼 PK 인덱스는 장비번호, 변경일자가 같은 레코드는 변경순번 순으로 정렬돼있다.
- 아래와 같이 장비번호와 변경일자를 모두 '=' 조건으로 검색할 때 PK 인덱스를 사용하면 결과집합은 변경순번 순으로 출력된다.
```
SELECT * 
  FROM 상태변경이력 
 WHERE 장비번호 = 'C' 
   AND 변경일자 = '2180316' 

Execution Plan
------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost = 85 Card=81 Bytes=5K) 
1   0    TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력' (TABLE) (Cost=85 ...) 
2   1       INDEX (RANGE SCAN) OF '상태변경이력-PK' (INDEX (UNIQUE)) (Cost=3 ...)
```
- 옵티마이저는 이런 속성을 활용해 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않는다. PK 인덱스를 스캔하면서 출력한 결과집합은 어차피 변경순번 순으로 정렬되기 때문이다.
- 인덱스 리프 블록은 양방향 연결 리스트 구조이기 때문에 내림차순 정렬인 경우에도 인덱스를 활용할 수 있다.
  - 오름차순 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색을 한다.
  - 내림차순 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색을 한다.

아래 SQL에서 SORT ORDER BY 연산이 없다는 것과 INDEX RANGE SCAN 단계에서 DESCENDING이라고 표시된 부분을 확인하자.

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

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

5. ORDER BY 절에서 컬럼 가공

조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 존재한다.
예시1) 그림의 상태변경이력 PK 인덱스를 [장비번호 + 변경일자 + 변경순번] 순으로 구성했다면, 아래 SQL도 정렬 연산 생략이 가능할까?
- 인덱스에는 가공하지 않은 상태로 값을 저장했는데, 가공한 값 기준으로 정렬해 달라고 요청했기 때문에 정렬 연산을 생략할 수 없다.
```
SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
 ORDER BY 변경일자 || 변경순번
```

6. SELECT-LIST에서 컬럼 가공

예시1) 아래와 같이 최소값, 최대값을 구하는 쿼리는 정렬 연산을 따로 수행하는가?
```
SELECT MIN(변경순번)
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'
;

SELECT MAX(변경순번)
  FROM 상태변경이력
 WHERE 장비번호 = 'C'
   AND 변경일자 = '20180316'
;
```
- 최소값을 구할 경우 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최소값이기 때문에 정렬 연산 수행하지 않는다.
- 최대값 또한 수직적 탐색을 통해 오른쪽 지점으로 내려가서 첫 번재 읽는 레코드가 바로 최대값이다.

7. 자동 형변환

조건절에서 양쪽 값의 데이터 타입이 서로 다르면 값을 비교할 수 없다. 오라클은 자동으로 형변환 처리 후 비교를 진행한다.

예시1) 고객 테이블에 생년월일이 선두 컬럼인 인덱스가 있다고 가정하자. 아래 SQL은 생년월일을 조건절에서 가공하지 않았는데도 옵티마이저는 테이블 전체 스캔을 선택했다.

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

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

Predicate information (identified by operation id):
---------------------------------------------------------
1 - filter(TO_NUMBER("생년월일 ") = 19821225)

예시2) 연산자가 LIKE인 경우 문자형 기준으로 숫자형 컬럼이 변환된다.

SELECT * 
  FROM 고객
 WHERE 고객번호 LIKE '9410%'

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

Predicate information (identified by operation id):
--------------------------------------------------------
1 - filter(TO_CHAR("고객번호") LIKE '9410%')

예시3) <옵션 조건 처리 목적으로 사용하는 LIKE 조건> 거래 데이터 조회 시 계좌번호는 사용자가 입력할 수도 있고 안 할 수도 있는 옵션 조건이 있다고 하자.
- 아래처럼 LIKE, BETWEEN 조건을 같이 사용했으므로 인덱스 스캔 효율이 안좋아지고 숫자형 컬럼을 LIKE 조건으로 검색하면 자동 형변환이 발생해 계좌번호가 아예 인덱스 액세스 조건으로 사용되지 못한다.
```
SELECT * 
  FROM 거래
 WHERE 계좌번호 LIKE :acnt_no || '%'
   AND 거래일자 BETWEEN :trd_dt1 and :trd_dt2
```
자동 형변환 주의
- 예시1) 아래의 EMP 테이블에서 가장 적게 받는 직원 급여가 800이고, 가장 많이 받는 직원 급여는 5,000이다. 가장 많이 받는 직원 'PRESIDENT'를 제외하고 가장 많이 받는 직원의 급여(max_sal2)를 조회했다.
```
select round(avg(sal)) avg_sal
     , min(sal) min_sal 
     , max(sal) max_sal
 	 , max(decode (job, 'PRESIDENT', NULL, sal)) max_sal2 
  from emp ;


AVG_SAL	  MIN_SAL	MAX_SAL		MAX_SAL2
-------	  -------	-------		--------
   2073      800       5000		950
```
  - 이 경우, 3,000의 급여를 받는 ANALYST가 아닌 950이라는 잘못된 결과가 나왔다.
  - 잘못된 결과가 나온 이유는 오라클이 decode 함수를 처리할 때 내부에서 사용하는 자동 형변환 규칙 때문이다.
  - decode(a, b, c, d)를 처리할 때 'a = b'이면 c를 반환하고, 아니면 d를 반환한다. 이때 반환 값의 데이터 타입은 세 번째 인자 c에 의해 결정된다. 또한 세 번째 인자(c)가 null인 경우 varchar2로 취급된다.
  - 위의 쿼리문에서 세번째 인자가 null이므로 네번째 인자 sal이 문자열로 변환되고, 문자열 기준으로 가장 큰 값(950)을 출력한 것이다.
  - 오류를 피하기 위해서는 아래처럼 to_number(NULL)로 데이터 타입을 명시적으로 일치시켜 주거나 0을 사용해도 된다.
```
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 ;


AVG_SAL	  MIN_SAL	MAX_SAL		MAX_SAL2
-------	  -------	-------		--------
   2073      800       5000		3000
```

저작자표시 비영리 변경금지 (새창열림)