Where절에 사용된 조건 컬럼에 인덱스가 있고 추출되는 데이터가 많으면 빠르게 수행됨.
하지만 인덱스에서 많은 데이터가 추출되고 나머지 조건 컬럼이 인덱스에 없다면 테이블을 조회하게 되는데,
이때 조건에 해당하는 데이터가 적다면 한건만 추출하더라도 속도가 느릴 수 있다.
-- 테이블 생성
DROP TABLE ROWNUM_T2 PURGE ;
CREATE TABLE rownum_t2
AS
SELECT LEVEL AS c1, CHR(65+MOD(LEVEL,26)) AS c2, LEVEL+99999 AS c3
FROM DUAL
CONNECT BY LEVEL <= 100000 ;
-- 추가 데이터 생성
BEGIN
FOR i IN 1..8 LOOP
INSERT INTO ROWNUM_T2 SELECT * FROM ROWNUM_T2;
COMMIT;
END LOOP;
END;
/
-- 인덱스 생성
CREATE INDEX rownum_t2_idx_01 ON rownum_t2 ( c2 ) ;
CREATE INDEX rownum_t2_idx_03 ON rownum_t2 (c2,c3) ;
-- 테스트[1] 빠른 조회가 되는 경우
(아무 조건이 없거나 인덱스 구성 컬럼만 존재할 때)
-- 아무 조건이 없는 경우
SELECT *
FROM ROWNUM_T2
WHERE ROWNUM <= 1 ;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 124 (99)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| ROWNUM_T2 | 28M| 799M| 124 (99)| 00:00:02 |
--------------------------------------------------------------------------------
-- 인덱스 구성 컬럼만 존재할 때
SELECT *
FROM rownum_t2
WHERE c2 IN ('P', 'S', 'H')
AND rownum <= 1 ;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| ROWNUM_T2 | 3318K| 91M| 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ROWNUM_T2_IDX_01 | 115K| | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
-- 테스트[2] 느린 조회가 되는 경우(ROWNUM_T2_IDX_01 구성 컬럼은 C2 컬럼임)
SELECT /*+ INDEX(ROWNUM_T2 ROWNUM_T2_IDX_01) */ *
FROM ROWNUM_T2
WHERE c2 IN ('P', 'S', 'H')
AND c1 = 1004
AND ROWNUM <= 1 ;
선택된 레코드가 없습니다.
경 과: 00:01:30.62
Execution Plan
----------------------------------------------------------
Plan hash value: 4236271276
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 7 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| ROWNUM_T2 | 2893 | 83897 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ROWNUM_T2_IDX_01 | 115K| | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
-- c2 컬럼이 인덱스 컬럼이므로 인덱스를 읽고 나서 c1 컬럼이 인덱스에 포함되어 있지 않으므로 테이블을 조회하게 된다.
-- c1 컬럼 조건에 해당하는 데이터가 테이블에 많다면 금방 찾을 수 있어서 수행 속도가 빠를 수 있지만,
-- 적다면 테이블을 거의 다 읽고 나서 찾아질 수도 있어서 수행 속도가 느릴 수 있다.
-- 해법 : 새로운 인덱스 생성(인덱스 구성 컬럼 c2 -> c2, c1)
CREATE INDEX rownum_t2_idx_02 ON rownum_t2 ( c2, c1 ) ;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 5 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| ROWNUM_T2 | 2893 | 83897 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ROWNUM_T2_IDX_02 | 463 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------