Header Oracle SQL Model Clause

Oracle SQL Model Clause

Theorie und Aufbau

Die Oracle Model Clause ist seit Version 10g verfügbar, ihre teils hohe Komplexität und die dadurch bedingte anfangs recht steile Lernkurve macht sie aber trotzdem zu einem Nischen-Feature. Im Prinzip kann man mit diesem Feature einzelne Zellen direkt ansprechen und sehr gezielt differenzierte Berechnungen anstellen. Da man mit diesem Feature allerdings auch ein enorm mächtiges Werkzeug zur Verfügung hat, soll hier im Folgenden ein kurzer Einblick gegeben werden, wie man sich langsam an die ersten Anwendungsfälle wagen kann, ohne von dem schieren Umfang and Möglichkeiten erschlagen zu werden. Dabei wird Information aus der offiziellen Oracle Dokumentation zusammengefasst und mittels anschaulicher Beispiele in praktischer Umsetzung verdeutlicht.

Lt. Doku (https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6) ist der grundlegende Aufbau der gesamten Model Clause wie folgt:

Abbildung 1: Grundlegender Aufbau SELECT

Abbildung 1: Grundlegender Aufbau SELECT

Hier sieht man nach der Group By Clause die Model Clause. Diese ist wiederum wie folgt aufgebaut:

Abbildung 2: model_clause

Abbildung 2: model_clause

Hieran sieht man bereits, dass lediglich die Komponente main_model verpflichtend ist. Um den Einstieg so einfach wie möglich zu halten, betrachten wir in diesem Artikel auch nur das main_model:

Abbildung 3: main_model

Abbildung 3: main_model

Wir betrachten auch hier nur die verpflichtenden Aspekte, die model_column_clauses und die model_rules_clause:

Abbildung 4: model_column_clauses

Abbildung 4: model_column_clauses

Die Model Column Clause definiert die genutzten Spalten und wie die Spalten verwendet werden. Dabei gibt es 3 Gruppen:

PARTITION BY:

Wie auch bei analytischen Funktionen kann man die Datenmenge anhand von beliebigen Spalten (und theoretisch auch Ausdrücken wie Funktionen) in Gruppen aufteilen (=partitionieren). Im Beispiel am Ende wird darauf noch genauer eingegangen. Dieser Aspekt ist optional.

DIMENSION BY:

Die Spalten der Dimension identifizieren eindeutig eine Zeile innerhalb einer Parition (falls vorhanden). Man sieht an den Keywords bereits, dass das ganze Konzept aus dem Analytics Bereich kommt.

MEASURES:

Hier werden die tatsächlichen Spalten definiert auf denen Berechnungen durchgeführt werden.

Abbildung 5: model_rules_clause

Abbildung 5: model_rules_clause

In der Model Rules Clause werden die tatsächlichen Berechnungen definiert, die im Prinzip Zuweisungen mit einer linken Seite (Ziel der Zuweisung) und einer rechten Seite (Wert der zugewiesen wird) bestehen. Der obere Bereich der Abbildung 5 ist komplett optional, daher betrachten wir erneut lediglich den unteren Bereich. Der wichtige Punkt hierbei ist cell_assignement.

Abbildung 6: cell_assignement

Abbildung 6: cell_assignement

Wir werden uns vorerst mal dem einfachsten Fall widmen, der direkten Identifizierung einer oder mehrerer Zellen. Wie oben bereits beschrieben, definiert die Gesamtheit der Spalten welche als Dimensionen definiert wurden eindeutig eine Zeile (innerhalb einer Partition falls vorhanden). Damit wird eine Zelle oder eine Menge an Zellen eindeutig definiert, indem die Measure Spalte definiert wird und für die Dimensionsspalten Werte angegeben werden (mehrere Zellen können angegeben werden indem beispielsweise Wildcards genutzt werden). Zur Veranschaulichung beginnen wird direkt mit einem praktischen Beispiel.

Praktisches Beispiel

Die Basis für unser praktisches Beispiel ist die Tabelle Schulnoten. Erstellskript und Testdaten können am Ende des Blogeintrags heruntergeladen werden. Der Aufbau ist wie folgt:

create table SCHULNOTEN
(
  schulstufe     NUMBER,
  schueler       VARCHAR2(255),
  schuelernummer NUMBER,
  klasse         VARCHAR2(1),
  jahr           NUMBER,
  fach           VARCHAR2(30),
  note           NUMBER
)

Die Tabelle beinhaltet Schulnoten für Schüler in einer sehr vereinfachten Form. Die Testdaten beinhalten die Daten von jeweils 16 Schülern einer 3. Klasse für 3 Schulstufen sowie zwei 2. Klassen mit je 2 Schulstufen. Auf Basis dieser Daten sollen nun diverse Auswertungen durchgeführt werden. Starten wir mit einer simplen Abfrage: Für den Schüler mit der Nummer 1, für das Fach „Deutsch“, für die 4. Schulstufe im Jahr 2021 soll die voraussichtliche Note berechnet werden als gerundeter Durchschnittswert der Vorjahre. Natürlich kann man diese Berechnung auch über Aggregatsfunktionen berechnen, aber es dient als simpler Einstieg:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0)); 

Hier sehen wir uns die einzelnen Komponenten an:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'

Das ist die grundlegende Abfrage. Wenn die Model Clause verwendet wird, müssen ALLE Spalten, die hier abgefragt werden als PARTITION, DIMENSION oder MEASURE deklariert werden. Weiters müssen hier bei Verwendung von Alias-Bezeichnungen diese angegeben werden. Das bedeutet, dass z.B. folgende Statements nicht funktionieren werden (der fehlerhafte Teil ist jeweils fett markiert):

select schulstufe, klasse, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Die Spalte Klasse wird hier in keiner der drei Kategorien angeführt und führt daher zu einem ORA-32614: unzulässiger MODEL SELECT Ausdruck.

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler name, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Hier wird in den Dimensionen der Spalte SCHUELER der Alias NAME gegeben, daher müsste auch im Select-Teil die Bezeichnung NAME verwendet werden.
Ansonsten folgt der Select-Teil dem üblichen Standard. Wir schränken die Datenmenge hier per Where Clause bereits stark ein. Damit kommen wir zum nächsten Punkt:

MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)

Das Keyword MODEL leitet die Model Clause ein. Da PARTITION BY optional ist und wir ohnehin auf genau einen Schüler und ein Fach einschränken erübrigt sich ihr Nutzen hier. Die Abschnitte für DIMENSION BY und MEASURES sind klar ersichtlich, wir haben also 3 Dimensionen und 1 Measure (Faktum wäre das häufig benutzte deutsche Wort dafür). Hier können für die einzelnen Spalten Alias-Bezeichnungen vergeben werden, wie im Fehlerbeispiel oben ersichtlich war. Diese Alias-Bezeichnungen sind dann durchgehend zu nutzen, auch beim nächsten und in diesem Fall letzten Abschnitt:

(note [ 4, 'Anton Anger', 2021 ] =
round((note [ 3, 'Anton Anger', 2020 ] + note [ 2, 'Anton Anger', 2019 ] + note [ 1, 'Anton Anger', 2018 ]) / 3,0));

Zur besseren Lesbarkeit wurden die linke und rechte Seite getrennt. Hier sieht man eine exakte Zuweisung zu einer Zelle auf der linken Seite (in diesem Fall, würde es mehrere Schüler mit dem exakt gleichen Namen geben würde die Zuweisung mehrere Zellen befüllen) sowie mehrere exakte Zuweisungen (oder besser Abfragen) auf der rechten. In einfachen Worten steht hier:
Addiere die Noten des angegebenen Schülers aus Stufe 3 im Jahr 2020 bzw. aus Stufe 2 im Jahr 2019 und Stufe 1 im Jahr 2018 und dividiere die Summe durch 3 – eine manuelle Durchschnittsberechnung. Da wir in diesem Select Daten erhalten die es in der Tabelle gar nicht gibt, wäre eine Umsetzung ohne Model Clause nur über Analytic Functions oder Mengenoperationen wie UNION möglich. Beides würde mehr Code benötigen.
Nun kann man den Sinn einer manuellen Durchschnittsberechnung hinterfragen, wenn Oracle dafür praktische Aggregationsfunktionen anbietet und das durchaus zu Recht. Würden wir das Statement anpassen (weil z.B. nicht von 3 sondern von mehreren 100 Zeilen der Durchschnitt berechnet werden soll) würde es wie folgt aussehen:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round(avg(note) [ any, 'Anton Anger', jahr between 2018 and 2020 ],0));

Das wirkt gleich deutlich sauberer und einfacher. Vorsicht bei der Setzung der Klammern, die Aggregationsfunktion wird NUR um die Measure Spaltenbezeichnung gemacht, das Dimensions-Array in eckigen Klammern steht danach (wird das nicht gemacht gibt die Datenbank einen Fehler zurück: ORA-00934: Gruppenfunktion hier nicht zulässig).

Ein weiterer Aspekt hier sind der Wildcard Operator und eine Range Angabe, beides Mittel, um mehrere Zeilen auf einmal anzusprechen (was hier durch Verwendung der Aggregationsfunktion Sinn macht).

Der Wildcard Operator any bewirkt genau das was das Keyword vermuten lässt: Diese Spalte wird nicht betrachtet bei der Berechnung. Die Range-Angabe between 2018 and 2020 wiederum funktioniert exakt gleich wie eine entsprechende Where Clause.

Bevor wir die Model Clause gewinnbringender einsetzen, noch eine kurze Erklärung zur Zuweisung der Werte der Dimensionen in den Regeln. Die Beispiele bisher nutzten fast ausschließlich eine positionelle Referenz, das bedeutet die Werte stehen an der Stelle im Array an welcher in der Dimensionsdefinition die jeweilige Spalte steht.

Abbildung 7: Zuweisung der Werte der Dimensionen

Abbildung 7: Zuweisung der Werte der Dimensionen

Alternativ dazu kann man auch wie beim Aufruf einer PL/SQL Prozedur eine symbolische Referenz verwenden. Bei der Range-Angabe wie oben MUSS das gemacht werden. Wenn wir das nun für alle Stellen so umsetzen würde es wie folgt aussehen:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ schulstufe=4, schueler='Anton Anger', jahr=2021 ] = round(avg(note) [ schulstufe is any, schueler='Anton Anger', jahr between 2018 and 2020 ],0));

VORSICHT: Wenn symbolische Referenzen verwendet werden, dann können keine neuen Zeilen eingefügt werden. Das oben angeführte Statement ergibt also keine neue Zeile für 2021, das gilt allerdings nur für die linke Seite, folgendes Statement funktioniert also dann wieder korrekt:

select schulstufe, schueler, jahr, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch'
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021 ] = round(avg(note) [ schulstufe is any, schueler='Anton Anger', jahr between 2018 and 2020 ],0));

So weit war alles sehr simpel und eigentlich nicht des Aufwands einer Model Clause wert. Dehnen wir das Thema aus und lassen uns für den Schüler mit Nummer 1 für alle Fächer die geschätzten Noten für das Jahr 2021 ausgeben.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
 where schuelernummer = 1
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr, fach)
   MEASURES(note)
   (note [ 4, 'Anton Anger', 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ any, 'Anton Anger', jahr between 2018 and 2020 , cv(fach)],0))
   order by 1,4;

Um alle Fächer zu berechnen, muss eine Schleife genutzt werden, das ist dieser Teil:

for fach in (select distinct fach from schulnoten)

Grundsätzlich könnte auch der Any Operator genutzt werden, aber dann gibt die Abfrage nur Zeilen zurück, welche bereits existieren, ich könnte also nur die Noten aus den Jahren 2018 – 2020 berechnen lassen aber keine neuen Zeilen für 2021. Aus diesem Grund muss das ganze über eine Schleife gemacht werden. In diesem Zusammenhang ist dann auch noch folgender Operator wichtig:

cv(fach)

Der CV() Operator gibt den Current-Value, den aktuellen Wert zurück. Im Rahmen dieser Schleife gibt er also bei jedem Durchlauf den Wert des Durchlaufs zurück. Mit diesen beiden Informationen können wir nun für alle Schüler und Fächer der Klasse die neuen Noten berechnen.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
  where schuelernummer in (select schuelernummer from schulnoten where schulstufe = 3)
 MODEL 
   DIMENSION BY(schulstufe, schueler, jahr, fach)
   MEASURES(note)
   (note [ 4, for schueler in (select distinct schueler from schulnoten), 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ any, cv(schueler), jahr between 2018 and 2020 , cv(fach)],0))
   order by 1,2,4;

An diesem Beispiel erkennt man auch gut, dass die Model Clause nur auf Daten zugreifen kann, welche auf Basis der Where-Bedingung existieren. Würde man die Where Clause statt mit einer IN Clause mit einem direkten Filter auf die Schulstufe machen, würde man nur Daten für Schulstufe 3 und 4 erhalten und der Durchschnittswert basiert nur auf den Werten aus Schulstufe 3.
Soweit können wir nun für alle Fächer für jeden Schüler einer bestimmten Klasse/Schulstufe eine simple Voraussage der Noten in der nächsten Schulstufe abfragen. Idealerweise können wir das allerdings für jeden Schüler, unabhängig von der Schulstufe für jedes Fach. Das wäre der gewünschte Endzustand. Ein mögliches Statement dafür könnte wie folgt aussehen:

select schuelernummer,schulstufe, schueler, klasse, jahr, fach, note
  from schulnoten
 MODEL
   PARTITION BY(schuelernummer, schueler, klasse) 
   DIMENSION BY(jahr, fach)
   MEASURES(schulstufe,note)
   (note [ 2021, for fach in (select distinct fach from schulnoten) ] = round(avg(note) [ jahr between 2018 and 2020 , cv(fach)],0),
    schulstufe [ 2021, for fach in (select distinct fach from schulnoten) ] = max(schulstufe) [ jahr between 2018 and 2020 , cv(fach)] + 1)
   order by 2,5,3,6;

Da sinnvollerweise auch Daten zu den Schulstufen der jeweiligen Jahre vorhanden sein sollen und diese aber nicht mehr hartcodiert übergeben werden können ist die Schulstufe in die Measures verschoben würden. Zusätzlich werden die Daten mittels PARTITION BY nach Schüler aufgeteilt, das hat den simplen Grund, dass die Noten immer nur in Abhängigkeit der Noten des jeweiligen Schülers berechnet werden, das erspart auch mühsame Arbeit bei der Dimensionsdefinition. Die Klasse ist kein echtes Partitionierungskriterium, da sie aber ausgegeben werden soll kann sie problemlos hier mit eingetragen werden. Für die Schulstufe wird eine neue Regel erfasst, welche den Maximalwert des jeweiligen Schülers für den jeweiligen Zeitraum berechnet und um eins erhöht. Da wird genau genommen auch jedes Fach für sich betrachten könnten wir hier aber auch einfach das Fach von den Dimensionen in die Partition Clause schieben und sparen uns damit die For Schleife, das würde dann so aussehen:

select schuelernummer, schulstufe, schueler, klasse, jahr, fach, note
  from schulnoten
 MODEL
   PARTITION BY(schuelernummer, schueler, klasse, fach) 
   DIMENSION BY(jahr)
   MEASURES(schulstufe,note)
   (note [ 2021 ] = round(avg(note) [ jahr between 2018 and 2020 ],0),
    schulstufe [ 2021 ] = max(schulstufe) [ jahr between 2018 and 2020 ] + 1)
   order by 2,5,3,6;

Nochmal etwas simpler, denn genau genommen ist das Jahr die einzige Variable die wir hartcodiert setzen.
Einen letzten Fall sehen wir uns noch an. Die Kinder bekommen in der 4. Schulstufe statt Sachunterricht die beiden Fächer Biologie und Physik. Die Note für Biologie soll sich aus zu je 50% aus den Noten der Fächer Deutsch und Sachunterricht der 3. Schulstufe berechnen, die Note für Physik zu je 50% aus den Noten der Fächer Mathematik und Sachunterricht der 3. Schulstufe. Wir betrachten also nur die Schüler der 3. Schulstufe in diesem Fall. Das Statement dafür könnte man zum Beispiel wie folgt strukturieren.

select schulstufe, schueler, jahr, fach, note
  from schulnoten
  where schuelernummer in (select schuelernummer from schulnoten where schulstufe = 3)
 MODEL
   PARTITION BY(schueler)
   DIMENSION BY(schulstufe, jahr, fach)
   MEASURES(note)
   (note [ 4, 2021, for fach in (select distinct fach from schulnoten where fach <> 'Sachunterricht') ] = round(avg(note) [ any, jahr between 2018 and 2020 , cv(fach)],0),
    note [ 4, 2021, 'Biologie' ] = round(( note[ 3, 2020, 'Sachunterricht' ] + note[ 3, 2020, 'Deutsch' ] ) / 2,0),
    note [ 4, 2021, 'Physik' ] = round(( note[ 3, 2020, 'Sachunterricht' ] + note[ 3, 2020, 'Mathematik' ] ) / 2,0))
   order by 1,2,4;

Eine Kombination des vorigen mit diesem Statement ist nicht möglich, da die Schulstufe einmal als Dimension herangezogen wird und einmal als Measure berechnet wird. In diesem Fall kann ich also nur einen von beiden Fällen innerhalb einer Model Clause abdecken.
Wie bereits eingangs erwähnt, wurden in diesem Artikel lediglich die grundlegenden Funktionen der Model Clause abgedeckt. Weitere Artikel, welche die zusätzlichen Möglichkeiten erklären, werden folgen.

Audit-Spalten mit dem Oracle SQL Developer Data Modeler automatisieren

In vielen Projekten erlebe ich es, dass Entwickler Audit-Spalten verwenden um entsprechende Informationen leicht zugänglich zu haben: Wer hat eine Datenzeile wann angelegt – und wann wurde dieselbe Zeile zuletzt von wem wieder bearbeitet? Wurden Tabellen früher noch direkt per DDL-Script erzeugt (CREATE TABLE my_test (id NUMBER NOT NULL, etc.), kommen hier heutzutage eher Modellierungstools wie der Oracle SQL Developer Data Modeler zum Einsatz:

 

Über das Icon “Spalte am Ende der Liste hinzufügen“ plus wurden hier nacheinander die vier Audit-Spalten (CREATED_BY, CREATED_ON, UPDATED_BY, UPDATED_ON) hinzugefügt und die Spalteneigenschaften definiert.

Die wenigsten Projekte bestehen aber aus nur einer einzigen Tabelle – bei drei oder sieben Tabellen mag das manuelle Hinzufügen der einzelnen Spalten und die ebenfalls manuelle Konfiguration derselben ja noch funktionieren. Aber was, wenn der Entwickler zwei oder drei Dutzend Tabellen modellieren will?

Q: Geht das nicht einfacher?

A: Natürlich geht das – über die Icons „Ausgewählte Spalten kopieren“  und „Kopierte Spalten am Ende einfügen“. Hiermit werden die ganzen Spalten samt ihrer vollständigen Definition kopiert und in der Ziel-Tabelle eingefügt.

Allerdings stößt auch diese Variante ab einer gewissen Tabellenanzahl an ihre Grenzen. Bei mehreren Hundert Tabellen werden wahrscheinlich die wenigsten Entwickler die Geduld aufbringen, bei jeder Tabelle die Icons drücken zu wollen. Hoffnungslos aufwändig wird es, wenn dann nicht neue Spalten hinzugefügt werden sollen – sondern beispielsweise der Datentyp bereits definierter Spalten geändert werden soll, etwa weil [CREATED|MODIFIED]_BY künftig bis zu 100 Zeichen speichern können soll.

Q: Geht das nicht einfacher?

A: Natürlich geht das – viel einfacher sogar. 😉

Hier kommen im Data Modeler die sogenannten Transformationen ins Spiel – im Menü unter Extras > Entwurfsregeln und Transformationen > Transformationen zu finden:

Speziell die Transformation mit dem Namen „Table template – uses column name“ ist hier sehr nützlich:

Um die Wartung von Audit-Spalten zu automatisieren, muss nun eine Tabelle angelegt werden, die nur die gewünschten Audit-Spalten enthält und deren Name in der Transformation referenziert wird, im Beispiel also MY_JOURNAL_TEMPLATE:

Beim Anwenden der Transformation werden dann alle im Model vorhandenen Tabellen durchgelooped und bezüglich der Spalten an die Definition in der Template-Tabelle angepasst. Gibt es die Audit-Spalten noch nicht, werden sie neu hinzugefügt. Gibt es sie bereits, werden ihre Attribute bei Bedarf auf die Werte der Template-Tabelle aktualisiert. So ist es dann beispielsweise auch möglich mit wenigen Klicks eine Datentyp-Änderung für ein Projekt mit mehreren Hundert Tabellen auszuführen. Um zu überprüfen ob eine Spalte aus einem Template stammt, kann man sich in den „Spalteneigenschaften“ die „Dynamischen Eigenschaften“ anschauen. Wenn die Spalte aus einem Template heraus erstellt oder angepasst wurde, wird im Rahmen der Transformation der unter p_name angegebene Wert (in diesem Beispiel also ctemplateID) nämlich als Schlüssel in die Eigenschaften eingetragen.

Oracle SQL Tuning: Unmögliche Optimizer Hints

Englisch Version here: https://www.dbconcepts.at/oracle-sql-tuning-impossible-optimizer-hints/

Query Blöcke

Ich war bis jetzt der Meinung, dass ein Optimizer Hint nur innerhalb seines Query Blocks wirkt, außer man qualifiziert den Hint mit dem Query Block Namen [1].

Man kann beispielsweise Viewnamen in einem Hint nicht wirksam angeben.

In meiner Untersuchung zur Verbesserung des Statspack [2] habe ich gesehen, dass das nicht immer stimmt und wollte diese genauer untersuchen.

Ausgangslage

Meine Testbeispiele erstelle ich im Scott Schema, so dass jeder meinen Test nachvollziehen kann.

Mittels des Optimizer Hints werde ich einen schlechten Plan erzwingen, damit eindeutig ist, dass der Optimizer den Plan unfreiwillig und durch den Hint gewählt hat.

Für die Tests habe ich Oracle Version 19c verwendet, um gleich die neue Hint Report Funktion nutzen zu können.

Die Pläne habe ich mit dem folgenden Statement angezeigt:

select * from dbms_xplan.display_cursor(format=>'TYPICAL +hint_report')
;

Hier ist unser Beispiel Statement:

SELECT ename,
       dname,
       sal,
       grade
FROM salgrade s,
    (
        SELECT ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Ohne weitere Angabe erzeugt der Optimizer den folgenden Plan:

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                 |          |     1 |    94 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |          |     1 |    55 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL          | EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)|          |
|*  6 |   TABLE ACCESS FULL           | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ENAME"='SCOTT')
   5 - access("E"."DEPTNO"="D"."DEPTNO")
   6 - filter(("SAL">="LOSAL" AND "SAL"<="HISAL"))

Versuchen wir es nun mit einem Hint.
In unserem Hint erzwingen wir, dass der Optimizer mit der Tabelle dept beginnt, was er freiwillig nicht machen würde.

SELECT /*+ leading(d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Gegen meine Erwartung wird der Hint befolgt, obwohl er sich auf einen Alias in einem anderen Query Block bezieht.

Diesmal ist auch ein Hint Report dabei, da es einen Hint gibt.

-------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |       |       |     9 (100)|          |
|   1 |  NESTED LOOPS       |          |     1 |    94 |     9   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |          |     1 |    55 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT     |     4 |    88 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - filter("ENAME"='SCOTT')
   5 - filter(("SAL">="LOSAL" AND "SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   1 -  SEL$F5BB74E1
           -  leading(d)

Eigentlich hätte der Hint gar nicht funktionieren dürfen.

Der Hint befindet sich in der Hauptabfrage und referenziert ein Objekt in einem anderen Query Block.

Weshalb geht es wohl doch?

Wie man sieht wurde aber die Unterabfrage aufgelöst und mit der Hauptabfrage verschmolzen. Man spricht hier von einem Simple View Merging.

Ein Simple View Merging ist eine sogenannte Transformation. Der Optimizer schreibt die Abfrage um.

Durch die Transformation gibt es nur noch einen Query Block für die Abfrage.

Das könnte der Grund sein, dass der Leading Hint funktioniert.
Wird der obige Hint noch wirken, wenn man die Transformation verbietet?

SELECT /*+ leading(d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT /*+ NO_MERGE */  ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

In der Tat wird der Leading Hint jetzt nicht mehr befolgt.

Das ist typisch für das nicht funktionieren von Hints. Die Transformation erfolgt vor der Optimierung.

Dadurch werden zwei unterschiedliche Query Blocks erzwungen.
Das direkte Referenzieren eines anderen Query Blocks funktioniert dann nicht mehr.

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                  |          |     1 |    68 |     7   (0)| 00:00:01 |
|   2 |   VIEW                         |          |     1 |    29 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |          |     1 |    55 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS               |          |     1 |    55 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL         | EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)|          |
|   7 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    22 |     1   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL            | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("ENAME"='SCOTT')
   6 - access("E"."DEPTNO"="D"."DEPTNO")
   8 - filter(("DE"."SAL">="LOSAL" AND "DE"."SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1
         U -  leading(d)
   3 -  SEL$2
           -  NO_MERGE

Der Hint Report zeigt ebenfalls an, dass der Leading Hint nicht befolgt wird.

Im Statspack sah ich aber diese Variante des Hints:

SELECT /*+ leading(de.d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT /*+ NO_MERGE */  ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Das Qualifizieren mit dem Alias der Unterabfrage sollte eigentlich nicht funktionieren.

Der Alias der Unterabfrage ist kein Query Block Name.

Jedoch, es geht trotzdem:

--------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     9 (100)|          |
|   1 |  NESTED LOOPS        |          |     1 |    68 |     9   (0)| 00:00:01 |
|   2 |   VIEW               |          |     1 |    29 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |          |     1 |    55 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| DEPT     |     4 |    88 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL  | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("ENAME"='SCOTT')
   6 - filter(("DE"."SAL">="LOSAL" AND "DE"."SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   3 -  SEL$2
           -  leading(de.d)
           -  NO_MERGE

Fazit

Die Möglichkeit mit dem Alias einer Subquery zu Hinten erleichtert das Hinten von komplexen Abfragen.

Aufgrund des Hint Reports kann man sehen, dass der Hint korrekt verstanden wird.

Wie so vieles im Kontext von Hints ist auch diese Option nicht dokumentiert.

Ich habe die Abfrage von Version 11.2.0.4. bis 19.0 getestet und sie funktionierte immer.

Statt mit einer Unterabfrage in der From Klausel funktioniert es auch mit einer View.

Jedoch muss auch hier, wenn die View einen Alias bekommt, der Alias im Hint verwendet werden.

Jonathan Lewis hat mich darauf hingewiesen, dass die Art von Hints offiziell erlaubt ist.

Es handelt sich um global table hints [3].

Diese besonders nützliche Art des Hints ist leider noch zu wenig bekannt. Jedenfalls habe ich sie in meiner Praxis noch nicht angetroffen.

Quellen

Vergleich der Oracle SQL Funktionen ADD_MONTHS vs INTERVAL

Oracle SQL ADD_MONTHS vs. INVERVAL Funktionen

ADD_MONTHS:

Die Funktion ADD_MONTHS fügt einem Datum eine definierte Anzahl von Monaten hinzu.

Die Syntax sieht folgendermaßen aus:

ADD_MONTHS (input_date, number_months)

Um diese Operation durchführen zu können, werden also zwei verpflichtende Parameter benötigt:

Input_date: Datum, zu welchem n Monate dazugerechnet werden

Number_months: Anzahl der Monate vom Datentyp Integer, die zum angegebenen Datum dazugerechnet werden.

Der Rückgabewert dieser Funktion ist immer ein DATE.

SELECT ADD_MONTHS (‚17-04-2020‘, 2) AS ADD_MONTHS_RESULT
FROM DUAL

 

Ist das angegebene Datum der letzte Tag des Monats und das resultierende Datum hat weniger oder mehr Tage, so ist das Ergebnis der letzte Tag des Monats.

SELECT ADD_MONTHS (‚31-03-2020‘, 1) AS ADD_MONTHS_RESULT
FROM DUAL

Natürlich kann man vom angegebenen Datum auch Monate abziehen, indem man ein „–“ vor den Monaten platziert.

SELECT ADD_MONTHS (‚28-02-2020‘, -1) AS ADD_MONTHS_RESULT
FROM DUAL

Spannend wird das Thema bei Schaltjahren.
2020 ist ein Schaltjahr, somit hat der Februar 29 anstatt 28 Tage.

SELECT ADD_MONTHS (‚28-02-2020‘, 1) AS ADD_MONTHS_RESULT
FROM DUAL

Nimmt man jedoch den 29. Februar, so bekommt man nicht den 29. März als Resultat, sondern den 31. März.

SELECT ADD_MONTHS (‚29-02-2020‘, 1) AS ADD_MONTHS_RESULT 
FROM DUAL

INTERVAL YEAR TO MONTH:

Dies ist ein spezieller Datentyp, welcher es ermöglicht, Intervalle von Jahren und Monaten zu speichern.

Die Syntax hat folgendes Format:

INTERVAL ‚year[-month]‘ [YEAR[(precision)])] [TO MONTH]

Defaultmäßig wird precision auf 2 gesetzt, sofern nichts anderes angegeben wurde. Das heißt, es können nur 99 Jahre und 11 Monate gespeichert werden.

INTERVAL '120-3' YEAR(3) TO MONTH:

Intervall von 120 Jahren und 3 Monaten.

In diesem Fall muss precision angegeben werden, da die angegebenen Jahre dreistellig sind.

INTERVAL '105' YEAR(3)

Intervall von 105 Jahren.

INTERVAL '500' MONTH(3)

Intervall von 500 Monaten.

In diesem Fall muss precision angegeben werden, da die angegebenen Monate dreistellig sind.

Bei Monatsangaben muss man sehr genau sein.

Anders als bei ADD_MONTHS rechnet INTERVAL nicht automatisch auf den letzten Tag des Monats.

Folgendes Statement gibt einen „ORA-01839: Datum für angegebenen Monat nicht gültig“ Fehler zurück:

SELECT TO_DATE (‚31-01-2020‘) + INTERVAL ‚1‘ MONTH AS RESULT
FROM DUAL

Auch bei Schaltjahren muss man sehr genau sein.

Dieses Statement gibt einen „ORA-01839: Datum für angegebenen Monat nicht gültig“ Fehler zurück:

SELECT TO_DATE (‚29-02-2020‘) + INTERVAL ‚3‘ YEAR AS RESULT 
FROM DUAL

Korrekt wäre:

SELECT TO_DATE (‚29-02-2020‘) + INTERVAL ‚4‘ YEAR AS RESULT FROM DUAL


 

Die SQL WITH clause (oder Subquery Refactoring)

Die SQL WITH clause (oder Subquery Refactoring) wurde mit der Oracle 9i Release 2 Database eingeführt. Deren Benutzung ist seither Standard, da sie deutliche Vorteile bietet.

Mit der WITH clause wird eine temporäre Tabelle/View erstellt, auf die im Nachhinein zugegriffen werden kann.

Dadurch spart man sich bei komplexen SQL-Statments jede Menge an Code und macht ihn in der Regel auch lesbarer. Die temporäre Tabelle/View wird nur solang innerhalb eines SQL Statements verwendet, bis die Ausführung beendet ist.

Als Beispiel nehmen wir eine Employee und Department Tabelle.

Employees:

Departments:

 

Wir wollen nun alle Employees anzeigen, deren Standort sich in New York verbindet.

Standardmäßig benutzen wir ein Subselect da sich die Location in der Departments Tabelle befindet.

SELECT *
FROM emp e
WHERE e.DEPTNO in (SELECT d.DEPTNO from dept d where d.loc = 'NEW YORK');

Jetzt benutzen wir die WITH clause und erhalten das gleiche Ergebnis.

With w_dept_ny as
(SELECT /*+ materialize */ d.deptno
FROM dept d
WHERE d.loc = 'NEW YORK')
SELECT *
FROM emp e
join w_dept_ny d on e.deptno = d.deptno;

Seit Oracle 12c ist es möglich PL/SQL Functions und Procedures in eine WITH claus zu packen.

Dazu wird es noch einen gesonderten Blog Eintrag geben.