* SQL*LOADER direct path load can be used with function-based indexes .


To use a function-based index:

The following initialization parameters must be defined:

QUERY_REWRITE_INTEGRITY must be set to TRUSTED
QUERY_REWRITE_ENABLED must be set to TRUE
COMPATIBLE must be set to 8.1.0.0.0 or a greater value 

FBI can be created with QUERY_REWRITE_INTEGRITY set to ENFORCED, but it will not
be used during query.



SQL> select * from tyu;

        C1
----------
         0
         1
         2

SQL> show parameters rewrite

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      ENFORCED

SQL> create or replace function func2_(c1 number) return number
  2  deterministic
  3  as
  4  x number;
  5  begin
  6  dbms_application_info.read_client_info(x);
  7  dbms_application_info.set_client_info(x+1);
  8  return c1+10;
  9  end;
 10  /

Function created.

SQL> create index func2_idx on tyu(func2_(c1));

Index created.

SQL> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> set autotrace on explain

SQL> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> select /*+ index(tyu func2_idx) */ func2_(c1) from tyu where func2_(c1)>0;

FUNC2_(C1)
----------
        10
        11
        12


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'TYU' (Cost=2 Card=1 Bytes=2)


-- See that funcion func2_ was executed 2*(rows in table tyu)

SQL> declare
  2  x number;
  3  begin
  4  dbms_application_info.read_client_info(x);
  5  dbms_output.put_line(x);
  6  end;
  7  /
6

PL/SQL procedure successfully completed.

SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

SQL> select /*+ index(tyu func2_idx) */ func2_(c1) from tyu where func2_(c1)>0;

FUNC2_(C1)
----------
        10
        11
        12


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
   1    0   INDEX (RANGE SCAN) OF 'FUNC2_IDX' (NON-UNIQUE) (Cost=2 Car
          d=1 Bytes=2)



-- Note that function was not even executed inthis case, when the index was used.
SQL> declare
  2  x number;
  3  begin
  4  dbms_application_info.read_client_info(x);
  5  dbms_output.put_line(x);
  6  end;
  7  /
6

PL/SQL procedure successfully completed.

SQL>