Performance by Example: ORDER BY INDEX DESC

Aufgabenstellung / Problemstellung

In einer Applikation sollen die letzten (aktuellsten) Werte einer Tabelle angezeigt werden. Die Tabelle selbst ist viele 100 GB groß, und die Applikation bearbeitet immer nur einige wenige Datensätze.

Die Abfrage in der Applikation sieht vereinfacht so aus:

SELECT * 
  FROM sehr_grosse_tabelle 
 ORDER BY pk_spalte DESC;

Die Werte für die Spalte pk_spalte stammen aus einer aufsteigenden Sequence. Es ist die einzige Spalte im Primary Key und somit automatisch indiziert. Die Query hat keine WHERE-Bedingung, weil die Applikation die Abfrage nach einigen Sätzen abbricht.

Was ist das Problem bei dieser Abfrage?

Da es keine WHERE Klausel gibt, muss der CBO – Cost Based Optimizer – annehmen, dass die Applikation ALLE Daten abfragen wird. In diesem Fall ist es natürlich sinnvoll, wenn einfach alle Daten gelesen und sortiert werden. Der CBO kann nicht wissen, dass nur wenige Datensätze benötigt werden.

Wie kann der CBO veranlasst werden, einen Ausführungsplan zu erzeugen, der der Realität entspricht?

Hinweis: Nicht jeder der Vorschläge wird 100% sicher zum gewünschten Erfolg führen, da der CBO auf Grund der vorhandenen Informationen möglicherweise einen „besseren“ Ausführungsplan wählt.

Einstellung von OPTIMIZER_MODE

Der Default Wert des Parameters OPTIMIZER_MODE ist ALL_ROWS. Das bedeutet, dass der CBO davon ausgehen soll, dass das GESAMTE Ergebnis benutzt wird. Das ist auch der Grund, warum sich der CBO in diesem Fall für einen Full Table Scan und Sort auf die Tabelle entscheidet. Bei OPTIMIZER_MODE=FIRST_ROWS wird der CBO in diesem Beispiel voraussichtlich den passenden Index nutzen!

Die Einstellung für den OPTIMIZER_MODE kann auf verschiedenen Ebenen verändert werden:

Vom DBA systemweit

Für die ganze Instanz – sprich für alle Benutzer, die mit der Datenbank Instanz verbunden sind:

ALTER SYSTEM SET OPTIMIZER_MODE=FIRST_ROWS;

Vom Entwickler für die eigene Session

Soll nur eine bestimmte Session umgestellt werden, kann dies mit folgendem Befehl erfolgen:

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;

Vom Entwickler für ein bestimmtes Statement

Soll nur ein bestimmtes Statement mit FIRST_ROWS Einstellung laufen, so geht das mittels Hint:

SELECT /*+ FIRST_ROWS(1) */ * 
  FROM sehr_grosser_tabelle 
 ORDER BY pk_spalte DESC;

Verbesserung des Statements ohne Änderung an OPTIMIZER_MODE

Es gibt aber auch noch eine andere – vermutlich sinnvollere – Lösung, ohne den OPTIMIZER_MODE zu verändern. Man kann das Statement so umformulieren, dass der CBO erkennt, dass nur wenige Datensätze benötigt werden.

Der Nachteil dabei ist, dass die Queries dann nur eine maximale Anzahl von Datensätzen liefern werden.

TOP n-Query

Die TOP n-Query beherrscht Oracle schon recht lange. Sie ist dafür gedacht, dass nur die ersten X Werte – in unserem Beispiel 10 – eines Endergebnisses ermittelt werden.

SELECT * 
  FROM (SELECT * 
          FROM sehr_grosser_tabelle 
         ORDER BY pk_spalte DESC)
 WHERE ROWNUM <=10;

Hier kann der Entwickler durchaus auch auf mehr Records (beispiel 100) einschränken, der CBO wird trotzdem den vorhandenen Index nutzen, sofern die Tabelle entsprechend groß ist.

fetch first 10 rows only

Bei aktuelleren Oracle Versionen (ab 12.1) gibt es eine, dem offiziellen SQL Standard entsprechende, Lösung:

SELECT * 
  FROM sehr_grosser_tabelle 
 ORDER BY pk_spalte DESC 
 FETCH FIRST 10 ROWS ONLY;

Zusammenfassung

Der CBO macht oft genau das, was man ihm sagt. Das ist nicht immer auch das, was man will! Einiges an Basiswissen um den CBO ist hierbei von erheblichem Vorteil, besonders für Entwickler.