Nonprefix 로컬 인덱스와 소트 #2

Nonprefix 로컬 인덱스로 소트를 회피할 수 없는 경우가 있다.

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

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    cd VARCHAR2(1)
  , dt DATE
  , vl NUMBER(1))
PARTITION BY RANGE (dt) (
    PARTITION p1 VALUES LESS THAN (DATE '2050-02-01')
  , PARTITION p2 VALUES LESS THAN (DATE '2050-03-01')
  , PARTITION p3 VALUES LESS THAN (MAXVALUE));

CREATE INDEX t1_x1 ON t1 (cd, dt) LOCAL;

INSERT INTO t1 VALUES ('A', DATE '2050-01-01', 1);
INSERT INTO t1 VALUES ('B', DATE '2050-01-02', 2);
INSERT INTO t1 VALUES ('A', DATE '2050-02-01', 1);
INSERT INTO t1 VALUES ('B', DATE '2050-02-02', 2);
INSERT INTO t1 VALUES ('A', DATE '2050-03-01', 1);
INSERT INTO t1 VALUES ('B', DATE '2050-03-02', 2);
COMMIT;

아래 쿼리는 t1_x1 인덱스의 열로 정렬했지만 cd 열을 부등호(>=)로 조회했기 때문에 소트가 발생했다.

-- 2
SELECT   *
    FROM t1
   WHERE cd >= 'A'
ORDER BY cd, dt;

CD DT         VL
-- ---------- --
A  2050-01-01  1
A  2050-02-01  1
A  2050-03-01  1
B  2050-01-02  2
B  2050-02-02  2
B  2050-03-02  2

6 행이 선택되었습니다.

-------------------------------------------------------------
| Id  | Operation                                   | Name  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                            |       |
|   1 |  SORT ORDER BY                              |       |
|   2 |   PARTITION RANGE ALL                       |       |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |
|*  4 |     INDEX RANGE SCAN                        | T1_X1 |
-------------------------------------------------------------

ORDER BY 절을 제거하면 파티션 별로 정렬된 데이터가 조회된다.

-- 3
SELECT *
  FROM t1
 WHERE cd >= 'A';

CD DT         VL
-- ---------- --
A  2050-01-01  1 -- p1
B  2050-01-02  2 -- p1
A  2050-02-01  1 -- p2
B  2050-02-02  2 -- p2
A  2050-03-01  1 -- p3
B  2050-03-02  2 -- p3

6 행이 선택되었습니다.

------------------------------------------------------------
| Id  | Operation                                  | Name  |
------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |
|   1 |  PARTITION RANGE ALL                       |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |
|*  3 |    INDEX RANGE SCAN                        | T1_X1 |
------------------------------------------------------------

1개의 파티션만 액세스하면 소트가 발생하지 않는다.

-- 4
SELECT   *
    FROM t1
   WHERE dt >= DATE '2050-01-01'
     AND dt <  DATE '2050-02-01'
     AND cd >= 'A'
ORDER BY cd, dt;

CD DT         VL
-- ---------- --
A  2050-01-01  1
B  2050-01-02  2

2 행이 선택되었습니다.

----------------------------------------------------
| Id  | Operation                          | Name  |
----------------------------------------------------
|   0 | SELECT STATEMENT                   |       |
|   1 |  PARTITION RANGE SINGLE            |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T1    |
|*  3 |    INDEX RANGE SCAN                | T1_X1 |
----------------------------------------------------

cd 열을 등호(=)로 조회해도 소트가 발생하지 않는다. Nonprefix 로컬 인덱스로 소트를 회피하기 위해서는 파티션 키 앞쪽의 열들을 모두 등호로 조회해야 한다.

-- 5
SELECT   *
    FROM t1
   WHERE cd = 'A'
ORDER BY cd, dt;

CD DT         VL
-- ---------- --
A  2050-01-01  1
A  2050-02-01  1
A  2050-03-01  1

3 행이 선택되었습니다.

----------------------------------------------------
| Id  | Operation                          | Name  |
----------------------------------------------------
|   0 | SELECT STATEMENT                   |       |
|   1 |  PARTITION RANGE ALL               |       |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T1    |
|*  3 |    INDEX RANGE SCAN                | T1_X1 |
----------------------------------------------------

cd 열의 값이 정해져 있다면 아래와 같은 기법을 활용할 수 있다.

-- 6
SELECT /*+ ORDERED USE_NL(B) NO_NLJ_BATCHING(B) */
       b.*
  FROM XMLTABLE ('"A","B"' COLUMNS cd VARCHAR2 (1) PATH '.');
     , t1 b
 WHERE b.cd = a.cd;

CD DT         VL
-- ---------- --
A  2050-01-01  1
A  2050-02-01  1
A  2050-03-01  1
B  2050-01-02  2
B  2050-02-02  2
B  2050-03-02  2

6 행이 선택되었습니다.

-----------------------------------------------------------------------------
| Id  | Operation                                   | Name                  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |
|   1 |  NESTED LOOPS                               |                       |
|   2 |   COLLECTION ITERATOR PICKLER FETCH         | XQSEQUENCEFROMXMLTYPE |
|   3 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1                    |
|*  4 |    INDEX RANGE SCAN                         | T1_X1                 |
-----------------------------------------------------------------------------

가장 간단한 해법은 비파티션 인덱스는 생성하는 것이다.

-- 7
DROP INDEX t1_x1;
CREATE INDEX t1_x1 ON t1 (cd, dt);

SELECT   *
    FROM t1
   WHERE cd >= 'A'
ORDER BY cd, dt;

CD DT         VL
-- ---------- --
A  2050-01-01  1
A  2050-02-01  1
A  2050-03-01  1
B  2050-01-02  2
B  2050-02-02  2
B  2050-03-02  2

6 행이 선택되었습니다.

----------------------------------------------------
| Id  | Operation                          | Name  |
----------------------------------------------------
|   0 | SELECT STATEMENT                   |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN                 | T1_X1 |
----------------------------------------------------