사용자 정의 함수와 FBI

사용자 정의 함수의 성능을 개선하기 위해 FBI(Function Based Index)를 사용하는 경우가 있다. 사용자 정의 함수를 사용한 FBI의 동작을 살펴보자.

테스트를 위해 아래와 같이 테이블과 함수를 생성하자. t1_x1 인덱스는 f1 함수를 사용한 FBI다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 2');

CREATE OR REPLACE FUNCTION f1 (i_val IN NUMBER)
    RETURN NUMBER DETERMINISTIC
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE ('X');
    RETURN i_val + 1;
END;
/

CREATE INDEX t1_x1 ON t1 (c1, f1 (c1));

아래의 2-1, 2-2번 쿼리는 t1_x1 인덱스를 사용했고, f1 함수가 수행되지 않았다. 2-2번 쿼리에서 인덱스로 테이블을 액세스한 경우 인덱스에 저장된 값을 사용한다는 것을 알 수 있다. t1_x1 인덱스를 사용하지 않은 3번 쿼리는 f1 함수가 수행된다. 

-- 2-1
SET SERVEROUTPUT ON

SELECT c1, f1 (c1) AS cf1 FROM t1 WHERE c1 = 1;

C1 C1F
-- ---
 1   2

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

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|*  1 |  INDEX RANGE SCAN| T1_X1 |
----------------------------------

-- 2-2
SELECT c1, c2, f1 (c1) AS cf1 FROM t1 WHERE c1 = 1;

C1 C2 CF1
-- -- ---
 1  1   2

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

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

-- 2-3
SELECT c1, f1 (c1) AS cf1 FROM t1;

C1 CF1
-- ---
 1   2
 2   3

2 행이 선택되었습니다.

X
X

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

FBI의 표현식은 테이블에 가상 칼럼(SYS_NC00003$)으로 생성된다. 가상 칼럼은 물리적으로 저장되지 않는다.

-- 3
SELECT column_name, data_default, hidden_column, virtual_column FROM user_tab_cols WHERE table_name = 'T1';

COLUMN_NAME  DATA_DEFAULT      HIDDEN_COLUMN VIRTUAL_COLUMN
------------ ----------------- ------------- --------------
C1                             NO            NO
C2                             NO            NO
SYS_NC00003$ "TUNA"."F1"("C1") YES           YES

3 행이 선택되었습니다.

블록 덤프를 수행하기 위해 아래와 같이 쿼리를 수행하자. 테이블은 7번 파일, 403번 블록, 인덱스는 7번 파일, 411번 블록에 저장되어 있다.

-- 4-1
SELECT object_id FROM user_objects WHERE object_name  = 'T1_X1';

OBJECT_ID
---------
   277654

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

-- 4-2
SELECT c1
     , sys_nc00003$
     , DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) AS tfno
     , DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) AS tbno
     , DBMS_ROWID.ROWID_RELATIVE_FNO (SYS_OP_LBID (277654, 'L', ROWID)) AS ifno
     , DBMS_ROWID.ROWID_BLOCK_NUMBER (SYS_OP_LBID (277654, 'L', ROWID)) AS ibno
  FROM t1;

C1 SYS_NC00003$ TFNO TBNO IFNO IBNO
-- ------------ ---- ---- ---- ----
 1            2    7  403    7  411
 2            3    7  403    7  411

2 행이 선택되었습니다.

X
X

아래와 같이 블록 덤프를 수행하자. t1 테이블은 f1 (c1) 표현식의 값이 저장되지 않고, t1_x1 인덱스는 표현식의 결과가 저장된 것을 확인할 수 있다. 복호화 함수로 FBI를 생성하면 보안 이슈가 발생할 수 있는 점에 주의할 필요가 있겠다.

-- 5-1
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 403;

tab 0, row 0, @0x1f77
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02 --> 1
col  1: [ 2]  c1 02 --> 1
tab 0, row 1, @0x1f6e
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03 --> 2
col  1: [ 2]  c1 03 --> 2

-- 5-2
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 411;

row#0[8021] flag: -------, lock: 0, len=15
col 0; len 2; (2):  c1 02             --> 1 : c1
col 1; len 2; (2):  c1 03             --> 2 : f1 (c1)
col 2; len 6; (6):  01 c0 01 93 00 00 --> ROWID
row#1[8006] flag: -------, lock: 0, len=15
col 0; len 2; (2):  c1 03             --> 2 : c1
col 1; len 2; (2):  c1 04             --> 3 : f1 (c1)
col 2; len 6; (6):  01 c0 01 93 00 01 --> ROWID