Oracle-DB Effiziente Nutzung von Function-based Indizes Peter Ramm Oktober 2023 

Otto Group Solution Provider (OSP) Gründung: März 1991 Muttergesellschaft: Otto Group Standorte: Dresden, Hamburg, Altenkunstadt, Madrid, Valencia, Malaga, Taipei Anzahl Mitarbeitende: > 500 Geschäftsführer: Dr. Stefan Borsutzky, Norbert Gödicke 

Zur Person Mail: [email protected] Peter Ramm Software Architekt / Teamleiter bei OSP in Dresden > 30 Jahre Historie in IT-Projekten Schwerpunkte: • Entwicklung von OLTP-Systemen auf Basis von Oracle-Datenbanken • Architektur-Beratung bis Trouble-Shooting • Performance-Optimierung bestehender Systeme 

Function-based Indizes in Oracle-DB § Was sind function-based Indizes § Was passiert bei Index-Maintenance § Warum deterministische Funktionen § Anwendungsbeispiel Zugriff auf kleine Anteile großer Tabellen (z.B. Queue-Verarbeitung) § Alternative zu unique Index für nicht deterministische Funktionen 

Was sind function-based Indizes § Indizierung erfolgt auf Basis von Funktionsergebnissen statt nativer Tabellenspalten. § Die Funktionen transformieren i.d.R. Werte der indizierten Tabelle. D.h., sie haben Spalten der indizierten Tabelle als Funktionsparameter. § Erlaubt damit die zielgenaue Indizierung der für eine Abfrage relevanten Bedingungen (WHERE-Klausel) § Die genutzten Funktionen müssen determistisch sein. § Es können sowohl interne als auch benutzerdefinierte Funktionen verwendet werden. § Mehrspaltige Indizes können Funktionen und Spalten der indizierten Tabelle kombinieren. § Anstelle der konkreten Funktionsausdrücke können auch virtuelle Spalten indiziert werden. § Neben Funktionsergebnissen können auch die Ergebnisse von CASE-Ausdrücken indiziert werden 

Was passiert bei Index Maintenance § INSERT: - Suchen des in Sortierfolge passenden Leaf-Blocks entsprechend den indizierten Werten des Records durch Traversieren des B-Baums - Einfügen eines neuen Record an entsprechender Position im Leaf-Block mit den indizierten Werten und Verweis auf Record in Tabelle (ROWID) § DELETE: - Suchen des Records im Index entsprechend den indizierten Werten und der ROWID des Records - Entfernen des gefundenen Records aus dem Leaf-Block des Index § UPDATE: - Finden und Entfernen des Index-Records entsprechend den alten Werten (analog DELETE) - Einsetzen eines neuen Index-Records entsprechend den neuen Werten (analog INSERT) 

Warum deterministische Funktionen § Durch Deklarieren der Funktion mit Schlüsselwort DETERMINISTIC beglaubigt der Ersteller, dass die Funktion für identische Aufrufparameter immer identische Ergebnisse liefert § Ob die deklarierte Funktion wirklich deterministisch ist, wird durch DB nicht geprüft § Nicht deterministisch sind z.B. Funktionen: - mit Abhängigkeit des Ergebnisses von in der Funktion ausgeführten SELECT-Statements - Mit Abhängigkeit von der Systemzeit § Problem nicht wirklich deterministischer Funktionen: - INSERT funktioniert - Bei UPDATE oder DELETE wird der vorhandene Index-Record nicht gefunden -> ORA_08102: index key not found - SELECT liefert unterschiedliche Ergebnisse bei FULL SCAN oder INDEX SCAN, da INDEX SCAN auf Werten zum Zeitpunkt des INSERT basiert, während FULL SCAN auf aktuellem Funktionsergebnis basiert 

Problembeispiel nicht deterministischer Funktionen CREATE TABLE Log(Creation DATE); CREATE OR REPLACE FUNCTION AgeSecs(creation DATE) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN (SYSDATE - creation)*86400; END; / CREATE INDEX Log_Age ON Log(AgeSecs(Creation)); INSERT INTO Log VALUES(SYSDATE); -- Scheitert mit ORA-08102 DELETE FROM LOG; INSERT INTO Log VALUES(SYSDATE); INSERT INTO Log VALUES(SYSDATE); -- Uses FULL TABLE SCAN, age based on current time SELECT AgeSecs(creation), Creation FROM LOG; -- Uses INDEX RANGE SCAN on function-based index, age based on time at creation as stored in index SELECT AgeSecs(creation), Creation FROM LOG WHERE AgeSecs(creation) < 1; 

Anwendungsbeispiel Zugriff auf kleine Anteile großer Tabellen Einsichten unter Nutzung des freien Analyse-Tools „Panorama“ (https://github.com/rammpeter/panorama) Ausgangspunkt: • SQL mit 6000 Ausführungen/Tag und 3 Sekunden Laufzeit • Selektiert je Ausführung im Schnitt 5 Records aus Tabelle mit 27 Mio. Records • Sucht nach wenigen neu hinzugekommenen Records, die noch nicht versendet wurden (Sent=N) • Zwei weitere Filter auf „Event_Context“ und Liste von Werten für „Event_Type“ • Aktuelle Indizierung ist suboptimal (Spalte „Sent“ an letzter Position im Index), Größe des Index ca. 3 GB Alternative 1: Verschieben der Spalte „Sent“ als führende Spalte des Index • Reduktion der Laufzeit auf < 100µs (Faktor 20000++), Größe des Index bleibt vergleichbar (1,5 GB neu) • 4 Buffer-Gets im Index / Execution 

Anwendungsbeispiel Zugriff auf kleine Anteile großer Tabellen Alternative 2: Nutzung eines function-based Index der nur die Records mit Sent=N indiziert § Alle Records mit Sent != N werden nicht indiziert (Indizierter Ausdruck = NULL) § Der Indexwert enthält weitere Attribute außer der Spalte „Sent“. Diese Spalte „Sent“ muss nicht Bestandteil des Index sein, da bereits Index-Wert != NULL implizit „Sent“=N enthält. § Unterstellt, die 5 Bedingungen für „Event-Type“ sind immer fix und der Filter auf „Event_Context“ variiert, dann würde ein optimaler function-based Index so aussehen § CREATE INDEX IX_Min ON Test(CASE WHEN Sent='N' AND Event_Type IN ('DeliveryCreated', 'DeliveryUpdated', 'DeliveryDeleted', 'OrderApproved', 'PurchaseOrderApproved') THEN Event_Context END); § Wird die Filterbedingung im SQL jetzt exakt wie der indizierte Ausdruck formuliert, damit wird der minimale function- based Index nutzbar für die Abfrage § Reduktion der Laufzeit auf < 100µs (Faktor 20000++), Indexgröße = 64K (initial extent, 4% der vorherigen Größe) 1 Buffer-Get im Index / Execution § Für mehrspaltige Indizes müssen alle Spalten das NULL-Verhalten aufweisen, um die Größenreduktion zu realisieren 

Alternative zu unique Index für nicht deterministische Funktionen § Es gab eine Anforderung, Eindeutigkeit einer Tabelle abzusichern inklusive einer zusätzlichen Spalte einer n:1-Relation § Implementiert wurde dies über: - eine Funktion, die die zusätzliche Spalte aus der n:1-Relation per Select liefert - einen unique function-based Index der als weitere Spalte den Return-Wert dieser Funktion enthält § Funktionierte genau so lange, bis sich Daten in der verbundenen Tabelle änderten § Delete in der mit unique Index abgesicherten Tabelle führte danach zu ORA-08102 § Dieser Blog-Post beschreibt eine alternative Lösung für Garantie von Eindeutigkeiten über Tabellengrenzen hinweg - Basis der Lösung sind Compound-Trigger - Beispiel für Optimierung der im Trigger verwendeten SQLs 

Vielen Dank für Ihr Interesse Otto Group Solution Provider (OSP) Dresden GmbH Freiberger Str. 35 | 01067 Dresden T +49 (0)351 49723 0 | F +49 (0)351 49723 119 osp.de