3.3 인덱스 스캔 효율화

액세스 조건 & 필터 조건

액세스 조건

• 인덱스 스캔 범위를 결정하는 조건절
• 스캔 시작점과 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는데 영향을 미침

필터조건

• 테이블로 엑세스할지를 결정

옵티마이저 비용 계산 원리

= 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용
= 인덱스 루트와 브랜치 레벨에서 읽는 블록 수 +
인덱스 리프 블록을 스캔하는 과정에서 읽는 블록 수 +
테이블 액세스 과정에서 읽는 블록 수

비교 연산자 종류와 컬럼 순서에 따른 군집성

• 첫번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.

인덱스 선행 컬럼이 등치( = ) 조건이 아닐 때 생기는 비효율

• 인덱스 선행 컬럼이 없거나 부등호, BETWEEN, LIKE같은 범위검색 조건이면, 인덱스를 스캔하는단계에서 비효율이 발생한다.

BETWEEN을 IN-LIST로 변환

• 주의 점
  • IN-LIST 개수가 많지 않아야 함
  • IN-LIST 개수가 많으면 수직적 탐색이 많이 발생한다.
  • BETWEEN 조건 때문에 리프 블록을 많이 스캔하는 비효율 보다, IN-LIST 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다.

num_index_keys 힌트

• num_index_keys ( table index num ) → 인덱스의 num번째 컬럼만 엑세스 조건으로 사용하라는 의미

BETWEEN 과 LIKE

• 둘 다 범위 조건으로서, 범위검색 조건을 사용할 때의 비효율 원리가 똑같이 적용되지만, LIKE 보다 BETWEEN을 사용하는 것이 낫다.

---

옵션 조건 처리 방식의 장단점 비교

OR 조건 활용

• 옵티마이저에 의한 OR-Expansion 쿼리 변환이 기본적으로 작동하지 않으므로, 인덱스 선두 컬럼에 대한 옵션 조건에 OR 조건을 사용해선 안된다.
• 인덱스에 포함되지 않는 컬럼에 대한 옵션 조건은 어차피 테이블에서 필터링할 수 밖에 없으므로 OR 조건을 사용해도 무방하다.
• 유일한 장점 : 옵션 조건 컬럼이 null 허용 컬럼이더라도 결과 집합을 보장한다.

⭐ OR-Expansion ( 출처 : 그루비 )

• 사용자가 직접 쿼리를 바꿔주지 않아도 옵티마이저가 작업을 대신해 주는 경우
• 힌트 : USE_CONCAT(OR-Expansion 유도), NO_EXPAND(OR-Expansion 방지)

select * from emp
where job='CLERK' or deptno = 20

↓ ( job과 deptno 에 각각 생성된 인덱스를 사용하려면 )

select * from emp
where job='CLERK'
union all
select * from emp
where deptno = 20
and LNNVL(job='CLERK')

• 중복 액세스 되는 영역(deptno=20 이면서 job='CLERK')의 데이터 비중이 작을수록 효과적, 그 반대라면 오히려 비용 증가
• 테이블에 중복 액세스되더라도 결과집합에는 중복이 없게 하기 위해 오라클 내부적으로 LNNVL 함수 사용
  ( LNNVL → lnnvl 조건식 안의 조건이 true면 false 리턴 false면 true 리턴 )

LIKE / BETWEEN 조건 활용

1. 인덱스 선듀 컬럼에 대한 옵션 조건을 LIKE / BETWEEN 연산자로 처리하는 것은 금물
2. NULL허용 컬럼에 대한 옵션 조건을 LIKE / BETWEEN 연산자로 처리하는 것은 금물
   → NULL이 입력되어있는 데이터는 결과 집합에서 누락되기 때문
3. 숫자형이면서 인덱스 액세스 조건으로도 사용 가능한 컬럼에 대한 옵션 처리는 LIKE 방식을 사용해선 안 된다.
4. LIKE를 옵션 조건에 사용할 때는 컬럼 값 길이가 고정적이어야 한다.

UNION ALL 활용

< 원본 >
select * from 거래
where **(:cust_id is null or 고객ID = :cust_id )**
and   거래일자 between :dt1 and :dt2

↓ ( union all 활용 )

select * from 거래
where **:cust_id is null**
and   거래일자 between :dt1 and :dt2
union all
select * from 거래
where **:cust_id is not null**
and   **고객ID = :cust_id** 
and   거래일자 between :dt1 and :dt2

NVL / DECODE 함수 활용

select * from 거래
where **고객ID = nvl(:cust_id, 고객ID)**
and   거래일자 between :dt1 and :dt2

or 

select * from 거래
where **고객ID = decode(:cust_id, null, 고객ID, :cust_id)**
and   거래일자 between :dt1 and :dt2

---

함수호출부하 해소를 위한 인덱스 구성

PL / SQL 성능적 특성

• 일반적으로 생각하는것 보다 매우 느림
• 느린 이유
  1. VM 상에서 실행되는 인터프리터 언어 ( → 소스를 기계어로 변환하는 과정없이 바로 실행하는 언어 )
  2. 호출 시마다 컨텍스트 스위칭 발생
  3. 내장 SQL에 대한 Recursive Call 발생

효과적인 인덱스 구성을 통한 함소호출 최소화

Full Scan 방식으로 읽으면서 encryption 함수가 테이블 건수만큼 수행

select /*+ full(a) */ 회원번호, 회원명, 생년, 생월일, 등록일자
from   회원 a
where 암호화된_전화번호 = encryption(:phone_no)

다른 조건절이 있는 경우 해당 조건절을 만족하는 건수만큼 encryption 함수 수행

select /*+ full(a) */ 회원번호, 회원명, 생년, 생월일, 등록일자
from   회원 a
where  생년 ='1987'
and    암호화된_전화번호 = encryption(:phone_no)

인덱스 3개 생성

create index 회원_x01 on 회원(생년);
create index 회원_x02 on 회원(생년, 생월일, 암호화된_전화번호);
create index 회원_x03 on 회원(생년, 암호화된_전화번호);

회원_x01 사용한 경우

→ 암호화된_전화번호 조건절을 액세스 단계에서 필터링 함

 encryption 함수는 테이블 액세스 횟수( 생년 ='1987' 조건을 만족하는 건수 )만큼 수행한다.

회원_x02 사용한 경우

→ 암호화된_전화번호는 선행 컬럼인 생월일에 대한 '='조건이 없으므로 인덱스 필터 조건이다.

 encryption 함수는 테이블 액세스 횟수( 생년 ='1987' 조건을 만족하는 건수 )만큼 수행한다.

회원_x03 사용한 경우

→ 암호화된_전화번호도 생년과 함께 인덱스 엑세스 조건으로 사용됨

encryption 함수는 단 한 번 수행된다.