FBI 활용 사례

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

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

-- 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가 발생했다.

-- 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 테이블의 크기가 큰 경우 인덱스의 추가가 어려울 수 있다. 

-- 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 인덱스에 비해 유리하다.

-- 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 인덱스 정보를 조회한 결과다.

-- 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 행이 선택되었습니다.