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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | select cons.owner||'.'||cons.table_name REFERENCING_TABLE, T.NUM_ROWS, cons.constraint_name, cname1 || nvl2(cname2,','||cname2,null) || nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) || nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) || nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) REFERENCING_COLUMNS, PCS.OWNER||'.'||PCS.TABLE_NAME REFERENCED_TABLE, T2.NUM_ROWS REFERENCED_NUM_ROWS from ( select b.table_name, b.constraint_name, b.owner, b.r_constraint_name, b.r_owner, max(decode( position, 1, column_name, null )) cname1, max(decode( position, 2, column_name, null )) cname2, max(decode( position, 3, column_name, null )) cname3, max(decode( position, 4, column_name, null )) cname4, max(decode( position, 5, column_name, null )) cname5, max(decode( position, 6, column_name, null )) cname6, max(decode( position, 7, column_name, null )) cname7, max(decode( position, 8, column_name, null )) cname8, count(*) col_cnt from (select substr(table_name,1,30) table_name, substr(constraint_name,1,30) constraint_name, substr(column_name,1,30) column_name, substr(owner,1,30) owner, position from all_cons_columns where OWNER NOT IN ('SYS', 'DBSNMP', 'OUTLN', 'PERFSTAT', 'SYSTEM', 'XDB') ) a, all_constraints b where a.constraint_name = b.constraint_name and A.OWNER = B.OWNER and b.constraint_type = 'R' group by b.owner, b.table_name, b.constraint_name, b.r_constraint_name, b.r_owner ) cons inner join all_tables t on (cons.owner = T.OWNER and cons.table_name = T.TABLE_NAME and t.OWNER NOT IN ('SYS', 'DBSNMP', 'OUTLN', 'PERFSTAT', 'SYSTEM', 'XDB','MDSYS')) inner join all_constraints pcs on (cons.r_constraint_name = PCS.CONSTRAINT_NAME and cons.r_owner = PCS.OWNER and PCS.CONSTRAINT_TYPE = 'P' and pcs.OWNER NOT IN ('SYS', 'DBSNMP', 'OUTLN', 'PERFSTAT', 'SYSTEM', 'XDB','MDSYS')) inner join all_tables t2 on (pcs.owner = T2.OWNER and pcs.table_name = T2.TABLE_NAME and t2.OWNER NOT IN ('SYS', 'DBSNMP', 'OUTLN', 'PERFSTAT', 'SYSTEM', 'XDB','MDSYS')) where col_cnt > ALL ( select count(*) from all_ind_columns i where i.table_name = cons.table_name and I.INDEX_OWNER = cons.owner and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8) and i.column_position <= cons.col_cnt and INDEX_OWNER NOT IN ('SYS', 'DBSNMP', 'OUTLN', 'PERFSTAT', 'SYSTEM', 'XDB','MDSYS') group by i.index_name ) order by cons.owner, cons.table_name; |
Das Ergebnis einer solchen Abfrage könnte auszugsweise wie folgt aussehen:
REFERENCING _TABLE | NUM _ROWS | CONSTRAINT _NAME | REFERENCING _COLUMNS | REFERENCED _TABLE | REFERENCED |
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.