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:


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;

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 _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.