Donnerstag, November 20, 2014

ANSI Join Syntax und Prädikatanordnung

Die ANSI-Syntax zur Formulierung von Joins finde ich grundsätzlich deutlich lesbarer als Oracles traditionelle Schreibweise und würde sie vermutlich regelmäßig einsetzen, wenn sie nicht so viele Bugs hervorrufen würde. Einer davon ist gestern in einem OTN-Thread aufgetaucht. Dort hatte sich der Fragesteller darüber gewundert, dass zwei Varianten eines Outer-Joins unterschiedliche Ergebnisse lieferten, je nachdem, ob die ON-Prädikate direkt bei den zugehörigen FROM-Klauseln erschienen, oder getrennt von diesen in einem gemeinsamen abschließenden Block. Mein erster Gedanke war, dass die zweite Variante syntaktisch falsch sein sollte, und mein erstes Beispiel schien diese Annahme zu bestätigen, aber offenbar hängen der Erfolg oder Misserfolg - und auch das Ergebnis - von der Reihenfolge der Bedingungen ab. Dazu ein Beispiel:

-- 12.1.0.2
drop table t1;
drop table t2;
drop table t3;
 
create table t1
as
select rownum id
     , 'AAA' col1
  from dual
connect by level <= 15;
 
create table t2
as
select rownum id
     , 'BBB' col1
  from dual
connect by level <= 15;
 
create table t3
as
select rownum id
     , 'CCC' col1
  from dual
connect by level <= 10;

select /* normal ANSI syntax */
        count(*)
  from t1
  left outer join t3
    on (t3.id = t1.id)
 inner join t2
    on (t3.id = t2.id);
 
  COUNT(*)
----------
        10
 
select /* strange ANSI syntax: predicate order 1, 2 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t1.id)
    on (t3.id = t2.id);

    on (t3.id = t1.id)
                *
FEHLER in Zeile 6:
ORA-00904: "T1"."ID": ungültiger Bezeichner
 
select /* strange ANSI syntax: predicate order 2, 1 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t2.id)
    on (t3.id = t1.id);
 
  COUNT(*)
----------
        15

Demnach ergibt sich:
  • die (mehr oder minder) normale Anordnung der Elemente liefert ein Ergebnis mit 10 rows.
  • die erste Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t1, t3-t2) liefert einen Fehler.
  • die zweite Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t2, t3-t1) liefert 15 rows.
Wenn man dazu ein CBO-Trace erzeugt (Event 10053) wird deutlich, dass der Optimizer im Rahmen seiner Transformationen den Outer Join aus der ersten Query ausschließt, während er in der zweiten (lauffähigen) Query erhalten bleibt, woraus sich die 10 bzw. 15 Ergebnissätze ergeben:

-- Final query after transformations (reformatted)
/* normal ANSI syntax */
SELECT COUNT(*) "COUNT(*)"
   FROM "C##TEST"."T1" "T1"
      , "C##TEST"."T3" "T3"
      , "C##TEST"."T2" "T2"
  WHERE "T3"."ID" = "T2"."ID"
    AND "T3"."ID" = "T1"."ID"

/* strange ANSI syntax: predicate order 2, 1 */
SELECT COUNT(*) "COUNT(*)"
  FROM "C##TEST"."T1" "T1"
     , (SELECT "T3"."ID" "ID"
          FROM "C##TEST"."T3" "T3"
             , "C##TEST"."T2" "T2"
         WHERE "T3"."ID" = "T2"."ID") "from$_subquery$_004"
WHERE "from$_subquery$_004"."ID"(+) = "T1"."ID"

Ich muss gestehen, dass ich mir nicht völlig sicher bin, welche der beiden Interpretationen der Join-Bedingungen korrekt ist - grundsätzlich handelt es sich offenbar um eine Frage der Reihenfolgen:
  • wenn zunächst der Outer Join von t1 und t3 erfolgt, der 15 rows liefert, und dann ein Inner Join dieses Ergebnisses an t2 durchgeführt wird und die Verknüpfung über die t3.id erfolgt, die im ersten Zwischenergebnis NULL ist, dann ist das Ergebnis 10 korrekt.
  • wenn zunächst der Inner Join von t2 und t3 erfolgt und dann der Outer Join dieses Zwischenergebnisses an t1, dann sollte sich die 15 ergeben.
Persönlich würde ich in einem solchen Fall wahrscheinlich eine explizite Klammerung über Inline-Views (oder CTEs) durchführen (und hoffen, dass der Optimizer diese Klammerung nicht wieder durch Transformationen beseitigt). Ein Bug scheint jedenfalls zu sein, dass die Reihenfolge der Prädikate darüber entscheidet, ob ein Fehler oder ein Ergebnis zurückgeliefert wird. Ich vermute, dass tatsächlich die Reihenfolge der Prädikate entscheidet, welche Verknüpfung Vorrang besitzt - und möglicherweise sagt die ANSI-Spezifikation mehr darüber aus, wie die Verarbeitungsreihenfolge der Join Operationen gedacht ist - wenn ich dazu noch Hinweise finde, ergänze ich sie. Ein Indiz dafür, dass tatsächlich der Inner Join Vorrang haben sollte, liefert postgres: dort liefern beide Varianten 15 Datensätze.

Korrektur: die abschließende Aussage zu postgres war falsch (basierend auf einem copy&paste-Fehler) - tatsächlich verhält sich postgres exakt genauso wie Oracle und liefert 10 rows, einen Fehler ("ERROR:  invalid reference to FROM-clause entry for table t1. TIP:  There is an entry for table t1, but it cannot be referenced from this part of the query.") bzw. 15 rows.

Nachtrag 21.11.2014: Patrick Barel hat mich in seinem Kommentar davon überzeugt, dass es sich nicht um einen Bug handelt (worauf bereits das postgres-Verhalten hindeutete), sondern um ein definiertes Verhalten. Seltsam sieht es trotzdem aus...

Mittwoch, November 19, 2014

SQL Plan Directive in 12c

Mir ist gerade aufgefallen, dass ich bisher nichts über die in 12c eingeführten SQL Plan Directives geschrieben habe, die mir erstmals im White Paper Optimizer with Oracle Database 12c von Maria Colgan begegnet sind. In der Dokumentation wird die SQL Plan Directive folgendermaßen definiert:
A SQL plan directive is additional information and instructions that the optimizer can use to generate a more optimal plan. For example, a SQL plan directive can instruct the optimizer to record a missing extension.
Grundlage für die Anlage einer Direktive sind die Informationen, die über die Automatic Reoptimization Features ermittelt werden. Wenn das Cardinality Feedback (das sich in 12c Statistics Feedback nennt) für eine rowsource Operation eine Abweichung zwischen erwarteter und tatsächlicher Satzanzahl mitteilt, dann wird eine SQL Plan Directive erzeugt, die dem Optimizer mitteilt, bei der nächsten Optimierung der Query zusätzliche dynamic sampling Informationen für Tabellen oder Spaltengruppen zu erfassen - wobei die extended statistics für die column group im Erfolgsfall auch persistent angelegt werden, was die Verwendung der Direktive dann überflüssig machen kann. Die Inhalte der Direktiven sind in den Dictionary Views DBA_SQL_PLAN_DIRECTIVES und DBA_SQL_PLAN_DIR_OBJECTS dargestellt. Direktiven sind nicht an eine SQL_ID gebunden, sondern können für ähnliche Queries herangezogen werden (etwa, wenn die Unterschiede auf der Ebene unterschiedlicher Spalten in der Select-Liste liegen).

Einige interessante Hinweise zu den Änderungen, die sich für die Direktiven in 12.1.0.2 ergeben haben, findet man bei Franck Pachot. Insbesondere erläutert er die Status-Angaben der STATE-Spalte in DBA_SQL_PLAN_DIRECTIVES.

In 12.1.0.1 gab es folgende Status-Angaben:
  • NEW: der Status nach der Anlage der Direktive: zu diesem Zeitpunkt ist nur bekannt, dass es eine Abweichung zwischen erwarteten und tatsächlichen Cardinalities gab.
  • MISSING_STATS: ergibt sich, wenn nach einer folgenden Optimierung (der gleichen oder einer ähnlichen Query) fehlende Statistiken als Ursache der Abweichung bestimmt wurden.
  • HAS_STATS: gibt an, dass die zusätzlichen Statistiken genügen, um die Ausführung zu optimieren.
  • PERMANENT: gibt an, dass die zusätzlichen Statistiken die Fehler in den Schätzungen nicht vermeiden - und deshalb ignoriert werden können.
Für 12.1.0.2 gibt es nur noch zwei Status-Angaben:
  • USABLE: fasst die bisher unter NEW, MISSING_STATS und PERMANENT aufgeschlüsselten Angaben zusammen. Die Semantik ist etwa: die Direktive muss vom Optimizer untersucht werden, aber das Problem ist noch nicht behoben.
  • SUPERSEDED: gibt an, dass die erforderlichen Statistiken erzeugt wurden (HAS_STATS) oder dass die Direktive durch eine andere Direktive überflüssig gemacht wurde.
Demnach ist die Detaillierung der Angaben in 12.1.0.2 geringer, aber die Status-Angaben der älteren Version sind in der (XML-) Spalte NOTES weiterhin verfügbar.

Mein Eindruck ist dabei, dass es sich um ein interessantes Feature mit großem Entwicklungspotential handelt - aber auch um eines, dessen Dokumentation noch eine ganze Menge offener Fragen lässt.

Freitag, November 14, 2014

Materialized Views für postgres

Mit Materialized Views hat sich mein erster Blog-Eintrag zu postgres im Jahr 2005 beschäftigt. Erst seit Version 9.3 gibt es reguläre MViews in postgres, aber Craig Kerstiens urteilt knapp: 
The problem with them is they were largely unusable. This was because they 1. Didn’t auto-refresh and 2. When you did refresh them it would lock the table while it ran the refresh making it unreadable during that time.
In 9.4 ist zumindest das zweite Problem behandelt: mit Hilfe des Befehls REFRESH MATERIALIZED VIEW CONCURRENTLY kann man den Neuaufbau der MView durchführen, ohne massive Locks zu halten - allerdings muss dazu ein unique index existieren, der sämtliche Datensätze enthält.

Dienstag, November 11, 2014

Import Foreign Schema für Postgres FDW

Vor ziemlich genau einem Jahr habe ich an dieser Stelle über database links und foreign data wrapper (FDW) geschrieben und mich darüber beklagt, wie unhandlich sich die Einbindung der Objekte unterschiedlicher Datenbanken eines Clusters in einer gemeinsamen Analyse-Datenbank gestaltet, weil man für jede Tabelle eine explizite Definition der Spalten angeben muss, was sich nicht ohne größeren Aufwand automatisieren lässt. In Version 9.5 soll es dafür nun eine Abhilfe geben: Michael Paquier beschreibt das FDW-Kommando IMPORT FOREIGN SCHEMA, das die Einbindung eines kompletten Schemas einer remote-Datenbank ermöglicht: "This command provides an automated way to create foreign table definitions that match remote tables, thereby reducing tedium and chances for error." Mein herzlicher Dank für diese Ergänzung an die postgres-Entwicklung.

Montag, November 10, 2014

SQL Profiles, Hints und Optimizer-Transformationen

Die Möglichkeiten des Plan-Managements sind in aktuellen Oracle-Releases bekanntlich recht umfangreich: neben den historischen Stored Outlines gibt es SQL Plan Baselines, SQL Profiles und SQL Patches. Jede dieser Techniken hat ihre Anwendungsbereiche - und ihre Beschränkungen. Franck Pachot beschäftigt sich in seinem jüngsten Artikel mit SQL Profiles und untersucht die Frage, wieso ein Profile für eine einzelne Tabelle mehrere OPT_ESTIMATE Hints enthalten kann (die die Cardinality-Schätzungen des Optimizers modifizieren). Zur Erinnerung noch mal eine minimale Definition für SQL Profiles: sie sind eine Sammlung von ergänzenden Hints, die der Optimizer für eine Query anlegt, wenn man ihm die Zeit lässt, seine Arbeit in Ruhe durchzuführen - statt unter dem üblichen OLTP-Zeitdruck; für Data Warehouse Systeme (oder wie man die Nicht-OLTP-Systeme sonst einordnen mag) kann das eine ziemlich gut Idee sein, wenn das Parsen gegenüber der Ausführung einer Query kaum ins Gewicht fällt. Aber zurück zum Artikel: durch einen Blick ins CBO-Trace wird klar, dass das mehrfache Erscheinen der Hints den unterschiedlichen Transformationen geschuldet ist, die der Optimizer im Rahmen seiner Optimierungsversuche durchspielt. Ausgehend von dieser Beobachtung kommt der Herr Pachot zu folgendem Schluss:
That observation brings me to the following: what happens to your profiles when you upgrade to a version that brings new optimizer transformations? The optimizer will compare some plans with adjusted cardinalities, compared with some plans with non-adjusted cardinalites. And that is probably not a good idea.
In my opinion, SQL Profiles are just like hints: a short term workaround that must be documented and re-evaluated at each upgrade.
Und das würde ich grundsätzlich unterschreiben - wobei ich mir vorstellen könnte, dass es Umgebungen gibt, in denen es sinnvoll wäre, wenn man dem Optimizer grundsätzlich mehr Zeit zum Nachdenken gibt; aber das wäre wieder ein anderes Feature.