인덱스 스캔 효율화

Last modified: 2026-03-03 18:58:29 +0900 View History

인덱스 탐색

인덱스 스캔 효율성

엑세스 조건과 필터 조건

Index

WHERE C1 = 'B'

Index

WHERE C1 = 'B' AND C2 = 3

Index

WHERE C1 = 'B' AND C2 >= 3

Index

WHERE C1 = 'B' AND C2 <= 3

Index

WHERE C1 = 'B' AND C2 BETWEEN 2 AND 3

Index

WHERE C1 BETWEEN 'A' AND 'C' AND C2 BETWEEN 2 AND 3

Index

인덱스 스캔 효율성

Dictionary
용어사전의 각 문자별로 인덱스를 생성한 경우

인덱스 스캔 효율성 측정

Rows    Row Source Operation
----    --------------------------------------------------------------------
10      TABLE ACCESS BY INDEX ROWID TBL (cr=7471 pr=1466 pw=0 time 22137 us)
10          INDEX RANGE SCAN TBL_IDX (cr=7463 pr=1466 pw=0 time 22328 us)

엑세스 조건과 필터 조건

Dictionary

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

LeafBlock

범위검색 조건 맨 처음과 마지막 구간에서의 엑세스 조건

Predicate information (identified by operation id):
-----------------------------------------------------------------------
2 - access("C1">=1 AND "C2"='A' AND "C3"='나' AND "C4"='a' AND "C1"<=3)
2 - filter("C2"='A' AND "C3"='나' AND "C4"='a')

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

SELECT *
FROM 매물아파트매매
WHERE 아파트시세코드='A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
AND 인터넷매물 BETWEEN '1' AND '3'
ORDER BY 입력일 DESC
img
인덱스 (아파트시세코드 + 평형 + 평형타입 + 인터넷매물)
img
인덱스 (인터넷매물 + 아파트시세코드 + 평형 + 평형타입)

BETWEEN을 IN-List로 전환

SELECT *
FROM 매물아파트매매
WHERE 아파트시세코드='A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
AND 인터넷매물 IN ('1', '2', '3')
ORDER BY 입력일 DESC
img
--------------------------------------------------------------------------------
| Id | Operation                      | Name            | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                 |      |       |    6 |
|  1 |  INLIST ITERATOR               |                 |      |       |      |
|  2 |   TABLE ACCESS BY INDEX ROWID  | 매물아파트매매    |    3 |    37 |    6 |
|  3 |    INDEX RANGE SCAN            | 매물아파트매매_PK |    3 |       |    5 |
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                 |      1 |      3 |      12 |
|  1 |  INLIST ITERATOR               |                 |      1 |      3 |      12 |
|  2 |   TABLE ACCESS BY INDEX ROWID  | 매물아파트매매    |      3 |      3 |      12 |
|  3 |    INDEX RANGE SCAN            | 매물아파트매매_PK |      3 |      3 |      10 |
--------------------------------------------------------------------------------------

-- 인덱스를 3번 탐색한다는 것은 SQL을 아래와 같이 작성한 것과 같음
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from   매물아파트매매
where  인터넷매물 = '1'
and    아파트시세코드='A01011350900056'
and    평형 = '59'
and    평형타입 = 'A'
union all
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from   매물아파트매매
where  인터넷매물 = '2'
and    아파트시세코드='A01011350900056'
and    평형 = '59'
and    평형타입 = 'A'
union all
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from   매물아파트매매
where  인터넷매물 = '3'
and    아파트시세코드='A01011350900056'
and    평형 = '59'
and    평형타입 = 'A'
order by 입력일 desc
img
select /*+ ordered use_nl(b) */ b.해당층, b.평당가, b.입력일
     , b.해당동, b.매물구분, b.연사용일수, b.중개업소코드
from   통합코드 a, 매물아파트매매 b
where  a.코드구분 = 'CD064' -- 인터넷매물구분
and    a.코드 between '1' and '3'
and    b.인터넷매물 = a.코드
and    b.아파트시세코드 = 'A01011350900056'
and    b.평형 = '59'
and    b.평형타입 = 'A'
order by b.입력일 desc

BETWEEN 조건을 IN-List로 전환할 때 주의사항

img
img

Index Skip Scan 활용

-- 2018년 월별로 10만개 판매데이터 입력
-- 판매구분 값별로는 'A'가 10만개, 'B'가 110만개
create table 월별고객별판매집계
as
select rownum 고객번호
     , '2018' || lpad(ceil(rownum/100000), 2, '0') 판매월
     , decode(mod(rownum, 12), 1, 'A', 'B') 판매구분
     , round(dbms_random.value(1000,100000), -2) 판매금액
from   dual
connect by level <= 1200000 ;

select count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 between '201801' and '201812'

create index 월별고객별판매집계_IDX1 on 월별고객별판매집계(판매구분, 판매월);

-- 인덱스를 스캔하면서 281개의 블록 I/O 발생
-- 테이블 엑세스는 발생하지 않음
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=281 pr=0 pw=0 time=47753 us)
 100000    INDEX RANGE SCAN 월별고객별판매집계_IDX1 (cr=281 pr=0 pw=0 time= ... )
img
create index 월별고객별판매집계_IDX2 on 월별고객별판매집계(판매월, 판매구분);

select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 between '201801' and '201812'

-- 인덱스를 스캔하면서 3090개 블록 I/O 발생
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3090 pr=0 pw=0 time=206430 us)
 100000    INDEX RANGE SCAN 월별고객별판매집계_IDX2 (cr=3090 pr=0 pw=0 time= ... )

select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 in ( '201801', '201802', '201803', '201804', '201805', '201806'
                , '201807', '201808', '201809', '201810', '201811', '201812' )

-- 3090개이던 블록 I/O 개수가 314개로 감소
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=314 pr=0 pw=0 time=31527 us)
 100000    INLIST ITERATOR (cr=314 pr=0 pw=0 time=900030 us)
 100000      INDEX RANGE SCAN 월별고객별판매집계_IDX2 (cr=314 pr=0 pw=0 time= ... )

select /*+ INDEX_SS(t 월별고객별판매집계_IDX2) */ count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 between '201801' and '201812'

-- 블록 I/O 300개 발생
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=300 pr=0 pw=0 time=94282 us)
 100000    INDEX SKIP SCAN 월별고객별판매집계_IDX2 (cr=300 pr=0 pw=0 time=500073 us)

IN 조건은 '='인가

-- 고객번호의 평균 카디널리티는 3이라고 가정(고객별로 평균 3 건의 상품 가입)
select *
from   고객별가입상품
where  고객번호 = :cust_no
and    상품ID in ('NH00037', 'NH00041', 'NH00050')
img
-- IN-List Iterator로 풀린다는 것은 SQL이 아래와 같은 방식으로 실행된다는 의미
select *
from   고객별가입상품
where  고객번호 = :cust_no
and    상품ID = 'NH00037'
union all
select *
from   고객별가입상품
where  고객번호 = :cust_no
and    상품ID = 'NH00041'
union all
select *
from   고객별가입상품
where  고객번호 = :cust_no
and    상품ID = 'NH00050'
img

다른 예시

select * from 상품
where 상품ID = :prod_id
and   상품구분코드 in ( 'GX', 'KR' )

Execution Plan
---------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=38)
1    0   TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=2 Card=1 Bytes=38)
2    1     INDEX (RANGE SCAN) OF '상품_X01' (INDEX) (Cost=1 Card=1)
---------------------------------------------------------------------------

Predicate information (identified by operation id): 
---------------------------------------------------
   2 - access("상품ID"=:PROD_ID)
   2 - filter("상품구분코드"='GX' OR "상품구분코드"='KR')

NUM_INDEX_KEYS 힌트 활용

select /*+ num_index_keys(a 고객별가입상품_X1 1) */ *
from   고객별가입상품 a
where  고객번호 = :cust_no
and    상품ID in ('NH00037', 'NH00041', 'NH00050')

Execution Plan
-------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   TABLE ACCESS (BY INDEX ROWID BATCHED) OF '고객별가입상품' (TABLE)
2    1     INDEX (RANGE SCAN) OF '고객별가입상품_X1' (INDEX)

Predicate information (identified by operation id):
---------------------------------------------------
   2 - access("고객번호"= TO_NUMBER(:CUST_NO))
   2 - filter("상품ID"='NH00037' OR "상품ID"='NH00041' OR "상품ID"='NH00050')

-- 힌트대신 인덱스 컬럼을 가공해도 됨
select *
from   고객별가입상품
where  고객번호 = :cust_no
and    RTRIM(상품ID) in ('NH00037', 'NH00041', 'NH00050')

select *
from   고객별가입상품
where  고객번호 = :cust_no
and    상품ID || '' in ('NH00037', 'NH00041', 'NH00050')

-- 상품ID까지 인덱스 엑세스 조건으로 사용하는 경우
-- 상품ID가 IN-List Iterator 방식으로 풀리면서 인덱스 엑세스 조건으로 사용
select /*+ num_index_keys(a 고객별가입상품_X1 2) */ *
from   고객별가입상품 a
where  고객번호 = :cust_no
and    상품ID in ('NH00037', 'NH00041', 'NH00050')

Execution Plan
-------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   INLIST ITERATOR
2    1     TABLE ACCESS (BY INDEX ROWID BATCHED) OF '고객별가입상품' (TABLE)
3    2       INDEX (RANGE SCAN) OF '고객별가입상품_X1' (INDEX)

Predicate information (identified by operation id):
---------------------------------------------------
   3 - access("고객번호"= TO_NUMBER(:CUST_NO)) AND ("상품ID"='NH00037' OR "상품
ID"='NH00041' OR "상품ID"='NH00050'))

BETWEEN과 LIKE 스캔 범위 비교

-- 인덱스를 (판매월 + 판매구분)
where  판매월 BETWEEN '201901' and '201912'
and    판매구분 = 'B'

where  판매월 LIKE '2019%'
and    판매구분 = 'B'
img
A가 90%, B가 10% 비중인 경우
img
A가 10%, B가 90% 비중인 경우

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

SELECT 거래일자, 종목코드, 투자자유형코드
     , 주문매체코드, 체결건수, 체결수량, 거래대금
FROM   일별종목거래
WHERE  거래일자 BETWEEN :시작일자 AND :종료일자  -- 필수 조건
AND    종목코드 BETWEEN :종목1 AND :종목2        -- 옵션 조건
AND    투자자유형코드 BETWEEN :투자자유형1 AND :투자자유형2  -- 옵션 조건
AND    주문매체구분코드 BETWEEN :주문매체구분1 AND :주문매체구분2 -- 옵션 조건

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

OR 조건 활용

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

Execution Plan
------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   TABLE ACCESS (FULL) OF '거래' (TABLE)

Execution Plan
------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   FILTER
2    1     TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE)
3    2       INDEX (RANGE SCAN) OF '거래_IDX3' (INDEX)

Predicate information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:DT1)<=TO_DATE(:DT2))
   2 - filter(:CUST_ID IS NULL OR "고객ID"=TO_NUMBER(:CUST_ID))
   3 - access("거래일자">=:DT1 AND "거래일자"<=:DT2)

select * from 거래
where 고객ID = :cust_id
and   ((:dt_type = 'A' AND 거래일자 between :dt1 and :dt2)
       or
       (:dt_type = 'B' AND 결제일자 between :dt1 and :dt2))

-- Execution Plan
---------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   CONCATENATION
2    1     FILTER
3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
4    3         INDEX (RANGE SCAN) OF '거래_IDX1' (INDEX)  -- 고객ID + 거래일자
5    1     FILTER
6    5       TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
7    6         INDEX (RANGE SCAN) OF '거래_IDX2' (INDEX)  -- 고객ID + 결제일자

LIKE/BETWEEN 조건 활용

-- 인덱스 : 등록일시 + 상품분류코드
select * from 상품
where  등록일시 >= trunc(sysdate)      -- 필수 조건(당일 등록 상품)
and    상품분류코드 like :prd_cls_cd || '%' -- 옵션 조건

-- 인덱스 : 상품명 + 상품분류코드
select * from 상품
where  상품명 = :prd_nm                -- 필수 조건
and    상품분류코드 like :prd_cls_cd || '%' -- 옵션 조건

-- 인덱스 : 상품대분류코드 + 상품코드
select * from 상품
where  상품대분류코드 = :prd_lcls_cd  -- 필수 조건
and    상품코드 like :prd_cd || '%'  -- 옵션 조건

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

Execution Plan
---------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   UNION ALL
2    1     FILTER    -- :cust_id is null
3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
4    3         INDEX (RANGE SCAN) OF '거래_IDX1' (INDEX)  -- 거래일자
5    1     FILTER    -- :cust_id is not null
6    5       TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
7    6         INDEX (RANGE SCAN) OF '거래_IDX2' (INDEX)  -- 고객ID + 거래일자

NVL/DECODE 함수 활용

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

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

Execution Plan
---------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   CONCATENATION
2    1     FILTER    -- :cust_id is null
3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
4    3         INDEX (RANGE SCAN) OF '거래_IDX1' (INDEX)  -- 거래일자
5    1     FILTER    -- :cust_id is not null
6    5       TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
7    6         INDEX (RANGE SCAN) OF '거래_IDX2' (INDEX)  -- 고객ID + 거래일자

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

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

-- 회원 테이블 Full Scan
-- encryption 함수는 테이블 건수만큼 수행
select /*+ full(a) */ 회원번호, 회원명, 생년, 생월일, 등록일자
from   회원 a
where  암호화된_전화번호 = encryption( :phone_no )

-- encryption 함수는 조건절을 만족하는 건수만큼 수행
select /*+ full(a) */ 회원번호, 회원명, 생년, 생월일, 등록일자
from   회원 a
where  생년 = '1987'
and    암호화된_전화번호 = encryption( :phone_no )

-- 인덱스 생성
create index 회원_X01 on 회원(생년);
create index 회원_X02 on 회원(생년, 생월일, 암호화된_전화번호);
create index 회원_X03 on 회원(생년, 암호화된_전화번호);

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

Execution Plan
---------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=156)
1    0   TABLE ACCESS (BY INDEX ROWID BATCHED) OF '회원' (TABLE) (Cost=1 ... )
2    1     INDEX (RANGE SCAN) OF '회원_X01' (INDEX) (Cost=1 Card=1)

Predicate information (identified by operation id):
---------------------------------------------------
   1 - filter("암호화된_전화번호"="ENCRYPTION"(:PHONE_NO))
   2 - access("생년"='1987')

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

Execution Plan
---------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=156)
1    0   TABLE ACCESS (BY INDEX ROWID BATCHED) OF '회원' (TABLE) (Cost=1 ... )
2    1     INDEX (RANGE SCAN) OF '회원_X02' (INDEX) (Cost=2 Card=1)

Predicate information (identified by operation id):
---------------------------------------------------
   2 - access("생년"='1987' AND "암호화된_전화번호"="ENCRYPTION"(:PHONE_NO))
   2 - filter("암호화된_전화번호"="ENCRYPTION"(:PHONE_NO))

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

Execution Plan
---------------------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=156)
1    0   TABLE ACCESS (BY INDEX ROWID BATCHED) OF '회원' (TABLE) (Cost=2 ... )
2    1     INDEX (RANGE SCAN) OF '회원_X03' (INDEX) (Cost=1 Card=1)

Predicate information (identified by operation id):
---------------------------------------------------
   2 - access("생년"='1987' AND "암호화된_전화번호"="ENCRYPTION"(:PHONE_NO))