Firebird 5.0.1 Verbesserungen im Optimierer

(c) D.Simonov, IBSurgeon, 21-Aug-2024

Kürzlich wurde ein Point-Release des Firebird 5.0 DBMS veröffentlicht: Firebird 5.0.1. Neben der Behebung von Fehlern wurde eine neue experimentelle Optimierungsfunktion hinzugefügt, die in diesem Artikel besprochen wird.

Konvertierung von Unterabfragen in ANY/SOME/IN/EXISTS in Semi-Join

Ein Semi-Join ist eine Operation, die zwei Relationen verbindet und nur Zeilen aus einer der Relationen zurückgibt, ohne den gesamten Join auszuführen. Im Gegensatz zu anderen Join-Operatoren gibt es keine explizite Syntax, um anzugeben, ob ein Semi-Join durchgeführt werden soll. Sie können jedoch einen Semi-Join mithilfe von Unterabfragen in ANY/SOME/IN/EXISTS durchführen.

Traditionell transformiert Firebird Unterabfragen in ANY/SOME/IN-Prädikaten in korrelierte Unterabfragen im EXISTS-Prädikat und führt die Unterabfrage in EXISTS für jeden Datensatz der äußeren Abfrage aus. Bei der Ausführung einer Unterabfrage innerhalb eines EXISTS-Prädikats wird die FIRST-ROWS-Strategie verwendet, und die Ausführung stoppt sofort, nachdem der erste Datensatz zurückgegeben wurde.

Ab Firebird 5.0.1 können Unterabfragen in ANY/SOME/IN/EXISTS-Prädikaten in Semi-Joins konvertiert werden. Diese Funktion ist standardmäßig deaktiviert und kann durch Setzen des Konfigurationsparameters SubQueryConversion auf true in der Datei firebird.conf oder database.conf aktiviert werden.

Diese Funktion ist experimentell und daher standardmäßig deaktiviert. Sie können sie aktivieren und Ihre Abfragen mit Unterabfragen in ANY/SOME/IN/EXISTS-Prädikaten testen. Wenn die Leistung besser ist, lassen Sie sie aktiviert; andernfalls setzen Sie den Parameter SubQueryConversion auf den Standardwert ( false) zurück.
Der Standardwert für den Konfigurationsparameter SubQueryConversion kann in Zukunft geändert oder der Parameter vollständig entfernt werden. Dies wird geschehen, sobald die neue Vorgehensweise sich in den meisten Fällen als optimaler erwiesen hat.


	Diese Funktion ist experimentell und daher standardmäßig deaktiviert. Sie können sie aktivieren und Ihre Abfragen mit Unterabfragen in ANY/SOME/IN/EXISTS-Prädikaten testen. Wenn die Leistung besser ist, lassen Sie sie aktiviert; andernfalls setzen Sie den Parameter `SubQueryConversion` auf den Standardwert ( `false`) zurück. Der Standardwert für den Konfigurationsparameter `SubQueryConversion` kann in Zukunft geändert oder der Parameter vollständig entfernt werden. Dies wird geschehen, sobald die neue Vorgehensweise sich in den meisten Fällen als optimaler erwiesen hat.

Im Gegensatz zur direkten Ausführung von ANY/SOME/IN/EXISTS auf Unterabfragen, d.h. als korrelierte Unterabfragen, bietet die Ausführung als Semi-Joins mehr Raum für Optimierungen. Semi-Joins können mit verschiedenen Algorithmen wie Hash Join (semi) oder Nested Loop Join (semi) durchgeführt werden, während korrelierte Unterabfragen immer für jeden Datensatz der äußeren Abfrage ausgeführt werden.

Lassen Sie uns versuchen, diese Funktion zu aktivieren, indem wir den Parameter SubQueryConversion in der Datei firebird.conf auf true setzen. Jetzt führen wir einige Experimente durch.

Führen wir die folgende Abfrage aus:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2
  AND H.CODE_HORSE IN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  )

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap And
                            -> Bitmap
                                -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                            -> Bitmap
                                -> Index "FK_HORSE_SEX" Range Scan (full match)
                -> Record Buffer (record length: 41)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
                                -> Bitmap
                                    -> Index "FK_COVER_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552356752
Delta memory = 352
Max memory = 552567920
Elapsed time = 0.045 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 43984
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1516|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Im Ausführungsplan sehen wir eine neue Join-Methode Hash Join (semi). Das Ergebnis der Unterabfrage in IN wurde gepuffert, was im Plan als Record Buffer (record length: 41) sichtbar ist. Das heißt, in diesem Fall wurde die Unterabfrage in IN einmal ausgeführt, ihr Ergebnis wurde im Speicher der Hash-Tabelle gespeichert, und dann suchte die äußere Abfrage einfach in dieser Hash-Tabelle.

Zum Vergleich führen wir dieselbe Abfrage mit deaktivierter Unterabfrage-zu-Semi-Join-Konvertierung aus.

Code

Sub-query
    -> Filter
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Range Scan (full match)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                    -> Bitmap
                        -> Index "FK_HORSE_SEX" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552046496
Delta memory = 352
Max memory = 552135600
Elapsed time = 0.395 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 186891
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |      297|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Der Ausführungsplan zeigt, dass die Unterabfrage für jeden Datensatz der Hauptabfrage ausgeführt wird, dabei aber einen zusätzlichen Index FK_COVER_FATHER verwendet. Dies ist auch in den Ausführungsstatistiken sichtbar: Die Anzahl der Fetches ist 4-mal größer, die Ausführungszeit ist fast 4-mal schlechter.


	Der Leser könnte fragen: Warum zeigt der Hash-Semi-Join 5-mal mehr Index-Lesevorgänge der COVER-Tabelle, ist aber ansonsten besser? Tatsache ist, dass Index-Lesevorgänge in den Statistiken die Anzahl der mit dem Index gelesenen Datensätze anzeigen, nicht die Gesamtzahl der Indexzugriffe, von denen einige überhaupt keine Datensätze abrufen - aber diese Zugriffe sind nicht kostenlos.

Was ist passiert? Um die Transformation von Unterabfragen besser zu verstehen, führen wir einen imaginären Semi-Join-Operator “SEMI JOIN” ein. Wie bereits erwähnt, ist diese Art von Join in der SQL-Sprache nicht repräsentiert. Unsere Abfrage mit dem IN-Operator wurde in eine äquivalente Form transformiert, die wie folgt geschrieben werden kann:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2

Jetzt ist es klarer. Dasselbe gilt für Unterabfragen mit EXISTS. Schauen wir uns ein weiteres Beispiel an:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.CODE_MOTHER = H.CODE_MOTHER
  )

Derzeit ist es nicht möglich, ein solches EXISTS mit IN zu schreiben. Sehen wir uns an, wie es ohne Transformation in einen Semi-Join implementiert wird.

Code

Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap And
                -> Bitmap
                    -> Index "FK_COVER_MOTHER" Range Scan (full match)
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                91908

Current memory = 552240400
Delta memory = 352
Max memory = 554680016
Elapsed time = 19.083 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 935679
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    91908|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Sehr langsam. Jetzt setzen wir SubQueryConversion = true und führen die Abfrage erneut aus.

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap
                            -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                -> Record Buffer (record length: 49)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap
                                -> Index "FK_COVER_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                91908

Current memory = 552102000
Delta memory = 352
Max memory = 561520736
Elapsed time = 0.208 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 248009
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |   140254|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Die Abfrage wurde 100-mal schneller ausgeführt! Wenn wir sie mit unserem fiktiven SEMI-JOIN-Operator umschreiben, sieht die Abfrage wie folgt aus:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT
      COVER.CODE_FATHER,
      COVER.CODE_MOTHER
    FROM COVER
  ) TMP ON TMP.CODE_FATHER = H.CODE_FATHER AND TMP.CODE_MOTHER = H.CODE_MOTHER
WHERE H.CODE_DEPARTURE = 1

Kann jede korrelierte Unterabfrage in IN/EXISTS in einen Semi-Join konvertiert werden? Nein, nicht jede. Wenn die Unterabfrage beispielsweise FETCH/FIRST/SKIP/ROWS-Filter enthält, kann die Unterabfrage nicht in einen Semi-Join konvertiert werden und wird als korrelierte Unterabfrage ausgeführt. Hier ist ein Beispiel für eine solche Abfrage:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_HORSE
    OFFSET 0 ROWS
  )

Hier ändert die Phrase OFFSET 0 ROWS die Semantik der Abfrage nicht, und das Ergebnis ihrer Ausführung ist dasselbe wie ohne sie. Schauen wir uns den Plan und die Statistiken dieser Abfrage an.

Code

Sub-query
    -> Skip N Records
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================

10971

Aktueller Speicher = 551912944
Delta-Speicher = 288
Maximaler Speicher = 552002112
Verstrichene Zeit = 0,201 Sek.
Puffer = 32768
Lesevorgänge = 0
Schreibvorgänge = 0
Abrufe = 408988
Statistik pro Tabelle:
--------------------------------+---------+---------+---------+---------+---------+
 Tabellenname                   | Natürlich | Index   | Einfügen | Aktualis. | Löschen |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Wie Sie sehen können, erfolgte die Umwandlung in einen Semi-Join nicht. Entfernen wir nun OFFSET 0 ROWS und erfassen die Statistiken erneut.

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Tabelle "HORSE" als "H" Zugriff per ID
                        -> Bitmap
                            -> Index "FK_HORSE_DEPARTURE" Bereichsscan (volle Übereinstimmung)
                -> Datensatzpuffer (Datensatzlänge: 33)
                    -> Tabelle "COVER" Vollscan

                COUNT
=====================
                10971

Aktueller Speicher = 552112128
Delta-Speicher = 288
Maximaler Speicher = 585044592
Verstrichene Zeit = 0,405 Sek.
Puffer = 32768
Lesevorgänge = 0
Schreibvorgänge = 0
Abrufe = 854841
Statistik pro Tabelle:
--------------------------------+---------+---------+---------+---------+---------+
 Tabellenname                   | Natürlich | Index   | Einfügen | Aktualis. | Löschen |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |   722465|         |         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Hier erfolgte die Umwandlung in einen Semi-Join, und wie wir sehen können, ist die Ausführungszeit schlechter geworden. Der Grund dafür ist, dass der Optimierer derzeit keine Kostenschätzung zwischen den Join-Algorithmen Hash Join (semi) und Nested Loop Join (semi) unter Verwendung eines Indexes hat. Daher gilt die Regel: Wenn die Join-Bedingung nur Gleichheit enthält, wird der Algorithmus Hash Join (semi) gewählt, andernfalls werden die IN/EXISTS-Unterabfragen wie gewohnt ausgeführt.

Nun deaktivieren wir die Semi-Join-Umwandlung und betrachten die Ausführungsstatistiken.

Code

Sub-query
    -> Filter
        -> Tabelle "COVER" Zugriff per ID
            -> Bitmap
                -> Index "FK_COVER_FATHER" Bereichsscan (volle Übereinstimmung)
Select Expression
    -> Aggregate
        -> Filter
            -> Tabelle "HORSE" als "H" Zugriff per ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Bereichsscan (volle Übereinstimmung)

                COUNT
=====================
                10971

Aktueller Speicher = 551912752
Delta-Speicher = 288
Maximaler Speicher = 552001920
Verstrichene Zeit = 0,193 Sek.
Puffer = 32768
Lesevorgänge = 0
Schreibvorgänge = 0
Abrufe = 408988
Statistik pro Tabelle:
--------------------------------+---------+---------+---------+---------+---------+
 Tabellenname                   | Natürlich | Index   | Einfügen | Aktualis. | Löschen |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Wie Sie sehen können, sind die Abrufe exakt gleich wie im Fall, wenn die Unterabfrage die Klausel OFFSET 0 ROWS enthielt, und die Ausführungszeit unterscheidet sich innerhalb der Fehlertoleranz. Das bedeutet, dass Sie die Klausel OFFSET 0 ROWS als Hinweis verwenden können, um die Semi-Join-Umwandlung zu deaktivieren.

Nun betrachten wir Fälle, in denen in Unterabfragen eine andere korrelierte Bedingung als Gleichheit und IS NOT DISTINCT FROM verwendet wird.

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.BYDATE > H.BIRTHDAY
  )

Code

Sub-query
    -> Filter
        -> Tabelle "COVER" Zugriff per ID
            -> Bitmap
                -> Index "COVER_IDX_BYDATE" Bereichsscan (untere Grenze: 1/1)
Select Expression
    -> Aggregate
        -> Filter
            -> Tabelle "HORSE" als "H" Zugriff per ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Bereichsscan (volle Übereinstimmung)

Wie ich oben sagte, erfolgte keine Umwandlung in einen Semi-Join; die Unterabfrage wird für jeden Datensatz der Hauptabfrage ausgeführt.

Setzen wir die Experimente fort und schreiben eine Abfrage mit Gleichheit und einem weiteren Prädikat außer Gleichheit.

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
  )

Code

Select Expression
    -> Aggregate
        -> Nested Loop Join (semi)
            -> Filter
                -> Tabelle "HORSE" als "H" Zugriff per ID
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Bereichsscan (volle Übereinstimmung)
            -> Filter
                -> Filter
                    -> Tabelle "COVER" Zugriff per ID
                        -> Bitmap
                            -> Index "COVER_IDX_BYDATE" Bereichsscan (untere Grenze: 1/1)

Hier sehen wir im Plan die erste Verwendung der Join-Methode Nested Loop Join (semi), aber leider ist dieser Plan schlecht, weil der Index FK_COVER_FATHER nicht verwendet wird. Sie werden mit einer solchen Abfrage keine Ergebnisse erhalten. Dies kann mit dem Hinweis OFFSET 0 ROWS behoben werden.

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
    OFFSET 0 ROWS
  )

Code

Sub-query
    -> Überspringe N Datensätze
        -> Filter
            -> Tabelle "COVER" Zugriff per ID
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Bereichsscan (volle Übereinstimmung)
Select Expression
    -> Aggregate
        -> Filter
            -> Tabelle "HORSE" als "H" Zugriff per ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Bereichsscan (volle Übereinstimmung)

                COUNT
=====================
                72199

Aktueller Speicher = 554017824
Delta-Speicher = 320
Maximaler Speicher = 554284480
Verstrichene Zeit = 45,548 Sek.
Puffer = 32768
Lesevorgänge = 0
Schreibvorgänge = 0
Abrufe = 84145713
Statistik pro Tabelle:
--------------------------------+---------+---------+---------+---------+---------+
 Tabellenname                   | Natürlich | Index   | Einfügen | Aktualis. | Löschen |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         | 75894621|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Nicht die beste Ausführungszeit, aber in diesem Fall haben wir zumindest ein Ergebnis erhalten.

Somit ermöglicht die Umwandlung von Unterabfragen in ANY/SOME/IN/EXISTS in Semi-Joins in einigen Fällen eine erhebliche Beschleunigung der Abfrageausführung, aber derzeit ist diese Funktion noch unvollkommen und daher standardmäßig deaktiviert. In Firebird 6.0 wird versucht, eine Kostenschätzung für diese Funktion hinzuzufügen sowie eine Reihe anderer Mängel zu beheben. Darüber hinaus plant Firebird 6.0 die Umwandlung von Unterabfragen ALL/NOT IN/NOT EXISTS in Anti-Joins.

Zum Abschluss der Betrachtung der Ausführung von Unterabfragen in IN/EXISTS möchte ich anmerken, dass, wenn Sie eine Abfrage der Form haben

Code

SELECT ...
FROM T1
WHERE  IN (SELECT field FROM T2 ...)

oder

Code

SELECT ...
FROM T1
WHERE EXISTS (SELECT ... FROM T2 WHERE T1. = T2.field)

dann sind solche Abfragen fast immer effizienter auszuführen als

Code

SELECT ...
FROM
  T1
  JOIN (SELECT DISTINCT field FROM T2) tmp ON tmp.field = T1.

Lassen Sie mich ein klares Beispiel geben:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_HORSE IN (
  SELECT
    CODE_FATHER
  FROM COVER
  WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
)

Ausführungsplan und Statistiken unter Verwendung von Hash Join (semi)

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Tabelle "HORSE" als "H" Vollscan
                -> Datensatzpuffer (Datensatzlänge: 41)
                    -> Filter
                        -> Tabelle "COVER" Zugriff per ID
                            -> Bitmap
                                -> Index "IDX_COVER_BYYEAR" Bereichsscan (volle Übereinstimmung)

                COUNT
=====================
                 1616

Aktueller Speicher = 554176768
Delta-Speicher = 288
Maximaler Speicher = 555531328
Verstrichene Zeit = 0,229 Sek.
Puffer = 32768
Lesevorgänge = 0
Schreibvorgänge = 0
Abrufe = 569683
Statistik pro Tabelle:
--------------------------------+---------+---------+---------+---------+---------+
 Tabellenname                   | Natürlich | Index   | Einfügen | Aktualis. | Löschen |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     6695|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Ziemlich schnell, aber die Tabelle HORSE wird vollständig gelesen.

Ausführungsplan und Statistiken mit klassischer Unterabfrageausführung

Code

Sub-query
    -> Filter
        -> Filter
            -> Tabelle "COVER" Zugriff per ID
                -> Bitmap Und
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Bereichsscan (volle Übereinstimmung)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Bereichsscan (volle Übereinstimmung)
Select Expression
    -> Aggregate
        -> Filter
            -> Tabelle "HORSE" als "H" Vollscan

                COUNT
=====================
                 1616

Aktueller Speicher = 553472512
Delta-Speicher = 288
Maximaler Speicher = 553966592
Verstrichene Zeit = 6,862 Sek.
Puffer = 32768
Lesevorgänge = 0
Schreibvorgänge = 0
Abrufe = 2462726
Statistik pro Tabelle:
--------------------------------+---------+---------+---------+---------+---------+
 Tabellenname                   | Natürlich | Index   | Einfügen | Aktualis. | Löschen |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1616|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Sehr langsam. Die Tabelle HORSE wird vollständig gescannt, und die Unterabfrage wird mehrfach ausgeführt - für jeden Datensatz in der Tabelle HORSE.

Und nun eine schnelle Option mit DISTINCT

sql

SELECT
  COUNT(*)
FROM
  HORSE H
  JOIN (
      SELECT
        DISTINCT
        CODE_FATHER
      FROM COVER
      WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE

Code

Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Unique Sort (Datensatzlänge: 44, Schlüssellänge: 12)
                -> Filter
                    -> Tabelle "COVER" als "TMP COVER" Zugriff per ID
                        -> Bitmap
                            -> Index "IDX_COVER_BYYEAR" Bereichsscan (volle Übereinstimmung)
            -> Filter
                -> Tabelle "HORSE" als "H" Zugriff per ID
                    -> Bitmap
                        -> Index "PK_HORSE" Eindeutiger Scan

                COUNT
=====================
                 1616

Aktueller Speicher = 554349728
Delta-Speicher = 320
Maximaler Speicher = 555531328
Verstrichene Zeit = 0,011 Sek.
Puffer = 32768
Lesevorgänge = 0
Schreibvorgänge = 0
Abrufe = 14954
Statistik pro Tabelle:
--------------------------------+---------+---------+---------+---------+---------+
 Tabellenname                   | Natürlich | Index   | Einfügen | Aktualis. | Löschen |
--------------------------------+---------+---------+---------+---------+---------+

```markdown
COVER                           |         |     6695|         |         |         |
HORSE                           |         |     1616|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Keine unnötigen Lesevorgänge, die Abfrage wird sehr schnell ausgeführt. Daraus folgt die Schlussfolgerung - schauen Sie sich immer den Ausführungsplan von Unterabfragen in IN/EXISTS/ANY/SOME an und prüfen Sie alternative Varianten der Abfrageformulierung.

Code