Skip to Main Content
 
Titelbild Muniqsoft Training

Search Results

Oracle Tipp Text

Function Based Indices - Spezialfälle

Bereich:PL/SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 08.04.2025

Keywords:DBA, PL/SQL

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



Weitere Interessente Artikel zum Thema:


Empfohlene Schulungen zum Thema:
2026.02.18