Een van de vele nieuwe functies in Oracle 8i is de Function-Based Index . Dit maakt het mogelijk voor de DBA om indexen op functions of expressions te creëren. Deze functions kunnen door de gebruiker gegenereerde pl/sql functions, standaard SQL functions (non-aggregate only) of zelfs een C callout zijn.

Een klassiek probleem dat de DBA tegenkomt in SQL Tuning is hoe queries te tunen die function calls aanroepen in de where clause die vervolgens resulteren in het aanmaken van indexen op deze kolommen die niet worden gebruikt.

Voorbeeld

Standaard B-Tree index op SURNAME met cost based optimizer

create index non_fbi on sale_contacts (surname); 
analyze index non_fbi compute statistics; 
analyze table sale_contacts compute statistics; 
SELECT count (*) FROM sale_contacts
WHERE UPPER(surname) = 'ELLISON';
Executie Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=17) 
1 0 SORT (AGGREGATE) 
2 1 TABLE ACCESS (FULL) OF 'SALES_CONTACTS' (Cost=3 Card=16 Bytes=272) 

Nu gebruiken we een function based index

create index fbi on sale_contacts (UPPER(surname));
analyze index fbi compute statistics;
analyze table sale_contacts compute statistics;
SELECT count(*) FROM sale_contacts
WHERE UPPER(surname) = 'ELLISON';

Executie Plan
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17) 
1 0 SORT (AGGREGATE) 
2 1 INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=2 Card=381 Bytes=6477) 

De function-based index heeft de optimizer geforceerd om een index range scan (geeft 0 of meer rowids terug) op de surname kolom in plaats van een full table scan (non-index lookup). Optimale performance varieert afhankelijk van tabelgrootte, uniekheid en selectiviteit van kolommen, het gebruik van fast full table scans etc. Daarom is het altijd handig om beide methoden uit te proberen om zo’n optimaal mogelijke performance op de database te bewerkstelligen. Het is belangrijk te onthouden dat de function-based B*Tree index de expression niet in de index opslaat maar gebruik maakt van een “expression tree”. De optimizer doet een expression matching door de expression van het sql-statement te vergelijken met de expression-tree waarden in de function-based index. Deze vergelijking is Case sensitive (negeert spaties) en dus moet uw function-based index expression matchen met de in het SQL-statement gebruikte where clauses.

Init.ora Parameters

De volgende parameter moeten gezet worden in je parameter file:

QUERY_REWRITE_INTEGRITY = TRUSTED
QUERY_REWRITE_ENABLED = TRUE
COMPATIBLE = 8.1.0.0.0 (of hoger)

Grants Privileges om function-based indexes te creeren moeten gegrant worden via de privilges CREATE INDEX en QUERY REWRITE, of anders CREATE AN Y INDEX enGLOBAL QUERY REWRITE. De eigenaar van de index moet EXECUTE rechten hebben op de functie die via de index word gebruikt. Als execute rechten worden ingenomen zal de function-based index worden “disabled”. Disabled Indexes Indien de function-based index een status "disabled" heeft, kan de DBA een van de volgende acties uitvoeren:

• drop en create de index (let op wat de huidige settings zijn)
• alter index enable, alleen voor function-based indexes, gebruik ook disable keyword indien nodig
• alter index unusable.

Queries op DISABLED index als de optimizer ervoor kiest om de index te gebruiken.

Een voorbeeld ORA error:

ERROR at line 1: ORA-30554: function-based index MYUSER.FBI is disabled.

Alle DML acties op een DISABLED index gaan ook fout tenzij de index de index is ook gemarkeerd UNUSABLE en de initialization parameter SKIP_UNUSABLE_INDEXES op true staat.

Nog een paar voorbeelden

CREATE INDEX expression_ndx 
ON mytable ((mycola + mycolc) * mycolb); 

SELECT mycolc FROM mytable 
WHERE (mycola + mycolc) * mycolb <= 256;

CREATE INDEX example_ndx 
ON myexample (mycola, UPPER(mycolb), mycolc); SELECT mycolc FROM myexample 
WHERE mycola = 55 AND UPPER(mycolb) = 'JONES'; 

Voorwaarden en Regels Samenvatting

De volgende beperkingen hebben betrekking op function based indexes. Je mag niet indexeren op:

• LOB columns
• REF
• Nested table column
• Objects types with any of the above data types.

Function-based indexes moeten altijd aan de volgende voorwaarden voldoen:

• Alleen Cost Based optimizer, moet statistics genereren nadat the index is gemaakt
• Er kunnen geen NULL waarden in voorkomen (function onder geen omstandigheid met een NULL terugkomen)
• Als een user defined pl/sql routine word gebruikt voor de function-based index, en deze is invalid, dan zal de index op “disabled” komen te staan.
• Functions moeten deterministisch zijn (altijd terugkomen met de zelfde waarde voor een bekende input)
• De index eigenaar moet "execute" rechten hebben op functies die gebruikt worden in de function-based index. Revoke van het privilege zorgt ervoor dat de index "disabled" word.
• Mogen alleen een B-Tree en Bitmap index type hebben.
• Kunnen geen expressions gebruiken die zijn gebaseerd op “aggregate functions”, bijv. SUM, AVG etc.
• Om een finction-based index als enabled aan te merken, moet de gebruikte functie valid zijn, deterministisch zijn en de functie moet matchen met hoe deze bij creatie was