Oracle/Tuning2019. 10. 29. 14:35

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

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


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

1
2
3
4
5
6
7
8
-- 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 오퍼레이션으로 동작한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 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 값을 집계한 후 가공해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 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 방식을 사용해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- 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 값을 집계한 후 결과를 병합해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- 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번 쿼리처럼 값을 다시 집계한 후 필터링해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 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. 인덱스 스캔 시작 위치를 찾는 과정의 비효율로 인해 기능을 구현하지 않은 것으로 추측된다. [본문으로]
저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

POWER 함수의 CPU 연산 #2  (0) 2019.11.23
WINDOW SORT로 인한 성능 저하  (0) 2019.11.22
OUTER OR JOIN 조건 #2  (0) 2019.10.28
V$DIAG_TRACE_FILE_CONTENT 뷰  (0) 2019.10.18
Null-Aware 안티 조인  (1) 2019.10.17
Posted by 정희락_

티스토리툴바