ROWNUM <= 1은 항상 빠르지 않다

• 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 |
--------------------------------------------------------------------------------------------------

• ROWNUM <= 1 조건을 가진 SQL의 성능을 빠르게 수행하기 위해서는 Where 절의 조건에 적절한 인덱스가 구성되어 있어야 한다.