Beiträge

Manuel R.

Oracle SQL MATCH_RECOGNIZE

/0 Kommentare/in , , , /von

Ein sehr mächtiges und effizientes Konstrukt gibt es seit der Oracle Version 12C: MATCH_RECOGNIZE. Vereinfacht gesagt lassen sich mit MATCH_RECOGNIZE regular expressions auf Zeilen anwenden, um so bestimmte Muster zu erkennen. Beispielsweise lassen sich somit in der Finanzwelt bestimmte Aktienkurs-Verläufe erkennen. Dieser Blogbeitrag soll dazu dienen, sich diesem Thema anhand einfacher und fiktiver Beispiele langsam anzunähern.
Grundlegender Aufbau:

 

 

PATTERN_PARTITION_CLAUSE

Diese ist optional. Hier kann man die Daten sortieren (ORDER BY) und in Gruppen aufteilen (PARTITION BY). Um bei jeder Ausführung der Query konsistente Ergebnisse zu erhalten, sollte zumindest ein ORDER BY eingebaut werden.

PATTERN_MEASURES_CLAUSE

Dieser Abschnitt definiert die Spalten, welche im SELECT Teil ausgegeben werden. Es gibt einige inbuilt functions, welche hier verwendet werden können. Dazu später mehr.

PATTERN_DEF_DUR_CLAUSE

Hier wird die eigentliche magic definiert. In diesem Abschnitt werden die patterns definiert, welche über jede Zeile ausgeführt werden. Gibt es ein match, sprich wird genau dieses Muster identifiziert, wird die Zeile ausgegeben.

 

Wir schauen uns anhand einiger Beispiele die einzelnen Abschnitte näher an. Zu diesem Zwecke habe ich eine Tabelle mit Aktiendaten erstellt, welche wie folgt definiert ist (Das Skript kann am Ende des Artikels heruntergeladen werden):

 

 

 

 

 

 

Die Tabelle beinhaltet Aktienkurse eines fiktiven Unternehmens. Wir starten mit einer simplen Abfrage, anhand welcher ich auf die Struktur näher eingehen möchte: Alle Handelstage, an denen es einen oder mehrere Handelstage gibt, welche einen Startkurs größer als 17 haben:

 

 

 

 

 

 

 

 

 

Nachfolgend eine Erklärung der einzelnen Codeteile:

PARTITION BY: Unterteilt die Daten je nach Titel in einzelne Gruppen. In unserem Fall haben wir nur einen Titel in unseren Testdaten, wodurch es sowieso nur eine Gruppe gibt, nämlich die der „Datenbank AG“.

ORDER BY: Sortiert die Daten in der jeweiligen Gruppe je nach Handelstag aufsteigend.

MEASURES: Definiert, welche Spalten im result set zusätzlich ausgegeben werden. Hier sehen zwei inbuilt functions: CLASSIFIER() und MATCH_NUMBER(). CLASSIFIER () gibt an, welche pattern variable für diese Zeile gematched hat. MATCH_NUMBER () vergibt für gematchte Gruppe eine eigene Nummer, welche bei 1 startet und sich für jede neue Gruppe um 1 erhöht.

ALL ROWS PER MATCH: Definiert, dass bei aufeinanderfolgenden matches jede Zeile ausgegeben wird. Das pendant dazu wäre ONE ROW PER MATCH, welches auch der Default Wert ist. Hier wird dann nur die erste Zeile der jeweiligen Gruppe ausgegeben. In unserem Beispiel sehen wir, dass für Gruppe 4 jede einzelne Zeile ausgegeben wird; würden wir stattdessen ONE ROW PER MATCH verwenden, würden wir für Gruppe 4 nur die erste Zeile sehen. Das Ganze hat dann auch Auswirkungen auf die oberen drei Bereiche: Bei ALL ROWS PER MATCH, so wie wir es verwenden, könnte ich den den PARTITION BY, ORDER BY und MEASURES Bereich ganz weglassen, da sowieso alle Spalten im result set ausgegeben werden. Verwende ich hingegen ONE ROW PER MATCH, muss ich entweder Spalten im MEASURES Bereich angeben und/oder die ORDER BY / PARTITION BY Klausel angeben. Das Prinzip ist dem GROUP BY also sehr ähnlich.

PATTERN: Hier liste ich die einzelnen Variablen auf, welche im DEFINE Bereich definiert sind und auf die jeweilige Zeile zutreffen müssen. In unserem Beispiel ist das die Variable „start_1“ mit einem regular expression chracter „+“. Dies bedeutet, dass es einen oder mehrere Handelstage geben muss, an denen der Startkurs höher als 17 ist.

DEFINE: Hier werden die Variablen mit ihren conditions definiert, welche im PATTERN Bereich verwendet werden können.

 

 

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Auf die Handelstage 01.12.2022, 03.12.2022 und 09.12.2022 folgen jeweils Tage, an denen der Startkurs kleiner als 17 ist. Der 13.12. ist der erste Tag einer Reihe von Handelstagen, wo der Startkurs höher als 17 ist.

Im nächsten Beispiel wollen wir alle Handelstage ausgeben, an denen exakt zwei Mal in Folge der Startkurs höher ist als der Schlusskurs am Vortag.

 

 

 

 

 

 

 

Hier sehen wir, dass das SQL im Prinzip ähnlich aufgebaut ist als im Beispiel eins. Im DEFINE Bereich ist unsere pattern Variable mit der dazugehörigen condition definiert. Diese besagt, dass der Startkurs der jeweiligen Zeile höher sein muss als der Schlusskurs der darüberliegenden Zeile. Hierzu verwende ich die Funktion PREV(), welche dann genau auf die darüberliegende Zeile verweist. An diesem Beispiel sehen wir auch, dass es Sinn macht, die Daten zu sortieren, da wir nicht immer wissen, in welcher Reihenfolge die Daten aus der Datenbank abgefragt werden (das Ergebnis ist also nicht deterministisch). Im PATTERN Bereich holen wir uns die Variable und wandeln diese nun mit „{2}“ zu einem regulären Ausdruck um. Dies bedeutet, dass wir zwei Handelstage in Folge haben wollen, auf welche die condition zutrifft. Zur Kontrolle definieren wir im MEASURES Bereich die Spalte „schlusskurs_vortag“. Da MATCH_RECOGNIZE wie eine INLINE VIEW zuerst ausgeführt wird, können wir diese im SELECT Teil verwenden und ausgeben.

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen nun immer zwei Handelstage in Folge, an denen der Startkurs höher ist als der Schlusskurs am Vortag.

Für das nächste Beispiel überlegen wir uns folgendes Muster: An Handelstag eins soll der Startkurs zwischen 16 und 17 sein, an Handelstag zwei soll der Startkurs größer als 17 sein, an Handelstag drei soll der Startkurs wieder zwischen 16 und 17 sein.

 

 

 

 

 

 

 

 

Dieses SQL enthält eine Erweiterung, nämlich AFTER MATCH SKIP TO LAST. Diese Syntax bedeutet, dass wenn es ein match gibt, die Suche nach einem neuen Match bei der letzten pattern Variable beginnt.

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen nun alle Handelstage, die genau unserem Muster entsprechen. Würden wir AFTER MATCH SKIP TO LAST weglassen, würden nur die ersten drei Handelstage im result set erscheinen, da die Suche erst am 22.12.2022 weitergehen würde.

In unserem letzten Beispiel wollen wir eine typische V-Formation erkennen. Dies bedeutet, dass der Aktienkurs zunächst einbricht und sich nach Erreichen der Talsohle wieder erholt.

 

 

 

 

 

 

 

 

 

 

 

 

Hier haben wir im DEFINE Bereich drei pattern Variablen definiert, wobei man die Definition der ersten Variable „strt“ auch weglassen könnte, da sie sowieso jede Zeile matched. Diese dient für uns lediglich als Einstiegspunkt in unsere Mustersuche.

 

 

 

 

 

 

 

Wie können wir das Ergebnis interpretieren?

Wir sehen in unserem result set anhand der Gruppennummer, welche Handelstage eine V-Formation bilden und somit zu einer Gruppe gehören. Das Ende der jeweiligen V-Formation ist zugelich der Beginn einer neuen Kursbewegung nach unten.

Wir haben uns nun anhand einiger Beispiel angesehen, was MATCH_RECOGNIZE ist und was für Abfragen wir damit durchführen können.

 

 

SQL Statements mithilfe von SQL Macros schlanker und dadurch effizienter schreiben
Manuel R.

SQL Macros

/0 Kommentare/in /von

SQL Macros

Des Öfteren kommt es vor, dass man ein und denselben Ausdruck über mehrere SELECT Befehle oder WHERE Bedingungen benötigt.
Mit den SQL Macros gibt es nun ein Werkzeug, um solche SQLs lesbarer und performanter zu gestalten.

Ein SQL Macro ist eine ausgelagerte Funktion, welche innerhalb eines SQL-Befehls aufgerufen wird. Dieses Konstrukt wird verwendet, um gängige SQL-Ausdrücke auszulagern und wiederzuverwenden. Überall, wo man normalerweise Funktionen aufrufen kann, können SQL Macros verwendet werden. In Oracle 20c nur als preview in der Cloud vorhanden, sind SQL Macros ab Oracle 21c fixer Bestandteil von Oracle Datenbanken, jedoch gibt es schon ab der Version 19.6 SQL TABLE Macros.

Generell gibt es zwei Arten von SQL Macros:

SCALAR: Liefert einen Ausdruck und kann im SELECT, WHERE, HAVING, GROUP BY und ORDER BY Teil des Befehls verwendet werden.

TABLE: Liefert einen Ausdruck und darf ausschließlich im FROM Teil des Befehls verwendet werden.

Aufbau:

Wird der Macro Typ nicht angegeben, wird das Macro defaultmäßig als TABLE Macro erstellt. Da in der Oracle Version 19.6 nur TABLE Macros zur Verfügung stehen, muss der Typ nicht angegeben werden.

Das SQL Macro wird nicht erst während der runtime ausgeführt, sondern bereits während dem parsen der Abfrage und liefert im Anschluss einen String (VARCHAR2, CHAR, CLOB) in die Hauptabfrage zurück. Dem Optimizer stehen daher schon alle nötigen Informationen zur Optimierung des SQLs zur Verfügung. Da das SQL Macro nur einmal während der parse time ausgeführt wird, entfällt daher auch der context switch zwischen SQL und PL/SQL engine. Des Weiteren können Parameter einem Macro mitübergeben werden. Dies ist besonders bei VIEWs interessant, denn bis dato konnte man VIEWs nicht parametrisieren.

Beispiele

Beispiel (Scalar SQL Macro): Wir wollen aus der Tabelle „emp“ alle Mitarbeiter ausgeben, welche schon mehr als 38 Jahre im Unternehmen angestellt sind.

Der Ausdruck „FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), t1.hiredate)/12)” wird in diesem Statement zwei Mal verwendet, diesen können wir also auch in ein SQL Macro auslagern.

Die Funktion „job_duration“ wurde als SCALAR SQL Macro angelegt und ist nun verwendbar.



Beispiel (TABLE SQL Macro): Wir wollen aus der Tabelle „emp“ alle Mitarbeiter mit dem Jobtitel „ANALYST“ ausgeben.


Wir können den Ausdruck in der FROM Klausel auch als Macro auslagern.


Die Funktion „analyst_emp“ wurde als TABLE SQL Macro angelegt und ist nun verwendbar.

Folgendes Macro liefert zwar beim Kompilieren keinen Fehler, jedoch beim Aufruf im SQL Statement:

Fehler Nummer 1: Zwei Ergebnisspalten in einem Macro. Dies ist nicht möglich, da ein Macro immer nur eine Ergebnisspalte zurückliefert.

Fehler Nummer 2: Spalten Alias in einem Macro. Da Macros unter Anderem auch in der WHERE Klausel verwendet werden können und diese mit Spalten Aliase nicht arbeiten können, wird hier ein weiterer Fehler erzeugt.

Parameter

Um mehr Flexibilität in SQL Macros zu erlangen, können Parameter verwendet werden. Beispiel: Wir wollen anhand eines beliebig gewählten Jahres wissen, welche Mitarbeiter schon länger als 38 Jahre im Unternehmen tätig sind.

Nun haben wir das SQL Macro erstellt und können es nun in unserer Abfrage verwenden.

Data Dictionary

Da SQL Macros eigene Datenbankobjekte sind, sind diese auch im Data Dictionary ersichtlich.



 

Andreas Schlögl

CBQT-ORE and its FIRST_ROWS optimization inability

/0 Kommentare/in , /von

CBQT-ORE and its FIRST_ROWS optimization inability

Terminology:

CBQT: cost based query transformation
ORE: or expansion

Some days ago, I took a look on a customer’s performance issue which was introduced after their upgrade from 12.1 to 19c. Quite fast we could narrow it down to a new optimizer feature „cost based or expansion“.

To get a basic understanding of the feature, I’ll recommend reading Optimizer Transformations: OR Expansion by Oracle’s CBO PM Nigel Bayliss.

As a quick fix we simply disabled the feature by setting „_optimizer_cbqt_or_expansion“ = off and the performance went back to good again.

Afterwards I wanted to understand the root cause and built a model to reproduce the problem.

rem ######################################
rem # set environment                    #
rem ######################################
alter session set statistics_level=ALL;

rem ######################################
rem # prophylactic cleanup               #
rem ######################################
drop table asc_t2;
drop table asc_t1;
drop table asc_t3;

rem ######################################
rem # create testdata                    #
rem ######################################

--will hold 1M rows with a unique ID
create table asc_t1
as
with gen as
(
   select rownum dummy from dual connect by level <= 1e4
)
select rownum id
  from gen, gen
 where rownum <= 1e6;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t2
as
select id, mod(id, 500) t1_id, lpad('*', 250, '*') pad
  from asc_t1;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t3
as
select id, t1_id, pad
  from asc_t2;

--indexes
create unique index asc_t1_uk on asc_t1 (id);
create unique index asc_t2_uk on asc_t2(id);
create index asc_t2_i1 on asc_t2(t1_id);
create unique index asc_t3_uk on asc_t3(id);
create index asc_t3_i1 on asc_t3(t1_id);

And this is the query to be examined:

select t1.*
  from asc_t1 t1
 where id between 1 and 10
   and exists (select 1
                 from asc_t2 t2, asc_t3 t3
                where t2.id = t3.id
                  and (t2.t1_id = t1.id or t3.t1_id = t1.id)
              );

With no hints or further parameters set, this is the plan including some rowsource execution statistics pulled from memory after its execution:
So at first we can see that ORE was used and the query was split into two disjunct union all branches. What’s really interesting for me at a first sight is the presence of a blocking operation in the VW_ORE block (HASH JOIN at ID 4), despite the fact it is called in an exists clause and therefore has the ability to leave after the first row is found.

So for each row we got from ASC_T1 an in-memory hash table was build after scanning the appropriate index on T2 and visiting the table block (IDs 6 and 5).  In total 20.000 rows on 20.078 buffers were read on these operations. After that index ASC_T3_UK was probed against that hash table and here we see the effects of “exists” very cleary:  despite the index contains 1 million entries just 55 rows over all 10 calls needed to be read to find a first match and therefore be able to quit, because the exists clause was fulfilled. The second union-all branch wasn’t called at all.

Let’s look at two more examples.

First: forbid CBQT-ORE from kicking in. 

select t1.*
  from asc_t1 t1where id between 1 and 10 
 and exists (select /*+ no_or_expand */1 
                          from asc_t2 t2, asc_t3 t3 
                        where t2.id = t3.id 
                              and (t2.t1_id = t1.id or t3.t1_id = t1.id) 
                            );

And its rowsource execution statistics:
It has got a much lesser cost than the ORE plan and much less buffers were visited to execute the query. It gives also the impression that the CBO is now aware that it has to deal with an “exists” clause here, because that part of the plan was optimized to find a first matching row very quickly. E.g low cost for the FTS on ASC_T2 or the absence of blocking operations are indicators for this strategy.

Second: Switch back to LORE (Legacy OR Expansion)

select /*+ opt_param('_optimizer_cbqt_or_expansion', 'off') */ t1.* 
  from asc_t1 t1 
 where id between 1 and 10 
    and exists (select 1 
                             from asc_t2 t2, asc_t3 t3 
                          where t2.id = t3.id 
                                and (t2.t1_id = t1.id or t3.t1_id = t1.id) 
                            );

Again rowsource execution statistics:
This one now shows the lowest cost and least buffers visited overall. It also seems to be aware of the exists clause and adapts a first_rows strategy in the relevant parts of the execution plan! This is basically what I would have expected from the CBQT-ORE.

So we now have a theory that CBQT-ORE loses track that its query block is called in an “exists” clause and provides an execution plan as if it was a standalone query, where it would need to fetch all the rows.

There’s some more evidence to this theory.

1.) The transformed query from the VW_ORE query block shows the same cost and execution plan when it is costed as a standalone query. The correlated value from the outer rowsource was replaced with a bind variable in the following example:

select 0
  from (
             select 1
                 from asc_t3 t3, asc_t2 t2
              where t2.id = t3.id and t2.t1_id = :b1
            union all
           select 1
               from asc_t3 t3, asc_t2 t2
            where t2.id = t3.id      
                 and t3.t1_id = :b1 
                 and lnnvl (t2.t1_id = :b1)
              );

generates this plan, which matches the “VW_ORE_82971ECB” from our first query.



2.) The 10053 traces for both the disabled CBQT-ORE and the old-style OR-Expansion (LORE) show lots of references that a first_rows optimization approach was chosen for the query blocks in question.

Like:

On the other hand when looking at the 10053 trace of the first query, interestingly before the ORE checks kicked in, the same plan was already found which was used in query 2, where I explicitly disabled the transformation.

Final cost for query block SEL$2 (#2) – First K Rows Plan:
  Best join order: 1
  Cost: 506.142990  Degree: 1  Card: 2.000000  Bytes: 71964.000000
  Resc: 506.142990  Resc_io: 506.000000  Resc_cpu: 4233492
  Resp: 506.142990  Resp_io: 506.000000  Resc_cpu: 4233492

Later in the same tracefile, the ORE transformation and costing was performed and produced a final cost (8591) which was (way) higher than the formerly calculated plan with a final cost of 506. But however at the end it was not picked and the CBO stayed with the cheapest ORE-plan.

Summary:
CBQT-ORE does not seem to be aware when its query block resides in an “exists” clause and therefore doesn’t optimize for first rows access patterns. The query block gets optimized as if all rows would be needed to fetch. Additionally in certain scenarios it seems to “forget” if cheaper plans were found during the whole optimization process. If this effects you in a negative way, as a first action the cost based transformation can be turned off and the legacy version used instead. Additionally an SR was raised to tackle this issue.

Further Reading:
Excellent articles on the topic of CBQT Or-Expansion can also be found on the blogs of Patrick Joliffe, Mohamed Houri and Nenad Noveljic

Update 1:
After playing with the same testcase in my 21c lab I noticed the that the problem went away. After quick look into v$system_fix_control I found bug 28414968 “expansion with some constant branches;fkr1 in (NOT)EXISTS subque” which is first available in 19.11 and has to be activated proactively. That plan now gets produced in in 21c and after setting alter session set „_fix_control“=’28414968:3′; in >=19.11
10 buffers less visited compared to the LORE plan, as ID 8 was superfluous in the LORE plan.

Funnily, if I code the union-all instead of the or-predicate myself, I still get the old plan. But that’s one topic for another post I guess.

select t1.id id
  from demo.asc_t1 t1
 where t1.id >= 1
   and t1.id <= 10
   and exists (select 0
                            from demo.asc_t3 t3, demo.asc_t2 t2
                         where t2.id = t3.id
                               and t2.t1_id = t1.id 
                         union all
                         select 1
                             from demo.asc_t3 t3, demo.asc_t2 t2
                          where t2.id = t3.id
                                and t3.t1_id = t1.id
                                and lnnvl (t2.t1_id = t1.id)
                            );

Header Oracle SQL Model Clause
Christoph H.

Oracle SQL Model Clause

/0 Kommentare/in , /von

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.

Erstellskript und Testdaten runterladen
Vergleich der Oracle SQL Funktionen ADD_MONTHS vs INTERVAL
Manuel R.

Oracle SQL ADD_MONTHS vs. INVERVAL Funktionen

/0 Kommentare/in /von

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


 

Christian S.

Die SQL WITH clause (oder Subquery Refactoring)

/0 Kommentare/in /von

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.

 

 

 

 

Oracle LiveSQL um SQL und PL/SQL Scripts zu testen
Christoph H.

SQL oder PL/SQL Skripte mit Oracle LiveSQL testen

/0 Kommentare/in /von

Wer kennt das Problem nicht, dass man SQL oder PL/SQL Skripte testen will und man hat vielleicht keine Datenbank zur Verfügung oder will die Ergebnisse mit anderen teilen?

Javascript (und in gewissem Sinne auch HTML und CSS) hat dafür z.B. JSFiddle.

Oracle hat nun nachgezogen und sein entsprechende Pendant „LiveSQL“ dazu online gestellt.
Unter https://livesql.oracle.com kann man nun genau das tun.

Alles was man benötigt ist ein kostenloster Oracle Account, der innerhalb von wenigen Minuten angelegt ist.

Entwickelt in der Entwicklungsumgebung Oracle Application Express (APEX) und auf einer Oracle Datenbank Version 12.1 gehostet, kann man in LiveSQL eigene Schemata erstellen und seine Skripte darauf laufen lassen.
Doch das ist noch nicht alles. Man kann seine eigenen Skripte speichern und sie dann entweder öffentlich mit allen LiveSQL Nutzern teilen oder privat mittels Key nur bestimmten Personen zugänglich machen.

Weiters gibt es bereits zu etlichen Themen Skripte/Tutorials.
Zum aktuellen Zeitpunkt gibt es in Summe 133 Skripte/Tutorials auf LiveSQL. Diese umfassen die klassischen Themen wie SQL und PL/SQL allgemein, aber auch fortgeschrittenere Bereiche wie Analytic Functions sowie XML und JSON in der Datenbank.

Die Bedienung ist simpel und auf das Wesentliche beschränkt gehalten.
Die Seite ist auch auf mobilen Endgeräten nutzbar und passt sich der Displaygröße entsprechend an.