INDEX MIN/MAX 오퍼레이션이 동작하지 않는 사례

INDEX MIN/MAX 오퍼레이션이 동작하지 않는 사례를 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT TO_CHAR (ADD_MONTHS (DATE '2049-12-01', ROWNUM), 'YYYYMM') AS c1, 'X' AS c2
  FROM XMLTABLE ('1 to 120');

CREATE INDEX t1_x1 ON t1 (c1);

아래 쿼리는 INDEX MIN/MAX 오퍼레이션으로 동작한다.

-- 2-1
SELECT MIN (c1) FROM t1;

--------------------------------------------
| Id  | Operation                  | Name  |
--------------------------------------------
|   0 | SELECT STATEMENT           |       |
|   1 |  SORT AGGREGATE            |       |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_X1 |
--------------------------------------------

-- 2-2
SELECT MIN (c1) FROM t1 WHERE c1 > '205012';

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  SORT AGGREGATE              |       |
|   2 |   FIRST ROW                  |       |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
----------------------------------------------

3-1번 쿼리는 c1 열을 가공했기 때문에 INDEX MIN/MAX 오퍼레이션으로 동작하지 않는다. 3-2번 쿼리처럼 MIN, MAX 값을 집계한 후 가공해야 한다.

-- 3-1
SELECT MIN (SUBSTR (c1, 1, 4)) FROM t1 WHERE c1 > '205012';

-----------------------------------
| Id  | Operation         | Name  |
-----------------------------------
|   0 | SELECT STATEMENT  |       |
|   1 |  SORT AGGREGATE   |       |
|*  2 |   INDEX RANGE SCAN| T1_X1 |
-----------------------------------

-- 3-2
SELECT SUBSTR (MIN (c1), 1, 4) FROM t1 WHERE c1 > '205012';

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  SORT AGGREGATE              |       |
|   2 |   FIRST ROW                  |       |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
----------------------------------------------

4-1번 쿼리는 c1 열에 LIKE 연산자를 사용했기 때문에 INDEX MIN/MAX 오퍼레이션으로 동작하지 않는다.^[각주:1] 4-2번 쿼리처럼 범위 조건을 사용해야 한다. 범위 조건을 사용하지 못한다면 4-3번 쿼리처럼 ORDER BY + ROWNUM 방식을 사용해야 한다.

-- 4-1
SELECT MIN (c1) FROM t1 WHERE c1 LIKE '2050%';

-----------------------------------
| Id  | Operation         | Name  |
-----------------------------------
|   0 | SELECT STATEMENT  |       |
|   1 |  SORT AGGREGATE   |       |
|*  2 |   INDEX RANGE SCAN| T1_X1 |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1" LIKE '2050%')
       filter("C1" LIKE '2050%')

-- 4-2
SELECT MIN (c1) FROM t1 WHERE c1 BETWEEN '205001' AND '205012';

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  SORT AGGREGATE              |       |
|   2 |   FIRST ROW                  |       |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1">'205012')

-- 4-3
SELECT c1
  FROM (SELECT   c1
            FROM t1
           WHERE c1 LIKE '2050%'
        ORDER BY c1)
 WHERE ROWNUM <= 1;

------------------------------------
| Id  | Operation          | Name  |
------------------------------------
|   0 | SELECT STATEMENT   |       |
|*  1 |  COUNT STOPKEY     |       |
|   2 |   VIEW             |       |
|*  3 |    INDEX RANGE SCAN| T1_X1 |
------------------------------------

5-1번 쿼리는 MIN, MAX 함수를 함께 사용했기 때문에 INDEX MIN/MAX 오퍼레이션으로 동작하지 않는다. 5-2, 5-3번 쿼리처럼 각각 MIN, MAX 값을 집계한 후 결과를 병합해야 한다.

-- 5-1
SELECT MIN (c1), MAX (c1) FROM t1 WHERE c1 > '205012';

-----------------------------------
| Id  | Operation         | Name  |
-----------------------------------
|   0 | SELECT STATEMENT  |       |
|   1 |  SORT AGGREGATE   |       |
|*  2 |   INDEX RANGE SCAN| T1_X1 |
-----------------------------------

-- 5-2
SELECT a.c1_min, b.c1_max
  FROM (SELECT MIN (c1) AS c1_min FROM t1 WHERE c1 > '205012') a
     , (SELECT MAX (c1) AS c1_max FROM t1 WHERE c1 > '205012') b;

------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|   1 |  NESTED LOOPS                  |       |
|   2 |   VIEW                         |       |
|   3 |    SORT AGGREGATE              |       |
|   4 |     FIRST ROW                  |       |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
|   6 |   VIEW                         |       |
|   7 |    SORT AGGREGATE              |       |
|   8 |     FIRST ROW                  |       |
|*  9 |      INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
------------------------------------------------

-- 5-3
SELECT MAX (c1_min) AS c1_min, MAX (c1_max) AS c1_max
  FROM (SELECT MIN (c1) AS c1_min, NULL     AS c1_max FROM t1 WHERE c1 > '205012' UNION ALL
        SELECT NULL     AS c1_min, MAX (c1) AS c1_max FROM t1 WHERE c1 > '205012');

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  SORT AGGREGATE                 |       |
|   2 |   VIEW                          |       |
|   3 |    UNION-ALL                    |       |
|   4 |     SORT AGGREGATE              |       |
|   5 |      FIRST ROW                  |       |
|*  6 |       INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
|   7 |     SORT AGGREGATE              |       |
|   8 |      FIRST ROW                  |       |
|*  9 |       INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
-------------------------------------------------

6-1번 쿼리는 HAVING 절에 집계 결과를 사용했기 때문에 INDEX MIN/MAX 오퍼레이션으로 동작하지 않는다. 6-2번 쿼리처럼 값을 다시 집계한 후 필터링해야 한다.

-- 6-1
SELECT MIN (c1)
  FROM t1
 WHERE c1 > '205012'
HAVING MIN (c1) IS NOT NULL;

------------------------------------
| Id  | Operation          | Name  |
------------------------------------
|   0 | SELECT STATEMENT   |       |
|*  1 |  FILTER            |       |
|   2 |   SORT AGGREGATE   |       |
|*  3 |    INDEX RANGE SCAN| T1_X1 |
------------------------------------

-- 6-2
SELECT MIN (c1)
  FROM (SELECT MIN (c1) AS c1
          FROM t1
         WHERE c1 > '205012')
HAVING MIN (c1) IS NOT NULL;

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|*  1 |  FILTER                         |       |
|   2 |   SORT AGGREGATE                |       |
|   3 |    VIEW                         |       |
|   4 |     SORT AGGREGATE              |       |
|   5 |      FIRST ROW                  |       |
|*  6 |       INDEX RANGE SCAN (MIN/MAX)| T1_X1 |
-------------------------------------------------

1. 인덱스 스캔 시작 위치를 찾는 과정의 비효율로 인해 기능을 구현하지 않은 것으로 추측된다. [본문으로]