von Markus Winand.

Was weißt du über Oracle Indizierung?


Die meisten Fragen folgen einem einfachen Muster: Sie zeigt einen Index und eine Abfrage. Die Frage ist dann, ob Index und Abfrage zusammenpassen, oder nicht.

Falls du eine Abfrage ändern würdest, um die Performance zu verbessern, muss die neue Abfrage dasselbe Ergebnis liefern. Das bedeutet auch alle Spalten, wenn select * genutzt wird.

Bitte lade deine Antworten hoch

100% optional und anonym: lade deine Antworten hoch.

(Datenschutz)

Frage 5

Diese Frage ist anders. Sieh dir zuerst den Index und die ursprüngliche Abfrage an:

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
 GROUP BY date_column

Gehe davon aus, dass die Abfrage einige hundert Zeilen liefert.

Um eine neue Anforderung umzusetzen, wird eine weitere Bedingung (b = 1) in die where-Klausel aufgenommen:

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
   AND b = 1
 GROUP BY date_column

Wie wird sich diese Änderung auf die Performance auswirken?

  • Falsch! Die Abfrage wird langsamer.

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan (kein TABLE ACCESS BY INDEX ROWID im Ausführugnsplan) ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusäzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan

  • Falsch. Die Information oben lässt den rückschluss zu, dass die Abfrage langsamer wird.

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan (kein TABLE ACCESS BY INDEX ROWID im Ausführugnsplan) ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusäzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan

  • Richtig: die Abfrage wird langsamer

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan (kein TABLE ACCESS BY INDEX ROWID im Ausführugnsplan) ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusäzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan

  • Flasch: die Abfrage wird langsamer!

    Der Index beinhaltet alle Spalten der ursprünglichen Abfrage. Daher konnte sie als Index-Only Scan (kein TABLE ACCESS BY INDEX ROWID im Ausführugnsplan) ausgeführt werden. Ein Zugriff auf die eigentliche Tabelle war nicht nötig.

    Jeder Zugriff auf eine Spalte, die nicht im Index ist, verhindert diese Optimierung. Die Datenbank muss dann für jede Zeile, die der ursprünglichen where-Klausel entspricht, in die Tabelle gehen, und nachsehen ob diese Zeile auch dem neuen where-Filter genügt. Selbst wenn der neue Filter alle Zeilen entfernt, geschieht das erst, nachdem ein zusäzlicher Aufwand angefallen ist. Obwohl die Gruppierung dann schneller geht (weniger Zeilen) kann das den Mehraufwand des Tabellenzugriffes nicht ausgleichen.

    Tipp

    Verwende einen Index-Only Scan für Abfragen über viele Zeilen aber wenige Spalten.

    Vermeide select * um die Chance auf einen Index-Only Scan zu verbessern.

    Siehe auch: Index-Only Scan

Frage 4

Passen Index und Abfrage zueinander oder nicht?

CREATE INDEX tbl_idx ON tbl (text)
SELECT *
  FROM tbl
 WHERE text LIKE '%TERM%'

Deine Antwort:

  • Like-Ausdrücke, die mit einem Wildcard-Zeichen (% oder _) beginnen, können einen normalen Index nicht effizient nutzen.

    Es gibt hier keine einfache Methode, diese Abfrage zu optimieren. Oft kann man dem Problem Herr werden, weil es noch andere Zugriffswege gibt (d. h. andere where-Filter). Wenn nicht, könnte man einen Volltextindex erwägen – dabei ist jedoch zu beachten, dass Volltextsuche nicht dieselbe Funktion wie like anbietet.

    Siehe auch: Eine bildhafte Erklärung, wann like langsam ist

  • Like-Ausdrücke, die mit einem Wildcard-Zeichen (% oder _) beginnen, können einen normalen Index nicht effizient nutzen.

    Es gibt hier keine einfache Methode, diese Abfrage zu optimieren. Oft kann man dem Problem Herr werden, weil es noch andere Zugriffswege gibt (d. h. andere where-Filter). Wenn nicht, könnte man einen Volltextindex erwägen – dabei ist jedoch zu beachten, dass Volltextsuche nicht dieselbe Funktion wie like anbietet.

    Siehe auch: Eine bildhafte Erklärung, wann like langsam ist

Frage 3

Passt der Index zu beiden Abfragen?

CREATE INDEX tbl_idx ON tbl (a, b)
SELECT *
  FROM tbl
 WHERE a = 123
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 123

Deine Antwort:

  • Der Index passt nur zur ersten Abfrage. Die Zweite Abfrage kann den Index nicht effizient nutzen.

    Grundsätzlich kann der Index immer noch komplett gelesen werden. Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Dreht man die Spalten im Index um, wird der Index für beide Abfragen nutztbar – ohne zusätchlichen Wartungsaufwand. Der Index sollte daher wie folgt aussehen:

    CREATE INDEX tbl_idx ON tbl (b, a)

    Tipp

    Indizes können nur von links nach rechts effizient genutzt werden. Wenn der erste Indexspalte nicht in der where-Klausel ist, ist der Index nutzlos.

    Lies mehr über mehrspaltige Indizes.

  • Der Index passt nur zur ersten Abfrage. Die Zweite Abfrage kann den Index nicht effizient nutzen.

    Grundsätzlich kann der Index immer noch komplett gelesen werden. Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Dreht man die Spalten im Index um, wird der Index für beide Abfragen nutztbar – ohne zusätchlichen Wartungsaufwand. Der Index sollte daher wie folgt aussehen:

    CREATE INDEX tbl_idx ON tbl (b, a)

    Tipp

    Indizes können nur von links nach rechts effizient genutzt werden. Wenn der erste Indexspalte nicht in der where-Klausel ist, ist der Index nutzlos.

    Lies mehr über mehrspaltige Indizes.

Frage 2

Passen Index und Abfrage zueinander oder nicht?

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT *
  FROM (
        SELECT id, date_column
          FROM tbl
         WHERE a = 123
         ORDER BY date_column DESC
       )
 WHERE rownum <= 1

Deine Antwort:

  • Die Abfrage kann als indizierte Top-N-Abfrage laufen. Dafür muss nur der B-Baum durchwandert werden (log(n)) und ein einziges mal auf die Tabelle zugegriffen werden.

    Dafür ist es essentiell, dass der Index die where und order by-Klauseln unterstützt. Die Datenbank nutzt den Index, um den letzten passenden Eintrag zu finden, und nimmt diese Zeile gleich als Ergebnis. Obwohl es eine order by-Klausel gibt, muss nicht sortiert werden.

    Tipp

    Verwende dieses Muster für Abfrgen nach dem letzten, besten, … Eintrag.

    Lies mehr über Abfragen nach den ersten N Zeilen.

  • Die Abfrage kann als indizierte Top-N-Abfrage laufen. Dafür muss nur der B-Baum durchwandert werden (log(n)) und ein einziges mal auf die Tabelle zugegriffen werden.

    Dafür ist es essentiell, dass der Index die where und order by-Klauseln unterstützt. Die Datenbank nutzt den Index, um den letzten passenden Eintrag zu finden, und nimmt diese Zeile gleich als Ergebnis. Obwohl es eine order by-Klausel gibt, muss nicht sortiert werden.

    Tipp

    Verwende dieses Muster für Abfrgen nach dem letzten, besten, … Eintrag.

    Lies mehr über Abfragen nach den ersten N Zeilen.

Frage 1

Passen Index und Abfrage zueinander oder nicht?

CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(*)
  FROM tbl
 WHERE TO_CHAR(date_column, 'YYYY') = '2016'

Deine Antwort:

  • Durch die Anwendung der Funktion wird der Index für diese Abfrage nutzlos.

    Grundsätzlich kann der Index immer noch komplett gelesen werden (full index scan). Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Tipp

    Schreibe Suchen nach zusammenhängenden Perioden als explizite Bereichsbedingung.

    Dieser Artikel beschreibt das genauer. Beachte, dass Funktions-basierende Indizierung nicht die empfohlene Lösung für solche Suchen ist.

    Besser ist es, den Index zu behalten und die Abfrage umzuschreiben:

    SELECT COUNT(*)
      FROM tbl
     WHERE date_column >= DATE'2016-01-01'
       AND date_column <  DATE'2017-01-01'
  • Durch die Anwendung der Funktion wird der Index für diese Abfrage nutzlos.

    Grundsätzlich kann der Index immer noch komplett gelesen werden (full index scan). Das ist jedoch sehr ineffizient und kann nicht als Lösung angesehen werden.

    Tipp

    Schreibe Suchen nach zusammenhängenden Perioden als explizite Bereichsbedingung.

    Dieser Artikel beschreibt das genauer. Beachte, dass Funktions-basierende Indizierung nicht die empfohlene Lösung für solche Suchen ist.

    Besser ist es, den Index zu behalten und die Abfrage umzuschreiben:

    SELECT COUNT(*)
      FROM tbl
     WHERE date_column >= DATE'2016-01-01'
       AND date_column <  DATE'2017-01-01'

Dein Ergebnis

Du hast ${RESULT} von 5 richtig Beantwortet.

Bitte lies die Erklärungen unten durch. Auch wenn du richtig geantwortet hast (aus dem falschen Grund?). Wenn du die Beispiele selbst ausprobieren möchtest, findest du die nötigen create-Anweisungen hier.

Ich würde mich freuen, wenn du mir deine Antworten zur Analyse wie in diesem Artikel überlässt.

(Datenschutz)

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Kaufen Sie sein Buch bei Amazon

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz