Zugriffsoperationen des Ausführungsplans (Oracle)

Diese Operationen gehören zu den Ausführungsplanoperationen? und spezifzieren die Art des Zugriffs auf die persistent gespeicherten Datensätze in der Datenbasis. Hier geht es um die Fragestellung, wie dieser performanter gestaltet werden kann, also mit möglichst wenigen ressourcen- und zeitaufwändigen I/O-Operationen, die zu den teuersten Aktionen eines Rechners gehören. Eine Möglichkeit kann die Verwenden eines Index statt dem Lesen der gesamten Tabelle mittels eines sogenannten "Full Table Scan" sein. Oder z.B. das Vermeiden eines zusätzlichen Tabellenzugrifffs, in dem ein Index alle benötigten Spalten enthält. Auf der Basis der verschiedenen Ausführungsplanoperationen? erstellt der ORACLE-Optimizer dann verschiedene Ausführungspläne?, um sich für eine kostengünstige Variante zu entscheiden.

Oracle kennt die folgenden Zugriffsoperationen:

TABLE ACCESS BY INDEX ROWID

Bei der Tabellenzugriffsmethode TABLE ACCESS BY INDEX ROWID werden Datensätze anhand ihrer physikalischen Adresse (ROWID?) geladen. Bei dieser Zugriffsform ermittelt der ORACLE-Prozess den zu lesenden Block und die Nummer des Datensatzes über die ROWIDs. Die ROWIDs werden in der Regel bei einer vorausgehenden Index-Operation bestimmt und dann in der WHERE-Bedingung angegeben. Die fixe Vorgabe der ROWID als Zeichenkette in der WHERE-Bedingung wird nicht empfohlen, weil sich die Position der Zeilen aufgrund von Migration und auch nach einem Exportieren oder Importieren verändern kann. Zu empfehlen ist es hingegen, sich die ROWID mit als zusätzliche Spalte seiner SELECT-Anfrage zu selektieren, um deren Wert dann in einer WHERE-Bedingung der UPDATE- oder DELETE-Anweisung? wieder zuverwenden. Damit dies möglich ist, wird für die Programmvariablen ein Datentyp ROWID benötigt.

Die Anwendung der Operation TABLE ACCESS BY INDEX ROWID kann bei der Erstellung des Ausführungsplans angewiesen werden, indem der Hint ROWID(Tabellenname) in der SQL-Abfrage verwendet wird. Z.B.:

 SELECT /*+ROWID(Angestellte)*/ Nachname FROM Angestellte WHERE Abt_Nr = 2;

Der Zugriff über die ROWID ist die schnellste Möglichkeit, um eine einzelne Zeile aus einer Tabelle zu lesen, weil die genaue Adresse der Zeile in der Datenbank angegeben wird. Achten Sie darauf, dass ein Tabellenzugriff immer langsamer als ein Index-Zugriff ist. Wenn eine SQL-Abfrage einen großen Teil der Tabelle? als Ergebnis zurückliefert oder wenn die Tabelle klein ist, ist die Anwendung eines TABLE ACCESS FULLs deutlich effizienter.


TABLE ACCESS FULL

Bei dieser Tabellenzugriffsmethode werden alle Zeilen einer Tabelle? gelesen. Obwohl die Blöcke nicht einzeln, sondern mehrere auf einmal gelesen werden, gehört der TABLE ACCESS FULL, auch als FULL TABLE SCAN bekannt, zu den systembelastenden Operationen, denn jeder Datensatz muss im Hauptspeicher gegen die WHERE-Bedingung geprüft werden.

TABLE ACCESS FULL liefert eine sehr schlechte Performance bei großen Tabellen, die als Ergebnismenge nur wenigen Datensätzen zurückgeben. Die Performance verringert sich dadurch proportional mit wachsender Tabellengröße.

Die Anwendung eines TABLE ACCESS FULLs kann bei der Erstellung des Ausführungsplans angewiesen werden, indem der Hint FULL (Tabellenname) in der SQL-Abfrage verwendet wird. Z.B.:

 SELECT /*+FULL(Angestellte)*/Nachname FROM Angestellte WHERE Abt_Nr = 2;

Es gibt aber auch Fälle, wie z.B. sehr kleine Tabellen, die nur einen Block oder nur sehr wenige Blöcke beanspruchen und damit nur eine oder sehr wenige I/O-Operationen erfordern, in denen der TABLE ACCESS FULL der schnellste Zugriffsform sein kann. Ein weiterer Fall kann sein, wenn die WHERE-Bedingung einen sehr großern Anteil aller Datensätze der Tabelle selektiert.


INDEX RANGE SCAN

Der Indexzugriffsmethode INDEX RANGE SCAN ist der häufigste Zugriff über einen Index. Diese Operation wird von dem ORACLE-Optimizer verwendet, wenn ein oder mehrere Spalten eines Indexes in der WHERE-Bedingung verwendet werden. Es ist aber auch notwendig, dass die Ausdrücke der WHERE-Bedingung die folgenden Vergleichsoperatoren =, > oder < enthalten, auch in Verbindung mit dem Operator AND. Ein LIKE-Operator ohne Platzhalter (Wildcard '%', '_') kann auch zum INDEX RANGE SCAN führen.

Ein idealer Zugriff ist nur möglich, wenn alle Attribute auf Gleichheit in der WHERE-Bedingung abgefragt werden. Der INDEX RANGE SCAN hat eine schlechte Performance bei großen Ergebnismengen und bei der Weiterverarbeitung von Daten, aber er ist sehr effizient für wenige Datensätze.

Es ist auch wichtig zu beachten, dass Indizes den Zugriff auf die Datensätze von Tabellen? beschleunigen, aber sie verlangsamt das Einfügen, Ändern und Löschen von Daten. Der Grund dafür ist es, dass beim Schreiben von Daten nicht nur die Datenänderung, sondern auch die Aktualisierung aller Indizes durchgeführt werden muss. Aus diesem Grund sollen nur Spalten indiziert werden, die zur Eindeutigkeit der Daten notwendig sind und in einer WHERE-Bedingung vorkommen können. Bei sehr kleinen Tabellen, deren gesamten Inhalt mit einer I/O-Operation in den Hauptspeicher kopiert werden kann, kann auf Indizes verzichtet werden.


INDEX UNIQUE SCAN

Der INDEX UNIQUE SCAN untersucht nur den Index-Baum. Diese Indexzugriffsmethode wird vom ORACLE-Optimizer verwendet, wenn ein UNIQUE- oder ein PRIMARY KEY-Constraint sicherstellt, dass auf maximal eine einzige Zeile zugegriffen wird, die der WHERE-Klausel entspricht. Alle Spalten des Unique-Indexes müssen in der WHERE-Bedingung mit dem Gleichheitszeichen (=) angegeben werden. An die nächste Operation wird immer nur die ROWID der ausgewählten Zeile weitergeleitet.

Der INDEX UNIQUE SCAN soll für den Zugriff auf genau einen Datensatz verwendet werden. Seine Performance ist aber bei Daten, die oft innerhalb eines Joins zugegriffen werden, sehr schlecht.

Normalerweise soll man keinen Hint verwenden, um einen INDEX UNIQUE SCAN zu erzwingen. Es könnte aber Fälle geben, bei denen ein solcher HINT Sinn macht. z.B. falls auf die Tabelle über einen Datenbank-Link und von einer lokalen Tabelle zugegriffen wird oder wenn die Tabelle klein genug ist, damit der Optimizer einen TABLE ACCESS FULL bevorzugt. Der Hint für den INDEX UNIQUE SCAN lautet INDEX (Tabellenalias Indexname).

Es ist auch wichtig zu beachten, dass Indizes den Zugriff auf die Datensätze von Tabellen beschleunigen, aber sie verlangsamen das Einfügen, Ändern und Löschen von Daten. Der Grund dafür ist es, dass beim Schreiben von Daten nicht nur die Datenänderung, sondern auch die Aktualisierung aller Indizes durchgeführt werden muss. Aus diesem Grund sollen nur Spalten indiziert werden, die zur Eindeutigkeit der Daten notwendig sind und in der WHERE-Bedingung vorkommen können. Bei sehr kleinen Tabellen, deren gesamten Inhalt mit einer I/O‐Operation in den Hauptspeicher kopiert werden kann, kann auf Indizes verzichtet werden.


INDEX FULL SCAN

Die Indexzugriffsmethode INDEX FULL SCAN liest den ganzen Indexbaum entsprechend seiner Index-Reihenfolge. Von dieser Operation erhält man eine sortierte Ergebnismenge. Der INDEX FULL SCAN kann vom ORACLE-Optimizer ausgewählt werden, wenn sich ein Prädikat in der WHERE-Klausel einen Index der Spalten bezieht oder auch wenn es kein Prädikat gibt, wie z.B. bei einem Kartesischen Produkt. Aber damit die Operation INDEX FULL SCAN ausgeführt wird, müssen alle ausgewählten Spalten der SELECT-Anweisung zum Index gehören und mindestens einer der Indexspalten nicht NULL sein.

Ein INDEX FULL SCAN kann auch verwendet werden, um einen Sortiervorgang zu beseitigen, weil die Daten schon nach dem Index-Schlüssel sortiert sind.


INDEX FAST FULL SCAN

Die Indexzugriffsmethode INDEX FAST FULL SCAN liest den ganzen Indexbaum. Im Gegensatz zu einem INDEX FULL SCAN kann diese Operation größere Tabellenbereiche auf einmal lesen (Multiblock-Read). Somit liefert der INDEX FAST FULL SCAN eine bessere Performance als die anderen Indexzugriffsmethoden. Die Blöcke des Indexes werden in einer unsortierten Reihenfolge komplett durchgelesen und der Lesevorgang kann parallelisiert werden. Der INDEX FAST FULL SCAN steht nur bei einem Cost-Based-Optimizer (CBO) zur Verfügung.

Der INDEX FAST FULL SCAN kann nicht verwendet werden, um einen Sortiervorgang zu eliminieren, weil die Daten nicht durch den Index-Schlüssel sortiert sind. Die Daten werden nach der Reihenfolge ihrer physikalischen Adressen gelesen. Der INDEX FAST FULL SCAN ist nur für große Ergebnismengen geeignet.


INDEX SKIP SCAN

Der INDEX SKIP SCAN kann nur bei zusammengesetzten Indizes eingesetzt werden, wobei nicht nach der ersten Spalte sondern nach einer der nachfoldenden Indexspalten gesucht wird. Er wurde erst bei ORACLE 9i eingeführt. Er spaltet den Index in logische kleinere Subindizes auf. Somit ist ein komplettes Scannen des Indexes nicht mehr notwendig und der Indexscan kann insgesamt beschleunigt werden.

In der Regel ist das Scannen von Indexblöcken schneller als das Scannen von Tabellendatenblöcken. Im INDEX SKIP SCAN darf der erste Teil des zusammengesetzten Indexes nicht in der SQL-Abfrage spezifiziert sein. Damit wird er übersprungen und nicht durchgesucht. Die Anzahl logischer Subindizes wird durch die Anzahl der eindeutigen Werte in der ersten Spalte des Indexes festgelegt.


Siehe auch: SQL-Tuning?, Ausführungsplan einer SQL-Abfrage, Statistiken, Hints, Werkzeuge zum SQL-Tuning

Quellen:

  • Alapati, Sam R./Kuhn, Darl/Padfield, Bill: "Oracle Database 11g Performance Tuning Recipes: A Problem-solution Approach", Apress, 2011, ISBN 978-1-4302-3662-7
  • Burleson, Donald.: "Oracle SQL Tuning", Rampant TechPress, 2004, ISBN 978-0-9823061-5-4
  • Dyke, Julian: "Optimizer Operations, Hints, Features"
  • Haas, Frank: "Oracle Tuning in der Praxis", 10/2009, ISBN 3-446-41907-1
  • Hotka, Dan: "Oracle SQL Tuning“, Createspace, 2007, ISBN 978-0-07-148474-9
  • Niemiec, Richard J.:"Oracle Database 11g Release 2 Performance Tuning Tips & Techniques", Oracle Press, Osborne/McGrawHill, 2012, ISBN 978-0-07-178026-1
  • Price, Jason: "Oracle Database 11g SQL – Master SQL and PL/SQL in the Oracle Database", Kap. 16, Oracle Press, MC Graw Hill, 2007, ISBN 978-0-07-149850-0
  • Oracle® Database Dokumentation: SQL Language Reference 11g Release 2, E26088-02, 07/2012
  • Oracle® Database Dokumentation: SQL Performance Guide, E16638-07, 08/2012
  • Sideris Courseware Corp.: "Oracle Database 11g R2 SQL Tuning", Sideris Courseware, 2011, ISBN
  • The Oracle FAQ Site: Oracle SQL Tuning Guide
  • TAKTUM Informatik GmbH: "Oracle Tuning und Optimierung Tutorial", Büren

Kategorie: Tuning, Z