INDEX_DESC와 ROWNUM <= 1을 함께 사용하지 말자

• 최대값, 최소값 추출시 빈번하게 사용되는 방법임.
• 인덱스의 상태가 Unusable 상태이거나, 인덱스가 존재하지 않으면 잘못된 데이터가 추출될 수 있는 위험 있음.

-- 테이블 생성
CREATE TABLE ROWNUM_T1 AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS c2
FROM DUAL CONNECT BY LEVEL <= 500000 ;

-- 인덱스 생성
CREATE INDEX IDX_01 ON ROWNUM_T1 ( C2, C1 ) ;


-- SQL(1)

SELECT /*+ INDEX_DESC(T1 IDX_01) */
       c1
FROM   ROWNUM_T1 t1
WHERE  t1.c2 = 'A'
AND    t1.c1 >= 0
ABD    ROWNUM <= 1;

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    16 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_01 | 23112 |   361K|     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


-- SQL(2)

SELECT c1
FROM  (SELECT /*+ INDEX_DESC(T1 IDX_01) */
              c1
       FROM   ROWNUM_T1 t1
       WHERE  t1.c2 = 'A'
       AND    t1.c1 >= 0
       ORDER BY c2 DESC, c1 DESC
       )
WHERE  ROWNUM <= 1;

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |        |       |       |            |          |
|   2 |   VIEW                        |        | 23112 |   293K|     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN DESCENDING| IDX_01 | 23112 |   361K|     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

-- SQL(3)

SELECT MAX(c1) AS c1
FROM   ROWNUM_T1 t1
WHERE  t1.c2 = 'A'
AND    t1.c1 >= 0;

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    16 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    16 |            |          |
|   2 |   FIRST ROW                  |        |     1 |    16 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDX_01 |     1 |    16 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

• SQL(1), SQL(2), SQL(3) 성능 차이는 거의 없음.
• 정합성에 문제가 없는 SQL(2), SQL(3) 을 사용하는 것이 좋음.