Nonprefix 로컬 인덱스와 소트 #1

Nonprefix 로컬 인덱스는 구조적 특징으로 인해 정렬을 대체할 수 없는 경우가 있다.

테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자. t1_x1 인덱스는 Prefix 로컬 인덱스, t1_x2 인덱스는 Nonprefix 로컬 인덱스다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1, c2)
PARTITION BY RANGE (c1) (
    PARTITION p1 VALUES LESS THAN (3),
    PARTITION p2 VALUES LESS THAN (MAXVALUE))
AS
SELECT LEVEL AS c1 , 5 - LEVEL AS c2 FROM DUAL CONNECT BY LEVEL <= 4;

CREATE INDEX t1_x1 ON t1 (c1, c2) LOCAL;
CREATE INDEX t1_x2 ON t1 (c2, c1) LOCAL;

아래에서 쿼리 2-1은 인덱스 순서대로 결과가 정렬된 반면, 쿼리 2-2는 파티션 별로 결과가 정렬된다.

-- 2-1
SELECT /*+ INDEX(T1 T1_X1) */ c1, c2 FROM t1 WHERE c1 > 0;

C1 C2
-- --
 1  4
 2  3
 3  2
 4  1

4 행이 선택되었습니다.

-----------------------------------------------------
| Id  | Operation           | Name  | Pstart| Pstop |
-----------------------------------------------------
|   0 | SELECT STATEMENT    |       |       |       |
|   1 |  PARTITION RANGE ALL|       |     1 |     2 |
|*  2 |   INDEX RANGE SCAN  | T1_X1 |     1 |     2 |
-----------------------------------------------------

-- 2-2
SELECT /*+ INDEX(T1 T1_X2) */ c2, c1 FROM t1 WHERE c2 > 0;

C1 C2
-- --
 3  2 -- P1
 4  1 -- P1
 1  4 -- P2
 2  3 -- P2

4 행이 선택되었습니다.

-----------------------------------------------------
| Id  | Operation           | Name  | Pstart| Pstop |
-----------------------------------------------------
|   0 | SELECT STATEMENT    |       |       |       |
|   1 |  PARTITION RANGE ALL|       |     1 |     2 |
|*  2 |   INDEX RANGE SCAN  | T1_X2 |     1 |     2 |
-----------------------------------------------------

ORDER BY 절로 직접 정렬을 수행하면 쿼리 3-2의 실행 계획에서 소트가 발생하는 것을 확인할 수 있다.

-- 3-1
SELECT /*+ INDEX(T1 T1_X1) */ c1, c2 FROM t1 WHERE c1 > 0 ORDER BY c1, c2;

-----------------------------------------------------
| Id  | Operation           | Name  | Pstart| Pstop |
-----------------------------------------------------
|   0 | SELECT STATEMENT    |       |       |       |
|   1 |  PARTITION RANGE ALL|       |     1 |     2 |
|*  2 |   INDEX RANGE SCAN  | T1_X1 |     1 |     2 |
-----------------------------------------------------

-- 3-2
SELECT /*+ INDEX(T1 T1_X2) */ c2, c1 FROM t1 WHERE c2 > 0 ORDER BY c2, c1;

------------------------------------------------------
| Id  | Operation            | Name  | Pstart| Pstop |
------------------------------------------------------
|   0 | SELECT STATEMENT     |       |       |       |
|   1 |  SORT ORDER BY       |       |       |       | -- !
|   2 |   PARTITION RANGE ALL|       |     1 |     2 |
|*  3 |    INDEX RANGE SCAN  | T1_X2 |     1 |     2 |
------------------------------------------------------

아래 쿼리는 INDEX_DESC 방식으로 최고 값을 조회한다. 쿼리 4-1은 최고 값인 4를 반환한 반면, 쿼리 4-2는 첫 번째 파티션(p1)의 최고 값인 2를 반환한다. 

-- 4-1
SELECT /*+ INDEX_DESC(A T1_X1) */ c1 FROM t1 WHERE c1 > 0 AND ROWNUM <= 1;

C1
--
 4

1개의 행이 선택되었습니다.

---------------------------------------------------------------
| Id  | Operation                     | Name  | Pstart| Pstop |
---------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |       |       |
|*  1 |  COUNT STOPKEY                |       |       |       |
|   2 |   PARTITION RANGE ALL         |       |     2 |     1 |
|*  3 |    INDEX RANGE SCAN DESCENDING| T1_X1 |     2 |     1 |
---------------------------------------------------------------

-- 4-2
SELECT /*+ INDEX_DESC(A T1_X2) */ c2 FROM t1 WHERE c2 > 0 AND ROWNUM <= 1;

C1
--
 2

1개의 행이 선택되었습니다.

---------------------------------------------------------------
| Id  | Operation                     | Name  | Pstart| Pstop |
---------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |       |       |
|*  1 |  COUNT STOPKEY                |       |       |       |
|   2 |   PARTITION RANGE ALL         |       |     2 |     1 |
|*  3 |    INDEX RANGE SCAN DESCENDING| T1_X2 |     2 |     1 |
---------------------------------------------------------------

아래 쿼리는 MAX 함수로 최고 값을 조회한다. 쿼리 5-1은 마지막 파티션(p2)만 액세스하여 최고 값을 구하고, 쿼리 5-2는 파티션 별로 최고 값을 구한 후 그 중에 다시 최고 값을 구하는 방식으로 처리된다.

-- 5-1
SELECT MAX (c1) AS c1 FROM t1 WHERE c1 > 0;

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | Pstart| Pstop | A-Rows | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |       |       |      1 |       1 |
|   1 |  SORT AGGREGATE               |       |      1 |       |       |      1 |       1 |
|   2 |   FIRST ROW                   |       |      1 |       |       |      1 |       1 | -- !
|   3 |    PARTITION RANGE ALL        |       |      1 |     2 |     1 |      1 |       1 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| T1_X1 |      1 |     2 |     1 |      1 |       1 |
-------------------------------------------------------------------------------------------

-- 5-2
SELECT MAX (c2) AS c2 FROM t1 WHERE c2 > 0;

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | Pstart| Pstop | A-Rows | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |       |       |      1 |       2 |
|   1 |  SORT AGGREGATE               |       |      1 |       |       |      1 |       2 |
|   2 |   PARTITION RANGE ALL         |       |      1 |     2 |     1 |      2 |       2 |
|   3 |    FIRST ROW                  |       |      2 |       |       |      2 |       2 | -- !
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| T1_X2 |      2 |     2 |     1 |      2 |       2 |
-------------------------------------------------------------------------------------------