single.php
< Beitrag von Frank Winter

Ermittlung von Foreign Keys ohne Index

Bei der Weiterentwicklung von Datenmodellen schleicht sich gelegentlich der Fehler ein, dass Fremdschlüssel (Foreign Keys) erstellt werden, ohne die zugehörigen Spalten zu indizieren. Im Folgenden geht es daher um die Ermittlung von Foreign Keys ohne Index.

Welche Auswirkung kann es haben, dass Foreign Keys ohne Index erstellt werden? Nehmen wir an, wir haben eine Tabelle CUSTOMER, die von einer Tabelle ADDRESS über eine Spalte CUSTOMER_ID und einem zugehörigen Foreign Key Constraint referenziert wird. Wenn in der Tabelle CUSTOMER Datensätze gelöscht werden, muss die Oracle-Datenbank für die Sicherstellung der referenziellen Integrität überprüfen, ob die Tabelle ADDRESS Datensätze enthält, die einen gelöschten Datensatz der Tabelle CUSTOMER referenzieren.

Fehlender Index

Wenn kein Index auf der Spalte ADDRESS.CUSTOMER_ID liegt, muss die Oracle-Datenbank hierfür die gesamte Tabelle ADDRESS durchlesen (Full Table Scan). Man kann sich vorstellen, dass dies zu langsam werden kann, wenn die Tabelle ADDRESS viele Datensätze enthält. Ist die Fremdschlüsselspalte hingegen indiziert, ist das Suchen nach referenzierenden Datensätzen kein Problem und das Löschen der Datensätze in der Tabelle CUSTOMER geht erheblich schneller. Dieser Effekt ist für manche überraschend: Das Löschen in der Tabelle CUSTOMER geht langsam, weil ein Index in Tabelle ADDRESS fehlt.

Wenn man also beim Löschen von Datensätzen (oder auch bei Updates der referenzierten Felder) auf massive Performanceprobleme stößt, ist es sinnvoll zu prüfen, ob es Foreign Key Constraints in anderen Tabellen gibt, bei denen die Fremdschlüsselspalten nicht indiziert wurden. Das gleiche gilt natürlich auch für Joins zwischen Tabellen, die ohne Index auf dem Fremdschlüssel ggf. zu Full Table Scans führen. Wenn in obigem Beispiel die Tabellen CUSTOMER und ADDRESS gejoint werden und die Where-Klausel sich nur auf Felder der Tabelle CUSTOMER bezieht, erhält man ohne Index auf den Fremdschlüssel unweigerlich einen Full Table Scan auf Tabelle ADDRESS.

Ermittlung von Foreign Keys ohne Index

Die Frage ist nun, wie solche unindizierten Fremdschlüsselspalten zu ermitteln sind.

Da das Problem häufiger auftritt, hat der bekannte Oracle-Guru Tom Kyte schon vor geraumer Zeit eine Abfrage entwickelt, die genau solche unindizierten Fremdschlüsselspalten findet. Das Problem an dieser Abfrage ist allerdings, dass sie

  • sich nur auf Datenbankobjekte innerhalb eines Schemas bezieht
  • nicht die Anzahl der Datensätze in einer referenzierenden Tabelle ausgibt

Aus diesem Grunde habe ich seine Abfrage wie folgt angepasst:

Das Ergebnis einer solchen Abfrage könnte auszugsweise wie folgt aussehen:

REFERENCING
_TABLE
NUM
_ROWS
CONSTRAINT
_NAME
REFERENCING
_COLUMNS
REFERENCED
_TABLE

REFERENCED
_NUM_ROWS

GRP.PRODUKTKAUF 3.967.950 PRKF_DPRA_FK PRAN_ID GPR.PRODUKTANG 97
HR.DEPARTMENTS 27 DEPT_MGR_FK MANAGER_ID HR.EMPLOYEES 107
OE.CUSTOMERS 319 CUSTOMERS
_ACCOUNT
_MANAGER_FK
ACCOUNT
_MGR_ID
HR.EMPLOYEES 107

Die REFERENCING_TABLE ist die Tabelle, die einen Foreign Key enthält und durchsucht werden muss, wenn in der REFERENCED_TABLE ein Datensatz gelöscht wird. Sofort erkennt man, dass in diesem Beispiel die Tabelle PRODUKTKAUF im Schema GPR aufgrund der vielen Datensätze problematisch sein kann. Hier fehlt ein Index auf dem Fremdschlüsselfeld PRAN_ID und es müssten somit für jeden gelöschten Datensatz in Tabelle PRODUKTANG immer 3.967.950 Datensätze in Tabelle PRODUKTKAUF durchsucht werden. Das ist natürlich extrem ungünstig; ein Index auf die Spalte PRAN_ID ist hier somit sehr zu empfehlen.

Stehen in der referenzierenden Tabelle eher wenig Datensätze (wie z. B. in Tabelle HR.DEPARTMENTS) spielt das Fehlen eines Index keine große Rolle.

Die obige Abfrage ist natürlich am sinnvollsten, wenn die Tabellenstatistiken aktuell sind und die Werte in Spalte NUM_ROWS annähernd der Realität entsprechen.

Fazit

Man erkennt, dass die gelegentliche Überprüfung auf unindizierte Fremdschlüsselspalten sehr empfehlenswert ist. Insbesondere bei sehr schlechter Löschperformance, aber natürlich auch bei zu langsam laufenden Joins, sollte diese Überprüfung zwingend durchgeführt werden.

Folgen
X

Folgen

E-mail : *
Kategorie: Oracle-Development | Schlagwörter: , , | Kommentare: 0

Beitrag kommentieren

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.