www.symax.de

von Thomas Geisel (Kommentare: 0)

Effizienzsteigerung bei komplexen Datenbankabfragen

So nutzen Sie Oracle´s Object Types um komplexe „SQL Select-Abfragen“ zu beschleunigen und besser zu strukturieren

Inhalt

Einleitung

Bei der Arbeit mit größeren Datenbankanwendungen werden in Abfragen häufig JOINs, UNIONs, Sub-SELECTs, Nested-SELECTs, usw. verwendet. Das SELECT-Statement wird immer „größer“ und unübersichtlicher, wodurch schnell eine grenzwertige Komplexität erreicht wird. Das erschwert die Wartung und Erweiterbarkeit und erhöht die Gefahr, dass ein Ausführungsplan unerwartet "kippt". Abfragen, die gestern noch vertretbare Antwortzeiten hatten, dauern sodann eine gefühlte Ewigkeit.

Seit Version 8i stellt Oracle in PL/SQL objektorientierte Programmierelemente zur Verfügung. Damit können komplexe Abfrage in kleinere, wesentlich besser strukturierte Teile zerlegt werden und sind somit einfacher zu warten. Diese „simpleren“ SELECTs können gezielt optimiert werden, sodass sie von der Datenbank in Summe meist schneller und ressourcenschonender verarbeitet werden, als ein riesiges SELECT-Statement.

Nachdem die hier beschrieben Vorarbeiten geleistet sind, ist es sehr einfach ein Session-bezogenes oder auch Session-übergreifendes Caching zu implementieren. Dadurch wird die Ausführung wiederkehrender und lang andauernder Abfragen drastisch beschleunigt. Resultate, auf die man zuvor länger warten musste, werden binnen zehntel-Sekunden geliefert. 

Um Ihnen diese Techniken näher zu bringen, nutzt dieser Artikel ein exemplarisches Beispiel, dessen Sinnhaftigkeit hier nicht diskutiert werden soll. Es dient lediglich dazu, Ihnen die nötigen Techniken zu vermitteln, damit Sie diese in einer realen und vermutlich erheblich komplexeren Umgebung eigenständig anwenden können.

Und schließlich noch der Hinweis, dass die hier vorgestellte Technik der Table-Functions nicht die Besonderheiten der Pipelined-Table-Functions abdeckt. Auf die Unterschiede wird aber unten kurz eingegangen.

Vorarbeiten und Vorbemerkungen

In diesem Artikel werden die Daten des Oracle-Sample-Schemas „HR“ genutzt und die Aktionen als User „HR“ ausgeführt. Daher ist es nötig, diesen User – sofern noch nicht geschehen – freizuschalten und ihm ein Passwort zuzuweisen:

sqlplus sys/<pw>@<db> as sysdba
ALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;
exit;

Um die weiter unten aufgeführten Skripte direkt ausführen zu können, führen Sie am besten direkt eine Anmeldung als HR durch:

sqlplus hr/hr@<db>

Aufgabenstellung

Basierend auf den Daten im HR-Schema soll eine Auswertung erstellt werden, welche die Gehaltsunterschiede zwischen den Regionen in Abhängigkeit der Betriebszugehörigkeit aufdeckt.

Die Rückgabemenge enthält demzufolge die Spalten zur Region, der Betriebsangehörigkeit (in Jahren), dem durchschnittlichen Einkommen in diesem Segment sowie die Angaben, wie dieses im Vergleich zu der Region mit den bestbezahlten, bzw. am schlechtesten bezahlten Jobs steht.

Diese Aufgabe kann natürlich auch klassisch mit SQL, ggfs. unter Einsatz der Statistic-Functions gelöst und dauerhaft als View abgelegt werden und bei den Datenmengen des HR-Schemas wird dies auch sicher zu flotten Antwortzeiten führen.

Aber die hier vorgestellte Technik dient dazu, Anleihen zu nehmen, um im realen Einsatz komplexe Abfragen, die sich eventuell in Views mit seitenlangen SELECT-Statements verbergen, zu entwirren, besser wartbar zu machen und i.d.R. auch deutlich zu beschleunigen.

Schritt 1: Ein Objekt für die Pufferung einer Entität erzeugen

So wie eine View eine bestimmte vordefinierte Anzahl von Spalten mit unterschiedlichen Datentypen zurückliefert, müssen wir auch hier vorab festlegen, welche Spalten mit welchen Datentypen der Aufrufer erwarten kann.

Dazu erzeugen wir den Object-Type T_RegSalStat:

CREATE OR REPLACE TYPE T_RegSalStat AS OBJECT (
   Region_ID      INTEGER,
   Region_Name    VARCHAR2(25),
   Length_of_Service INTEGER,
   Avg_Salary     FLOAT,
   Above_MinSal   FLOAT,
   Below_MaxSal   FLOAT
   );
/

Dies wirkt zunächst sehr ähnlich einer Record-Definition, aber da es sich hierbei um ein Objekt handelt, lässt sich damit deutlich mehr anfangen als mit einem Record.

Eine Instanz dieses Objektes ließe sich bereits mit folgendem Skript erzeugen:

DECLARE
   o T_RegSalStat;
BEGIN
   o := T_RegSalStat(NULL,NULL,NULL,NULL,NULL,NULL);
END;
/

Bekanntermaßen können Objekte vererbt und erweitert werden, führen Polymorphie ein, können Klassen- und Instanzmethoden enthalten, usw.

In unserem Beispiel wollen wir das Objekt so erweitern, dass es:

  • später vererb- und damit erweiterbar ist,
  • mind. einen Konstruktor enthält, der uns von der lästigen Initialwert-Auflistung (siehe oben 6x NULL) befreit und
  • eine Routine enthält, welche die Daten auf Initialwerte zurücksetzt.

Also wird das oben stehende Script wie folgt erweitert:

CREATE OR REPLACE TYPE T_RegSalStat AS OBJECT (
   Region_ID      INTEGER,
   Region_Name    VARCHAR2(25),
   Length_of_Service INTEGER,
   Avg_Salary     FLOAT,
   Above_MinSal   FLOAT,
   Below_MaxSal   FLOAT,
   
   CONSTRUCTOR FUNCTION T_RegSalStat 
      RETURN SELF AS RESULT,
      
   MEMBER PROCEDURE reset
   );
/

TYPEs werden, ähnlich wie PACKAGEs, in zwei Abschnitten definiert:

  1. Dem Interface- oder Declaration-Teil und
  2. Dem Implementation- oder Body-Teil.

Im Interface-Teil wird festgelegt, wie sich das Objekt nach außen verhält, während die konkrete Umsetzung dazu erst im Implementation-Teil stattfindet.

Dadurch können beliebig Codeänderungen und -optimierungen an der Implementierung vorgenommen werden, ohne dass aufrufende Programmeinheiten invalid werden.

Allerdings muss alles, was im Interface-Teil auch "versprochen" wurde, im Implementation-Teil auch umgesetzt werden.

Im obigen Fall stehen wir nun noch in der Verpflichtung, den Constructor und die Member-Procedure reset zu implementieren:

CREATE OR REPLACE TYPE BODY T_RegSalStat AS
  
   CONSTRUCTOR FUNCTION T_RegSalStat 
      RETURN SELF AS RESULT
      AS
      BEGIN
         reset;
         RETURN;
      END;  

   MEMBER PROCEDURE reset
   AS
   BEGIN
      SELF.Region_ID := NULL;
      SELF.Region_Name := NULL;
      SELF.Length_of_Service := NULL;
      SELF.Avg_Salary := NULL;
      SELF.Above_MinSal := NULL;
      SELF.Below_MaxSal := NULL;      
   END;
   
END;
/

Der parameterlose Konstruktor ruft mit der zu dem Zeitpunkt bereits bestehenden Objektinstanz implizit die Methode reset auf, um die Member-Variablen auf ihre Initialwerte zu setzen und liefert das so initialisierte Objekt zurück.

Schritt 2: Eine Collection für eine beliebige Anzahl von Entitäten

Der oben in Schritt 1 definierte TYPE kann genau einen Eintrag unserer gewünschten Rückgabemenge enthalten. Als Speicherpuffer für eine beliebige Anzahl von Einträgen muss daher eine Collection erzeugt werden, die sich ähnlich einer LIST in anderen OO-Sprachen verhält:

CREATE OR REPLACE TYPE T_RegSalStat_List AS TABLE OF T_RegSalStat;
/

In eine solche Collection können neue Elemente aufgenommen, einzelne oder alle Elemente gelöscht werden oder auch die aktuelle Anzahl der enthaltenen Einträge ermittelt und über diese iteriert werden. Dazu stehen von Hause aus die Methoden FIRST, NEXT, EXTEND, COUNT usw. zur Verfügung.

Letztlich kann die Collection wie eine TABLE oder VIEW in SELECT-Statements verwendet werden, sofern man sie in den Term TABLE(<collection>) kapselt.

Schritt 3: Die Table-Function für die eigentliche Arbeit erstellen

In der Table-Function werden nun die Daten selektiert, die für die statistische Auswertung nötig sind und pro Gruppierungselement "Region / Length_of_Service" in eine T_RegSalStat-Instanz gepackt und in die Rückgabe-Collection eingehängt.

Abschließend wird für jede Zeile der Collection noch die prozentuale Abweichung zur besten/schlechtesten Region in diesem Segment ermittelt und das Gesamtergebnis zurückgegeben:

CREATE OR REPLACE FUNCTION get_RegSalStats
   RETURN T_RegSalStat_List
AS
   o T_RegSalStat;
   res T_RegSalStat_List;
   minSal FLOAT;
   maxSal FLOAT;
   i INTEGER;
BEGIN
   -- Collection initialisieren
   res := T_RegSalStat_List();
   
   -- Grunddaten für die Auswertung ziehen
   FOR dat IN
      (
         WITH stat AS (
            SELECT r.region_id, r.region_name, extract(YEAR FROM sysdate) - EXTRACT(YEAR FROM e.hire_date) +1 length_of_service, e.salary
              FROM regions r
              JOIN countries c ON c.region_id = r.region_id
              JOIN locations l ON l.country_id = c.country_id
              JOIN departments d ON d.location_id = l.location_id
              JOIN employees e ON e.department_id = d.department_id
            )
         SELECT s.region_id, s.region_name, s.length_of_service, AVG(s.salary) Avg_Salary
           FROM stat s
          GROUP BY s.region_id, s.region_name, s.length_of_service
          ORDER BY s.region_name, s.length_of_service
      )
   LOOP
      -- neue Instanz von T_RegSalStat erstellen
      o := T_RegSalStat;
      o.Region_ID := dat.region_id;
      o.Region_Name := dat.region_name;
      o.Length_of_Service := dat.length_of_service;
      o.Avg_Salary := dat.Avg_Salary;
      -- Collection erweitern
      res.extend();
      -- und die befüllte Objektinstanz in die Liste einhängen
      res(res.last) := o;
   END LOOP;
   
   -- durch die Collection itererieren und die Abweichung zu anderen Regions ermitteln
   i := res.first;
   WHILE (i IS NOT NULL)
   LOOP
      -- das geringste Gehalt in schlechter bezahlten Regions ermitteln
      SELECT MIN(x.avg_salary)
        INTO minSal
        FROM TABLE(res) x
       WHERE x.region_id <> res(i).region_id
         AND x.length_of_service = res(i).length_of_service
         AND x.avg_salary < res(i).avg_salary;
      -- und die prozentuale Abweichung dazu merken
      IF (nvl(minSal,0) = 0) THEN
         res(i).above_minSal := 0;
      ELSE
         res(i).above_minSal := (res(i).avg_salary / minSal - 1) * 100;
      END IF;
      -- das höchste Gehalt in besser bezahlten Regionen ermitteln
      SELECT MAX(x.avg_salary)
        INTO maxSal
        FROM TABLE(res) x
       WHERE x.region_id <> res(i).region_id
         AND x.length_of_service = res(i).length_of_service
         AND x.avg_salary > res(i).avg_salary;
      -- und die prozentuale Abweichung dazu merken
      IF (nvl(maxSal,0) = 0) THEN
         res(i).below_maxSal := 0;
      ELSE
         res(i).below_maxSal := (res(i).avg_salary / maxSal - 1) * 100;
      END IF;
      -- Indexer auf den nächsten Eintrag der Collection setzen
      i := res.next(i);
   END LOOP;
   
   
   -- gefüllte Collection zurückliefern
   RETURN res;
END;
/

Statt wie oben nur die Rückgabe-Collection auszuwerten, können in der Table-Function natürlich beliebig komplexe Zusatzabfragen und Berechnungen bis hin zur Berücksichtigung von Berechtigungssystemen ("darf der User überhaupt bestimmte Werte sehen?") eingebaut werden.

 

In einfachen Beispielen wie diesen, wo die initiale Befüllung der Collection (hier in der oberen FOR-Schleife) keine besonderen Aktionen auf Zeilenebene erfordert, kann sowohl der Code als auch die Ausführungsdauer ein Stück verkürzt werden, in dem BULK-COLLECT angewendet wird.
Allerdings muss dann für jedes Attribut der zu erzeugenden Objektinstanzen auch ein Wert aus dem SELECT zurückkommen. Daher werden hier noch 2 Füllspalten mit NULL angehängt, um für alle Attribute des Objektes eine Spalte zurückzugeben:

…
   WITH stat AS (
      SELECT r.region_id, r.region_name, extract(YEAR FROM sysdate) - EXTRACT(YEAR FROM e.hire_date) +1 length_of_service, e.salary
        FROM regions r
        JOIN countries c ON c.region_id = r.region_id
        JOIN locations l ON l.country_id = c.country_id
        JOIN departments d ON d.location_id = l.location_id
        JOIN employees e ON e.department_id = d.department_id
      )
   SELECT T_RegSalStat(s.region_id, s.region_name, s.length_of_service, AVG(s.salary), NULL, NULL)
     BULK COLLECT INTO res
     FROM stat s
    GROUP BY s.region_id, s.region_name, s.length_of_service
    ORDER BY s.region_name, s.length_of_service;
…

Das geht zwar etwas schneller, aber man verliert dadurch auch ein Stück Flexibilität.

Das dargestellte Konzept ermöglicht nicht nur, dass u.U. riesige SELECT-Statements nun in kleine, überschaubare und i.d.R. performantere (weil besser auf Indizes anpassbare) Einzelabfragen zerlegt werden können, sondern es ist natürlich auch möglich, durch (ggfs. optionale) Function-Argumente auch ein cleveres Filtering einzubauen.
Und selbstverständlich klappt jetzt auch das gezielte Debugging bei sonderbaren Ergebnissen und Fehlverhalten der Function, bzw. wird ein Profiling der einzelnen Schritte der Function möglich.

Schritt 4: Die neue Table-Function anwenden

Der Aufrufer der Table-Function kann diese genau wie eine TABLE oder VIEW verwenden:

SELECT * FROM TABLE(get_RegSalStats);

und bei Bedarf auch ganz normal JOINen:

SELECT s.Region_ID, s.Region_Name, s.Length_of_Service, s.Avg_Salary, s.Above_MinSal, s.Below_MaxSal, COUNT(*) countries
  FROM TABLE(get_RegSalStats) s
  JOIN countries c ON c.region_id = s.region_id
 GROUP BY s.Region_ID, s.Region_Name, s.Length_of_Service, s.Avg_Salary, s.Above_MinSal, s.Below_MaxSal
 ORDER BY s.region_name, s.length_of_service;

Und hätte die Function Argumente, würde das auch einfach funktionieren:

SELECT * FROM TABLE(get_RegSalStats(arg1, arg2, …);

Ausblick: Caching und Pipelined-Table-Functions

Auch wenn mit obiger Technik Abfragen z.T. erheblich beschleunigt werden können, kommt es doch immer wieder vor, dass auch mit Table-Functions die Antwortzeiten nicht mehr im Bereich des erträglichen liegen.

Werden jedoch überwiegend weitgehend statische Daten abgefragt, so bietet es sich an, einmal eingesammelte Daten in einen Cache zu speichern.

Dazu wird ein Schlüsselwert benötigt, der die Abfrage eindeutig kennzeichnet (z.B. die Jahreszahl, die Department_Id, oder ähnliches) und eine Tabelle, die neben dem Schlüssel noch einen NESTED-TABLE von eben genau dem jeweiligen Collection-Type enthält.

Eine Collection mit dem Zwischen- oder Endergebnis kann dann unter dem Schlüssel komplett in die Nested-Table geschrieben und bei einem weiteren Aufruf der Function auch direkt dort wieder ausgelesen werden.

Dann ist noch zu erwähnen, dass die oben Technik komplett im Speicher abläuft:  die Objekte werden im Speicher erzeugt und in die im Speicher verwaltete Liste geschoben.

Bei Abfragen, die hunderttausende oder noch mehr Datensätze zurückliefern bedeutet dies, dass das Ergebnis einerseits erst dann zurückkommt, wenn die Liste komplett gefüllt ist und andererseits, dass der Speicher extrem belastet wird.

Eine Alternative sind dazu Pipelined-Table-Functions, welche immer dann, wenn eine einzelne Rückgabezeile komplett fertig zusammengestellt ist, diese auch schon zurückliefern.

Das Ergebnis sind weniger Speicherlast, frühere Rückmeldung mit den ersten Ergebnissen und bessere Parallelisierbarkeit, aber andererseits steht die Collection in der Function selbst dann auch nicht als Ganzes wie oben für statistische Auswertungen zur Verfügung.

Zurück

Einen Kommentar schreiben