Skip to main content
 

Function Based Index

I 'Function Based Indexes' (FBIs) vengono utilizzati per creare indici basati non su un set di colonne appartenenti ad una tabella, ma sul risultato di una stored function applicata alle colonne di una tabella.

Vengono usati quando ci si trova di fronte la necessità di scrivere select filtrate secondo i risultati di una stored function, e non ci si possono permettere i tempi di una inevitabile ACCESS FULL da parte dell'ottimizzatore Oracle.

Sono necessari i seguenti requisiti per attivarle su database Oracle (da 8i in avanti):

  1. Parametro query_rewrite_enabled = true 
    • alter session/system set query_rewrite_enabled = true;
  2. Parametro query_rewrite_integrity = trusted 
    • alter system set query_rewrite_integrity = trusted;
  3. La function di database sulla quale è basato l'FBI deve essere di tipo deterministic. Significa che, a parità di parametri in input, la function restituirà sempre lo stesso risultato in output. Cioè permette ad Oracle di costruire l'indice e di mantenerlo coerente nel tempo.
     
    • create or replace 
       function function_name(col1 in varchar2, col2 in date) 
       return varchar2 deterministic is ....
  4. A questo punto è possibile creare l'indice in un modo del tutto simile a quelli classici: 
    • create index table_name_idx on table_name(function_name(col1, col2));
  5. La select, per poter usare l'indice, deve passare alla function esattamente le stesse colonne presenti nella definizione dell'FBI, inoltre potrebbe essere necessario forzare Oracle ad usare l'indice tramite un hint, soprattutto se l'ottimizzatore è forzato a RULE. 
    • select /*+ index(table_name, table_name_idx) */ * 
       from table_name 
       where function_name(col1, col2) = 'ABC';