인덱스 스캔 효율화 2

1️⃣ BETWEEN과 LIKE 스캔 범위 비교

BETWEEN이 LIKE보다 인덱스 효율이 좋다!

• LIKE '2019%' → 편하지만 스캔 범위가 넓어 불필요한 데이터 읽음
• BETWEEN '201901' AND '201912' → 정확한 범위 지정으로 인덱스 효율 높음
• 개발자들이 LIKE를 선호하는 이유는 “코딩이 간단하기 때문”

📌 예시

where 판매월 like '2019%';
-- vs
where 판매월 between '201901' and '201912';

• BETWEEN이 더 효율적
• LIKE는 인덱스 범위 확장으로 성능 손실 가능

2️⃣ 범위검색 조건을 남용할 때 생기는 비효율

인덱스 중간 컬럼 조건이 빠지면 스캔 범위가 폭증한다!

• 인덱스가 (회사코드 + 지역코드 + 상품명)일 때
  → 지역코드 조건이 빠지면, 상품명까지 Full Scan 발생
• 사용자 입력값이 유동적이라면 조건 절 설계를 신중히 해야 함

📌 예시

WHERE 회사코드 = :com
  AND 지역코드 LIKE :reg || '%'
  AND 상품명 LIKE :prod || '%'

• 결론: 중간 컬럼 누락 시 인덱스 스캔 범위 급확대
• 세 컬럼 다 입력 (회사+지역+상품명) → 인덱스 범위가 좁고 빠름
• 지역코드 미입력 → 인덱스 범위 확장 & 느려짐

3️⃣ 다양한 옵션 조건 처리 방식 비교

옵션 조건은 OR Expansion, NVL/DECODE, UNION ALL 등으로 해결 가능

① OR 조건 활용

• OR 조건이 포함되면 옵티마이저가 내부적으로 UNION ALL로 변환
• 각 조건별로 따로 인덱스를 RANGE SCAN 후 합침
• 단, NULL 컬럼 포함 시 인덱스 사용 불가 (18c 이후 일부 허용)

📌 예시

where (cust_id is null or cust_id = :cust_id)
and 거래일자 between :dt1 and :dt2

② LIKE/BETWEEN 옵션 조합

• 필수 조건 + LIKE 패턴 조합이 이상적
• 필수조건은 인덱스 선두, LIKE는 후순위
• 예: 등록일 + 상품분류코드 조합

📌 예시

where 등록일시 >= trunc(sysdate)
and 상품분류코드 like :prd_cls_cd || '%'

‼️ LIKE/BETWEEN 사용 시 반드시 확인

1. 인덱스 선두 컬럼 여부
2. NULL 허용 여부
3. 숫자형 컬럼인지
4. 가변 길이 컬럼 여부

③ NULL 허용 컬럼 주의

• NULL 값은 LIKE/BETWEEN 검색 결과에서 누락됨
• BETWEEN 사용 시도 마찬가지로 NULL 데이터는 제외됨
• LIKE는 NULL이 포함된 컬럼에 적합하지 않음

📌 예시

select * from dual where null like :var || '%';  -- 결과 없음

④ 숫자형 컬럼 변환 시

• 자동 형변환으로 인덱스 무력화 방지
• 숫자형 고객ID를 문자열 비교 시 자동 형변환으로 필터 조건 처리됨
• 인덱스 선두 컬럼으로 사용 불가

📌 예시

select * from 거래
where 거래일자 = :trd_dt
and 고객ID like :cust_id || '%';

⑤ 문자열 길이 문제

• LIKE 사용 시 변수 길이가 달라지면 의도치 않은 데이터 포함

📌 예시

where 고객명 like :cust_nm || '%'
-- ex) '김훈' 입력 시 '김훈님'도 검색됨

✅ 해결방법

where 고객명 like :cust_nm || '%'
and length(고객명) = length(nvl(:cust_nm, 고객명))

⑥ UNION ALL 방식

• 조건 입력 여부에 따라 쿼리 분기
• 값이 NULL이면 첫 쿼리만 실행, 값이 있으면 두 번째 쿼리 실행
  → 두 경우 모두 인덱스 액세스 조건 사용 가능

📌 예시

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

• 고객ID가 NULL인 데이터는 검색 안 됨
• 결과 불완전

⑦ NVL / DECODE 함수 활용

• 옵션 조건을 인덱스 액세스 조건으로 유지 가능
• 옵티마이저가 OR Expansion으로 변환하여 인덱스 활용 가능
• 단, NULL 허용 컬럼에서는 성능 이슈 주의

📌 예시

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

-- or

where 고객ID = decode(:cust_id, null, 고객ID, :cust_id)

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

💡 PL/SQL 함수 호출은 느리므로 인덱스로 최소화하라

① PL/SQL 함수가 느린 이유

1. 가상머신(VM) 내 인터프리터 실행
2. 호출 시마다 컨텍스트 스위칭 발생
3. 내장 SQL에 대한 Recursive Call 다수 발생

• 즉, 함수가 많을수록 Context Switching 부하 증가 → 느림

📌 예시

• encryption 함수 호출
• 인덱스가 없으면 함수 호출이 건수만큼 발생 (Full Scan)

where 암호화된_전화번호 = encryption(:phone_no)

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

STEP1. 아래와 같이 인덱스를 구성했다고 가정

1. 회원_X01 (생년)
2. 회원X02 (생년, 생월일, 암호화된전화번호)
3. 회원X03 (생년, 암호화된전화번호)

STEP2. 아래 쿼리 수행

SELECT /* +INDEX(회원_X0숫자) */
  FROM 회원 A
 WHERE 생년 = '1987'
   AND 암호화된_전화번호 encryption(:phone_no)

STEP3. 결과 요약

인덱스명	컬럼 순서	암호화된_전화번호 조건절 필터링 단계	encryption 함수 수행 횟수
회원_X01	(생년)	테이블 엑세스 단계	생년 = '1987' 조건을 만족하는 건수만큼 실행
회원_X02	(생년, 생월일, 암호화전화번호)	인덱스 필터 조건	생년 = '1987' 조건을 만족하는 건수만큼 실행
회원_X03	(생년, 암호화전화번호)	인덱스 액세스 조건	단 1회 수행

핵심 결론

• PL/SQL 함수의 성능 병목은 인덱스로 줄일 수 있다!!!!
• 자주 호출되는 함수(예: encryption, get_addr 등)는 함수 호출을 최소화하는 인덱스 순서로 설계해야 함
• 단일 컬럼 인덱스보다 복합 인덱스(함수 포함) 구조가 훨씬 효율적