Die häufigsten Einsatzfälle eines Function Based Index dürften sein:
- eine Suche unabhängig von Groß-/Kleinschreibung zu machen, indem man einen Index auf UPPER(spalte) legt, oder
- normalerweise nicht indizierte - NULL-Werte für die Indizierung zugänglich zu machen durch NVL(spalte, ...).
An dieser Stelle sollen zwei besondere Einsatzmöglichkeiten solcher Indices vorgestellt werden.
ERZWINGUNG SELEKTIVER EINDEUTIGKEIT
Wir hatten schon öfter in Applikationen folgendes Szenario: In Tabellen soll nicht physikalisch gelöscht werden, sondern nur logisch durch Setzen eines Flags. Gleichzeitig soll ein bestimmtes Merkmal (z.B. ein Name) nur innerhalb der aktiven Einträge eindeutig sein, um anhand dieses - in einer Maske angezeigten - Kriteriums suchen zu können. Ein Beispiel zur Demonstration des Problems:
CREATE TABLE my_tab(nr NUMBER, NAME VARCHAR2(100), status CHAR(1));
DECLARE
v_status CHAR (1);
v_name VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 10000
LOOP
IF MOD (i, 1000) = 0
THEN
v_status := 'A';
ELSE
v_status := 'I';
END IF;
v_name := 'DEFAULT_' || TRUNC( i/1000);
INSERT INTO MY_TAB(nr, NAME, status )
VALUES (i, v_name, v_status );
END LOOP;
END;
/
SELECT status, COUNT (DISTINCT NAME) distinct_pro_status,
COUNT (*) gesamt_pro_status,
(SELECT COUNT (*) FROM MY_TAB) gesamt
FROM MY_TAB GROUP BY status;
S DISTINCT_PRO_STATUS GESAMT_PRO_STATUS GESAMT
- ------------------- ----------------- ----------
A 10 10 10000
I 10 9990 10000
Ein "normaler" UNIQUE Index hilft hier nicht weiter. Kein Name ist Unique, und auch die Kombination aus Status und Name ist es nicht:
CREATE UNIQUE INDEX my_tab_idx ON my_tab(NAME, status);
FEHLER in Zeile 1:
ORA-01452: CREATE UNIQUE INDEX nicht ausführbar; doppelte Schlüssel gefunden
Was tun? Ein Ansatz wäre, vor neuen Einträgen zunächst die Anzahl aktiver Datensätze mit diesem Wert zu zählen, und in dem Fall, dass die Anzahl > 0 ist, den Wert abzulehnen. Sinnvoller wäre es aber, wenn die Datenbank selber solche Anforderungen erzwingen würde. Dann können sie in keinem Fall umgangen werden. Hier kann ein Funktionsbasierter Index weiterhelfen. Der Trick dabei ist, dass NULL-Werte nicht indiziert werden:
CREATE UNIQUE INDEX my_tab_idx ON my_tab(CASE status WHEN 'A' THEN NAME END);
-- Alternative:
--CREATE UNIQUE INDEX my_tab_idx ON my_tab(DECODE(status, 'A',NAME));
Index wurde erstellt.
INSERT INTO my_tab (nr, NAME, status )
VALUES (10001, 'DEFAULT_1', 'A' );
FEHLER in Zeile 1:
ORA-00001: Unique Constraint (SCOTT.MY_TAB_IDX) verletzt
INSERT INTO my_tab (nr, NAME, status )
VALUES (10001, 'DEFAULT_1', 'I' );
1 Zeile wurde erstellt.
Ziel erreicht. Die Eindeutigkeit für aktive Datensätze - und nur für aktive Datensätze - wird durch die Datenbank erzwungen.
INDIZIERUNG EINES BESTIMMTEN WERTES
Eine andere Fragestellung: Sie haben in Ihrem Workflow eine Tabelle mit zu erledigenden Aufträgen. Es werden laufend neue Einträge mit dem Status 'WAITING' eingetragen. Ein Job arbeitet die Aufträge ab und setzt dabei den Status z.B. zuerst auf 'IN PROGRESS' und am Ende auf 'DONE'. Sie werden sehr bald sehr viele Einträge haben, wobei in der Spalte "Status" nur drei (oder vier: ERROR im Fehlerfall) Werte stehen. Nur ein sehr geringer Prozentsatz davon wird WAITING sein. Aber die sollen effektiv gefunden werden.
Vielleicht denken Sie jetzt: "Wenige unterschiedliche Werte? Aha - Bitmap Index". Nun, das wäre das schlechteste, was Sie tun können, da die Tabelle laufend geändert wird.
Alternative 2 wäre ein normaler B*Tree-Index mit Histogrammen. Das funktionert, solange Sie beim Status nicht mit Bind-Variablen arbeiten und ausgerechnet bei der ersten Ausführung nach einem anderen Status gesucht haben. Allerdings wird der Index unnnötig groß.
Alternative 3: Sie gehen genauso vor wie im ersten Beispiel beschrieben:
CREATE TABLE tasks(nr NUMBER, TO_DO VARCHAR2(100), status VARCHAR2(20));
DECLARE
v_status VARCHAR2(20);
v_task VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 100000
LOOP
IF MOD (i, 10000) = 0
THEN
v_status := 'WAITING';
ELSE
v_status := 'DONE';
END IF;
v_task := 'Tu was';
INSERT INTO TASKS(nr, TO_DO, status )
VALUES (i, v_task, v_status );
END LOOP;
END;
/
SELECT COUNT (*) gesamt, SUM (DECODE (status, 'WAITING', 1, 0)) wartend
FROM tasks;
GESAMT WARTEND
---------- ----------
100000 10
CREATE INDEX status_idx ON tasks(CASE status WHEN 'WAITING' THEN 1 END);
EXEC dbms_stats.gather_table_stats('SCOTT', 'TASKS', cascade=> true)
Selbstverständlich können Sie hier auch mit DECODE arbeiten, und es ist letztlich unerheblich, welcher Wert GENAU im Index steht. Es könnte genauso gut 'WAITING' selber sein.
Hier ist die Zielsetzung eine andere als oben: Der Zugriff soll beschleunigt werden. Entsprechend muss nun beim SELECT-Befehl darauf geachtet werden, dass der Index auch wirklich verwendet wird. Das heisst, die Funktion muss auch in der WHERE-Klausel stehen:
set autotrace traceonly explain
SELECT to_do FROM tasks WHERE status = 'WAITING';
Ausführungsplan
----------------------------------------------------------
Plan hash value: 41374823
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 86 (4)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TASKS | 1 | 13 | 86 (4)| 00:00:02 |
---------------------------------------------------------------------------
--> kein Indexzugriff
SELECT to_do FROM tasks WHERE (CASE status WHEN 'WAITING' then 1 END) = 1;
Ausführungsplan
----------------------------------------------------------
Plan hash value: 1504895482
-------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time |
-------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 10 | 130 | 11 (0)|00:00:01|
| 1| TABLE ACCESS BY INDEX ROWID|TASKS | 10 | 130 | 11 (0)|00:00:01|
|* 2| INDEX RANGE SCAN |STATUS_IDX| 10 | | 1 (0)|00:00:01|
-------------------------------------------------------------------------------
--> Index wird verwendet
Anmerkung: Aus Platzgründen wurden Leerzeichen aus dem Plan entfernt
Wenn Sie nicht - oder nicht mehr - wissen, wie ein funktionsbasierter Index angelegt wurde, schauen Sie in DBA_IND_EXPRESSIONS nach.
Funktionsbasierte Indices sind erfreulicherweise seit Version 9.2 auch in der Standard Editon von Oracle enthalten; davor waren sie auf die Enterprise Edition beschränkt.
Quelle:
Thomas Kyte, Expert Oracle Database Architecture. 9i and 10g Programming Techniques and Solutions, Apress, 2005