인덱스를 이용한 최적화
UNIQUE SCAN
UNIQUE SCAN: 유일한 결과를 보장하는 경우
SELECT * FROM TB_COMPANY_MASTER WHERE LISTING_DT > '1995-01-01' and BIZ_REG_NO = '123-45-67890';
BIZ_REG_NO에는 UNIQUE INDEX가 설정되어 있음UNIQUE SCAN으로 ROWID(행 위치)를 찾은 다음, 조건에 맞는 행을 FilterWHERE에서 순서를 바꿔도 동일하게 최적화됨
FULL SCAN
- 인덱스 전체를 순서대로 스캔
- 회사 이름 순으로 정렬: 인덱스에 회사 이름이 정렬되어 있으므로
FULL SCAN
SELECT * FROM TB_COMPANY_MASTER ORDER BY COMPANY_NM;
- 테이블 전체를 보는 경우 회사 이름 순이 아니므로 인덱스를
FULL SCAN하지 않음
SELECT * FROM TB_COMPANY_MASTER;
FAST FULL SCAN
- 테이블을 스캔하는 것보다 인덱스를 스캔하는 것이 더 빠를 때, 순서 없이 스캔
SELECT BIZ_REG_NO, COMPANY_NM FROM TB_COMPANY_MASTER;
BIZ_REG_NO와 COMPANY_NM을 각각 인덱스에서 FAST FULL SCAN한 후 JOIN
RANGE SCAN
- 일정 범위를 인덱스에서 스캔
- 1980년부터 2000년 사이
SELECT * FROM TB_COMPANY_MASTER
WHERE ESTABLISH_DT BETWEEN TO_DATE('1980', 'YYYY') AND TO_DATE('2000', 'YYYY');
SELECT * FROM TB_COMPANY_MASTER WHERE COMPANY_NM LIKE '(주)%';
- 회사 이름이 "자"로 끝나는 경우는
RANGE SCAN을 안함(사전순으로 정렬되어 있으므로)
SELECT * FROM TB_COMPANY_MASTER WHERE COMPANY_NM LIKE '%자';
SKIP SCAN
- 여러 개의 컬럼으로 이뤄진
COMPOSITE 인덱스에서 두 번째 이후 컬럼으로 검색할 때
- 첫번째 컬럼 → 두번째 컬럼 순으로 정렬되어 있으므로 건너 뛰면서(
SKIP) 스캔
FS_TYPE과 FS_YM이 하나의 인덱스로 되어 있으므로 특정 FS_YM을 찾으면 SKIP 스캔 실행
SELECT * FROM TB_FINANCIAL_STATEMENT WHERE FS_YM = '202312';
- 특정
FS_TYPE을 찾는 경우에는 RANGE 스캔
SELECT * FROM TB_FINANCIAL_STATEMENT WHERE FS_TYPE = 'ANNUAL';
SARGABLE
Search Argument Able의 줄임말로 인덱스를 효과적으로 사용할 수 있는 형태
- Sargable 연산자:
=, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, IN
- 회사 이름이 흑풍 그룹과 같은 경우(sargable)
SELECT * FROM TB_COMPANY_MASTER WHERE COMPANY_NM = '흑풍그룹';
- 회사 이름이 흑풍 그룹과 다른 경우(not sargable)
SELECT * FROM TB_COMPANY_MASTER WHERE COMPANY_NM <> '흑풍그룹';
컬럼에 계산
- 비교할 컬럼에 계산이나 함수를 적용할 경우
not sargable
SELECT * FROM TB_FINANCIAL_STATEMENT WHERE TOTAL_ASSETS * 1.1 > 1000000000000;
SELECT * FROM TB_FINANCIAL_STATEMENT WHERE TOTAL_ASSETS > 1000000000000 / 1.1;
- 설립일자에서 연도만 뽑아서 2000과 비교하는 경우
not sargable
SELECT * FROM TB_COMPANY_MASTER WHERE TO_CHAR(ESTABLISH_DT, 'YYYY') < '2000';
- 2000을 날짜로 바꿔서 설립 일자와 비교
sargable
SELECT * FROM TB_COMPANY_MASTER WHERE ESTABLISH_DT < TO_DATE('2000', 'YYYY');
함수 기반 인덱스
- 함수 기반 인덱스가 설정된 경우에는 함수를 적용해도
sargable
SELECT * FROM TB_COMPANY_MASTER WHERE UPPER(COMPANY_ENG_NM) < 'E';
SELECT INDEX_NAME, COLUMN_EXPRESSION
FROM ALL_IND_EXPRESSIONS
WHERE INDEX_NAME = 'IDX_COMPANY_COMPANY_ENG_NM_UPPER';
- 함수 기반 인덱스가 설정되어 있지 않으면
not sargable
SELECT * FROM TB_COMPANY_MASTER WHERE LOWER(COMPANY_ENG_NM) < 'e';
UNION을 이용해 인덱스 사용하기
OR로 검색을 할 경우 인덱스를 사용하지 못할 수 있음
SELECT * FROM TB_COMPANY_MASTER WHERE COMPANY_NM LIKE '(주)%' OR ESTABLISH_DT > '2000-01-01';
- 각각 인덱스를 이용해 검색을 하도록 질의한 후, 그 결과를
UNION이나 UNION ALL을 이용하여 합칠 수 있음
- 단, 여러 번 검색으로 총 검색 비용은 더 높을 수도 있으므로 실행 계획을 비교해볼 것
SELECT * FROM TB_COMPANY_MASTER WHERE COMPANY_NM LIKE '(주)%'
UNION
SELECT * FROM TB_COMPANY_MASTER WHERE ESTABLISH_DT > '2000-01-01';
