Nonprefix 로컬 인덱스 개선 사례

PARTITION FOR 절을 사용하여 Nonprefix 로컬 인덱스의 테이블 액세스를 제거한 개선 사례를 살펴보자.

테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자. 파티션 키인 ym은 파티션 별로 유일하며, t1_x1 인덱스는 파티션 키가 포함되지 않은 Nonprefix 로컬 인덱스다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    ym VARCHAR2(6)
  , cd VARCHAR2(1))
PARTITION BY RANGE (ym) (
    PARTITION p1 VALUES LESS THAN ('205001')
  , PARTITION p2 VALUES LESS THAN ('205002')
  , PARTITION pm VALUES LESS THAN (MAXVALUE));

CREATE INDEX t1_x1 ON t1 (cd) LOCAL;

INSERT INTO t1 VALUES ('205001', 'A');
INSERT INTO t1 VALUES ('205001', 'B');
INSERT INTO t1 VALUES ('205001', 'C');
COMMIT;

아래 쿼리는 ym이 205001인 행을 필터링하기 위해 테이블을 액세스했다.

-- 2
SELECT 1
  FROM t1
 WHERE ym = '205001'
   AND cd = 'A';

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("YM"='205001')
   3 - access("CD"='A')

파티션 별로 파티션 키 값이 유일하기 때문에 PARTITION FOR 절로 테이블 액세스를 제거할 수 있다.

-- 3
SELECT 1
  FROM t1 PARTITION FOR ('205001')
 WHERE cd = 'A';

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