인덱스 기본 사용법

Last modified: 2026-02-02 01:06:53 +0900 View History

인덱스를 사용한다는 것

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

-- 인덱스에는 가공되지 않은 값이 저장돼 있는데, 가공된 값을 기준으로 검색하기 위한 스캔 시작점과 종료점을 찾을 수 없음
WHERE SUBSTR(생년월일, 5, 2) = '05'
WHERE NVL(주문수량, 0) < 100

-- '대한'을 포함하는 값은 전체 구간에 걸쳐 흩어져 있음
WHERE 업체명 LIKE '%대한%'

-- 수직적 탐색을 통해 전화번호가 '01012345678' 이거나 고객명이 '홍길동'인 어느 한 시작지점을 바로 찾을 수 없음
WHERE (전화번호 = :tel_no OR 고객명 = :cust_nm)

OR Expansion

SELECT *
FROM 고객
WHERE 고객명 = :cust_nm -- 고객명이 선두 컬럼인 인덱스 Range Scan
UNION ALL
SELECT *
FROM 고객
WHERE 전화번호 = :tel_no -- 전화번호가 선두 컬럼인 인덳 ㅡRange Scan
AND (고객명 <> :cust_nm OR 고객명 IS NULL)

SELECT /*+ use_concat */ *
FROM 고객
WHERE (전화번호 = :tel_no OR 고객명 = :cust_nm)

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

-- 아래처럼 하면 가능
SELECT *
FROM 고객
WHERE 전화번호 = :tel_no1
UNION ALL
SELECT *
FROM 고객
WHERE 전화번호 = :tel_no2

더 중요한 인덱스 사용 조건

-- 소속팀 + 사원명 + 연령
-- 선두 컬럼이 없어서 인덱스 못탐
SELECT *
FROM 사원
WHERE 사원명 = '홍길동'

-- 기준연도 + 과세구분코드 + 보고회차 + 실명확인번호
-- 인덱스 컬럼을 가공했으나, 선두 컬럼이 가공되지 않은 상태로 조건절에 있음
-- 인덱스 스캔 가능
SELECT *
FROM TBL
WHERE 기준연도 = :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
2   1        INDEX (RANGE SCAN) OF '주문상품_N1' (INDEX)
------------------------------------------------------

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

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

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

-- 변경순번 순으로 출력
SELECT *
FROM TBL
WHERE 장비번호 = 'C' AND 변경일자 = '20180316'

-- 옵티마이저는 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않음
-- PK 인덱스를 스캔하면서 출력한 결과집합이 어차피 변경순번 순으로 정렬되기 때문
SELECT *
FROM TBL
WHERE 장비번호 = 'C' AND 변경일자 = '20180316'
ORDER BY 변경순번

ORDER BY 절에서 컬럼 가공

-- PK가 (장비번호 + 변경일자 + 변경순번)

-- 정렬연산 생략
SELECT *
FROM TBL
WHERE 장비번호 = 'C'
ORDER BY 변경일자, 변경순번

-- 가공한 값 기준으로 정렬 요청했기 때문에 정렬 연산 필요
SELECT *
FROM TBL
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번

-- PK가 (주문일자 + 주문번호)
-- ORDER BY 대상이 TO_CHAR로 가공한 주문번호를 대상으로 하기 때문에, 정렬 연산이 생략되지 않음
SELECT *
FROM (
    SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
    FROM TBL A
    WHERE A.주문일자 = :dt AND A.주문번호 > NVL(:next_ord_no, 0)
    ORDER BY 주문번호
)
WHERE ROWNUM <= 30

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

SELECT-LIST에서 컬럼 가공

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

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

-- 인덱스가 문자열 기준으로 정렬돼 있는데, 이를 숫자값으로 바꾼 값 기준 최종 변경순번 요구
-- 정렬연산 생략 불가능
SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
FROM TBL
WHERE 장비번호 = 'C' AND 변경일자 = '20180316'

-- 정렬 연산 없이 최종 변경순번을 찾을 수 있음
-- 변경순번 값이 고정너비로 입력돼 있어야 함
SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)
FROM TBL
WHERE 장비번호 = 'C' AND 변경일자 = '20180316'

-- 정렬 연산 없이 MIN/MAX, FIRST ROW 방식으로 최종변경일자 출력
SELECT 장비번호, 장비명, 상태코드
    ,(SELECT MAX(변경일자)
      FROM TBL1
      WHERE 장비번호 = P.장비번호) 최종변경일자
FROM TBL2 P
WHERE 장비구분코드 = 'A001'

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

-- 가독성은 올라가지만, 인덱스 컬럼을 가공함
-- 각 장비가 속한 과거 이력 데이터를 모두 읽어야 해 장비당 이력 레코드가 많다면 성능 이슈가 있음
SELECT 장비번호, 장비명, 상태코드
    , SUBSTR(최종이력, 1, 8) 최종변경일자
    , SUBSTR(최종이력, 9) 최종변경순번
FROM(
    SELECT 장비번호, 장비명, 상태코드
            , (SELECT MAX(변경일자 || 변경순번)
                FROM TBL1
                WHERE 장비번호 = P.장비번호) 최종이력
    FROM TBL2 P
    WHERE 장비구분코드 = 'A001'
)

자동 형변환

SELECT *
FROM TBL
WHERE 생년월일 = 19821225

-- 옵티마이저가 실제 실행한 SQL
SELECT *
FROM TBL
WHERE TO_NUMBER(생년월일) = 19821225

-- 좌변 컬럼 기준으로 우변을 변환하므로, 인덱스 사용이 가능
SELECT *
FROM 고객
WHERE 가입일자 = '01-JAN-2018'

-- NLS_DATE_FORMAT 파라미터가 다르게 설정된 환경에서 수행하면 컴파일 오류가 발생하거나 결과집합이 달라질 수 있음
-- 아래처럼 날짜 포맷을 정확히 지정해주자
SELECT *
FROM 고객
WHERE 가입일자 = TO_DATE('01-JAN-2018', 'DD-MON-YYYY')

자동 형변환 주의

-- 문자형 컬럼(v_col)에 숫자형 컬럼(n_col)로 변환할 수 없는 문자열이 입력되면 에러 발생
WHERE n_col = v_col

-- 1번
SELECT ROUND(AVG(sal)) avg_sal
        ,MIN(sal) min_sal
        ,MAX(sal) max_sal
        ,MAX(DECODE(job, 'PRESIDENT', NULL, sal)) max_sal2
FROM EMP

-- 2번
SELECT empno, ename, job, sal
FROM EMP
WHERE job <> 'PRESIDENT'
ORDER BY sal DESC;

-- 3번
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