FBI 활용 사례

이번 글에서는 FBI(Function-Based Index)의 활용 사례를 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자. c2 열의 값은 대부분 N이다.

1 2 3 4 5 6 7 8

-- 1 DROP TABLE t1 PURGE;   CREATE TABLE t1 AS SELECT ROWNUM AS c1      , CASE WHEN ROWNUM IN (1, 1000000) THEN 'Y' ELSE 'N' END AS c2      , LPAD ('X', 100, 'X') AS c3   FROM XMLTABLE ('1 to 1000000');

아래가 성능 개선이 필요한 쿼리다. t1 테이블을 전체 스캔하여 15,629개의 블록 I/O가 발생했다.

1 2 3 4 5 6 7 8 9 10

-- 2 SELECT MAX (c1) FROM t1 WHERE c2 = 'Y';   --------------------------------------------------------------- | Id  | Operation          | Name | Starts | A-Rows | Buffers | --------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |      1 |   15629 | |   1 |  SORT AGGREGATE    |      |      1 |      1 |   15629 | |*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |   15629 | ---------------------------------------------------------------

간단한 해법은 아래처럼 t1_x1 인덱스를 생성하는 것이다. 블록 I/O가 3으로 감소한 것을 확인할 수 있다. 하지만 t1 테이블의 크기가 큰 경우 인덱스의 추가가 어려울 수 있다. 

1 2 3 4 5 6 7 8 9 10 11 12 13

-- 3 CREATE INDEX t1_x1 ON t1 (c2, c1);   SELECT MAX (c1) FROM t1 WHERE c2 = 'Y';   -------------------------------------------------------------------------- | Id  | Operation                    | Name  | Starts | A-Rows | Buffers | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |      1 |      1 |       3 | |   1 |  SORT AGGREGATE              |       |      1 |      1 |       3 | |   2 |   FIRST ROW                  |       |      1 |      1 |       3 | |*  3 |    INDEX RANGE SCAN (MIN/MAX)| T1_X1 |      1 |      1 |       3 | --------------------------------------------------------------------------

이런 경우 아래와 같이 FBI를 활용할 수 있다. t1_x2 인덱스에는 c2가 Y인 행만 저장되므로 관리와 성능 측면에서 t1_x1 인덱스에 비해 유리하다.

1 2 3 4 5 6 7 8 9 10 11 12

-- 4 CREATE INDEX t1_x2 ON t1 (DECODE (c2, 'Y', c1));   SELECT MAX (DECODE (c2, 'Y', c1)) FROM t1;   ------------------------------------------------------------------------ | Id  | Operation                  | Name  | Starts | A-Rows | Buffers | ------------------------------------------------------------------------ |   0 | SELECT STATEMENT           |       |      1 |      1 |       1 | |   1 |  SORT AGGREGATE            |       |      1 |      1 |       1 | |   2 |   INDEX FULL SCAN (MIN/MAX)| T1_X2 |      1 |      1 |       1 | ------------------------------------------------------------------------

아래는 t1_x1, t1_x2 인덱스 정보를 조회한 결과다.

1 2 3 4 5 6 7 8 9 10 11

-- 5 SELECT index_name, index_type, blevel, leaf_blocks   FROM user_indexes  WHERE table_name = 'T1';   INDEX_NAME INDEX_TYPE            BLEVEL LEAF_BLOCKS ---------- --------------------- ------ ----------- T1_X1      NORMAL                     2        2505 T1_X2      FUNCTION-BASED NORMAL      0           1   2 행이 선택되었습니다.