Entwickler nutzen gerne Constraints um die Datenkonsistenz zu verbessern. Im Fall von Primary und Unique Constraints werden dafür von Oracle automatisch passende Indizes genutzt bzw. automatisch angelegt.
Beim FK Constraint ist das nicht der Fall, da die Indizes technisch nicht zwingend notwendig sind. In den meisten Fällen machen sie aber sehr viel Sinn!
Beispielumgebung erzeugen
Damit die Auswirkungen klarer werden, braucht man Beispiele und sinnvolle Datenmengen, daher erzeugen wir diese mittels eines Kreuzprodukts.
create table MASTER
AS
select rownum as MASTER_ID,
t.owner || '.' ||to_char(u.user_id) as OWNER,
t.table_name || '.' || to_char(u.user_id) AS TABLE_NAME
from dba_tables t, dba_users u;
create table DETAIL
as
select rownum as DETAIL_ID,
m.MASTER_ID,
tc.column_name,
tc.last_analyzed
from MASTER m, dba_tab_columns tc
where tc.owner = substr(m.owner,1,instr(m.owner,'.')-1)
and tc.table_name = substr(m.table_name,1,instr(m.table_name,'.')-1);Jetzt benötigen wir noch die Constraints:
alter table MASTER add constraint MASTER_PK primary key (MASTER_ID);
alter table DETAIL add constraint DETAIL_PK primary key (DETAIL_ID);
alter table DETAIL add constraint DETAIL_MASTER_FK
foreign key (MASTER_ID) REFERENCES MASTER (MASTER_ID);Jetzt haben wir zwei Tabellen in einem typischen Master/Detail Verhältnis. Die Tabellengrößen sind dabei anhängig von der Anzahl der in Ihrer Datenbank aktuell vorhandenen Tabellen.
Welche Indizes haben wir damit erzeugt?
Das kann man in USER_IND_COLUMNS herausfinden.
col table_name for a16
col index_name for a15
col segment_name for a16
col "Columns" for a20
set tab off
select table_name, index_name,
listagg(column_name,', ') within group (order by column_position) "Columns"
from user_ind_columns
where table_name in ('MASTER','DETAIL')
group by table_name, index_name
order by 1,2;
TABLE_NAME INDEX_NAME Columns
---------------- ------------ --------------
DETAIL DETAIL_PK DETAIL_ID
MASTER MASTER_PK MASTER_IDWie groß sind die Objekte aktuell?
Das kann man einfach in DBA_SEGEMENTS auslesen.
select segment_name, bytes, blocks
from dba_segments
where segment_name in ('MASTER','DETAIL','MASTER_PK','DETAIL_PK');
SEGMENT_NAME BYTES BLOCKS
---------------- ---------- ----------
MASTER 4194304 512
MASTER_PK 2097152 256
DETAIL 34603008 4224
DETAIL_PK 16777216 2048 Jetzt schauen wir uns einige einfache Statements und den Aufwand an, den Oracle bei der Ausführung dieser hat. Dafür nutzen wir im SQLPLUS das AUTOTRACE, da man hier neben dem Ausführungsplan auch die Laufzeit-Statistik angezeigt bekommt.
Einschalten von SQLPLUS Autotrace:
set autotrace traceonlyAusschalten von SQLPLUS Autotrace:
set autotrace OFFMan kann diese Informationen auch aus anderen Quellen auslesen, das würde aber den Rahmen dieses Artikels sprengen.
Auswirkung von FK Constraints ohne passenden Index
Im ersten Statement möchten wir eine Spalte aus der Master Tabelle und eine Spalte aus der Detail Tabelle für einen bestimmten Master-Datensatz ausgeben.
select M.Owner, D.Column_name
from Master M join Detail D on (M.Master_id=D.Master_Id)
where M.Master_ID = 1234;
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1447752056
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 8188 |
| 1 | NESTED LOOPS | | 46 | 8188 |
| 2 | TABLE ACCESS BY INDEX ROWID| MASTER | 1 | 99 |
|* 3 | INDEX UNIQUE SCAN | MASTER_PK | 1 | |
|* 4 | TABLE ACCESS FULL | DETAIL | 46 | 3634 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("M"."MASTER_ID"=1234)
4 - filter("D"."MASTER_ID"=1234)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4217 consistent gets
0 physical reads
0 redo size
775 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processedHinweis: Wie viele Datensätze gefunden werden, ist von dem zufälligen Objekt mit der MASTER_ID=1234 abhängig, und wird bei Ihnen anders sein. Sollten Sie recursive calls und/oder physical reads sehen, bitte das Statement ein zweites Mal aufrufen. Wir wollen fair sein und den vorhandenen Buffer Cache optimal nutzen.
Was sehen wir hier im Ausführungsplan?
Oracle nutzt eine Nested Loop über den MASTER_PK und die Tabelle MASTER sowie einen Full Table Scan auf die Tabelle DETAIL. Die Vorgehensweise ist sinnvoll, da nur ein Datensatz in der Tabelle MASTER mit dem PK=WERT zu erwarten sei. Wie viel Aufwand der Full Table Scan auf DETAILs bedeutet, kann man an den consistent gets sehen. Diese geben (gemeinsam mit db block gets) an, wie viele Datenblöcke im Buffer Cache gelesen werden mussten, um das Ergebnis zu erzeugen. Der Zugriff auf die Tabelle MASTER und den Index für den MASTER_PK wird hier maximal 2-3 Blockzugriffe erzeugt haben, somit bleiben in diesem Beispiel rund 4215 für den Full Table Scan auf die Detail Tabelle.
Was, wenn die Detail Tabelle jetzt 10, 100 oder 1000-fach größer wäre? Die consistent gets werden um den gleichen Faktor wachsen – und damit auch die Statement Laufzeit.
Als nächstes Beispiel sehen wir uns ein Delete auf der Tabelle Master an (es würde aber auch ein Update auf die vom FK referenzierte Master_ID reichen, um das gleiche Ergebnis zu erhalten).
delete from Master where MASTER_ID=1234;Da dies „sofort“ zu folgendem Fehler führt:
ERROR at line 1:
ORA-02292: integrity constraint (SYS.DETAIL_MASTER_FK) violated - child record foundmüssen wir einen Trick anwenden. Damit wir wirklich 100% sicher sind, dass wir keine Blöcke im Buffer Cache haben, starten wir die Datenbank neu (bitte NICHT auf produktiven Datenbanken machen!).
shutdown immediate;
startup;Zur Sicherheit verifizieren wir, dass auch wirklich keine Blöcke im Cache sind:
select name, count(*)
from v$cache
where name in ('MASTER','DETAIL','MASTER_PK','DETAIL_PK')
group by name;
no rows selected.Jetzt nochmals den DELETE ausführen und prüfen, wie viele Blöcke im Cache sind:
delete from Master where MASTER_ID=1234;
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.DETAIL_MASTER_FK) violated - child record found
select name, count(*)
from v$cache
where name in ('MASTER','DETAIL','MASTER_PK','DETAIL_PK')
group by name;
NAME COUNT(*)
---------------- ----------
DETAIL 4208
MASTER_PK 2
MASTER 2Oracle musste von der Tabelle Detail 4208 Blöcke einlesen – das entspricht der ganzen Tabelle. Dass es einige Blöcke weniger sind, als die Tabelle groß ist, liegt daran, dass nicht alle Blöcke der Tabelle schon Daten enthalten.
Daraus lernt man, dass das Löschen eines Datensatzes in der Master Tabelle oder auch nur das Update der MASTER_ID zu einem Full Table Scan auf der Detail Tabelle führt. Was wir hier nicht beweisen können ist, dass zu diesem Zeitpunkt Oracle intern beide Objekte teilweise sperrt. So würde ein DML auf Detail potentiell so lange warten müssen, bis das DML auf der Master Tabelle mit dem Fehler abbricht. Je größer die Tabellen werden, um so störender ist dieser Einfluss auf die Datenbank.
Gemeinsam mit FK Optionen wie ON DELETE CASCADE und großen Datenmengen führt dies dazu, dass das Löschen eines Datensatzes im MASTER im Sekunden- oder Minutenbereich liegt.
Änderung der Auswirkungen, sobald man einen passende Index für FK Constraint anlegt
Machen wir jetzt die Gegenprobe mit einem Index für den FK Constraint:
CREATE INDEX DETAIL_MASTER_FK on DETAIL (MASTER_ID);
Index created.Wir wiederholen jedes Statement und schauen, ob sich etwas geändert hat:
select M.Owner, D.Column_name
from Master M join Detail D on (M.Master_id=D.Master_Id)
where M.Master_ID = 1234;
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2518150809
-------------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 |
| 1 | NESTED LOOPS | | 7 |
| 2 | TABLE ACCESS BY INDEX ROWID | MASTER | 1 |
|* 3 | INDEX UNIQUE SCAN | MASTER_PK | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| DETAIL | 7 |
|* 5 | INDEX RANGE SCAN | DETAIL_MASTER_FK | 7 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("M"."MASTER_ID"=1234)
5 - access("D"."MASTER_ID"=1234)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
775 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processedAus dem Full Table Scan auf DETAIL ist ein Indexzugriff über den neuen Index geworden. Die consistent gets sind von über 4200 auf gerade mal 9 zurückgegangen. Selbst wenn die Tabellen viel größer werden, wird die Anzahl der benötigten Blöcke im Buffer Cache – und damit die Statementlaufzeit – nur wenig ansteigen.
Als letztes prüfen wir, was beim DELETE auf den Master passiert. Dazu müssen wir die Instanz nochmals restarten:
set autotrace off
shutdown immediate;
startup;
select name, count(*)
from v$cache
where name in ('MASTER','DETAIL','MASTER_PK','DETAIL_PK','DETAIL_MASTER_FK')
group by name;
no rows selected
delete from Master where MASTER_ID=1234;
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.DETAIL_MASTER_FK) violated - child record found
select name, count(*)
from v$cache
where name in ('MASTER','DETAIL','MASTER_PK','DETAIL_PK','DETAIL_MASTER_FK')
group by name;
NAME COUNT(*)
---------------- ----------
DETAIL 1
MASTER_PK 2
DETAIL_MASTER_FK 3
MASTER 2Das DELETE musste nur noch wenige Blöcke angreifen und war somit auch viel schneller fertig. Der Laufzeitunterschied bei großen Tabellen liegt oft im Bereich von Sekunden bis Minuten!
Was ist ein passender Index für einen FK Constraint?
FK Constraint ohne passenden Index führt in vielen Fällen zu suboptimalen Ausführungsplänen, langsamen DMLs (DELETE, UPDATE) auf der Master Tabelle und unnötigen „Verschmutzen“ des Buffer Caches mit nur für die Constraintprüfung nötigen Datenblöcken der Detail Tabelle.
Oracle akzeptiert inzwischen viele Indizes als für den FK Constraint tauglich, beispielsweise würden folgende Indizes ebenfalls als FK Index funktionieren (sofern die Datenmenge in DETAILs entsprechend groß ist):
/* Mehrspaltiger Index mit der FK Spalte als führende Spalte(n):
funktioniert praktisch immer */
CREATE INDEX DETAIL_IDX01 on DETAIL (MASTER_ID, COLUMN_NAME);
/* Mehrspaltige Indizes mit FK Spalte nicht als führende Spalte:
funktioniert meist, sofern der Index deutlich weniger Blöcke
belegt wie die Tabelle. In unserem Beispiel, würde es nur
funktionieren, wenn die Tabelle Details viel mehr (befüllte)
Spalten hätte. */
CREATE INDEX DETAIL_IDX01 on DETAIL (COLUMN_NAME, MASTER_ID, LAST_ANALYZED);
/* Mehrspaltige Indizes mit der FK Spalte am Ende:
funktioniert nur selten, wenn es mehr als 3-4 Spalten sind */
CREATE INDEX DETAIL_IDX01 on DETAIL (DETAIL_ID, COLUMN_NAME, LAST_ANALYZED, MASTER_ID);Wie lange ein Index, der die Spalten des FK Constraints enthält, funktioniert, hängt von mehreren, den Cost Based Optimizer beeinflussenden Faktoren ab. In der Praxis sollte die FK Spalte bei mehrspaltigen Indizes entweder die erste oder die zweite Spalte sein. Je weiter hinten die Spalte im Index vorkommen, um so ineffektiver wird der Zugriff über den Index, bis dieser keinen Vorteil gegenüber einen Full Table Scan auf dem Detail mehr bringt.
Deep Dive: DML auf den Master
Bei DELETE auf den Master oder UPDATE, der durch den FK Constraint referenzierten Spalte der Master Tabelle, muss Oracle die Detail Tabelle lesen, um den Constraint zu prüfen. Das führt zu langen Laufzeiten für die DML Statements – je großer die Detail Tabelle wird, um so länger. In älteren Oracle Versionen hat Oracle die Detail Tabelle dabei sogar exclusive gesperrt. Damit konnte niemand während des DMLs auf den Master ein DETAIL Record erzeugen, das dem Constraint verletzten hätte.
Seit einiger Zeit nutzt Oracle hier jedoch einen Trick: interne Trigger auf der Detail Tabelle.
Während das DELETE/UPDATE Statement auf dem Master läuft und auf die Detail Tabelle zugegriffen wird, wird eine Art interner Trigger erzeugt. Dieser verhindert, dass in der Detail Tabelle durch INSERT oder UPDATE ein Record entsteht, das für das Statement auf der Master Tabelle relevant ist. Hier ein Beispiel:
Session 1:
delete from MASTER where MASTER_ID=1234;
Session 2:
insert into DETAIL (DETAIL_ID, MASTER_ID) values (0,1234);Solange in Session 1 noch kein Commit erfolgt ist, wäre das Insert in Session zwei gültig (es gibt dem Master mit der MASTER_ID=1234 noch). Würde Session 1 zuerst commiten und die Session 2 erst danach, wäre in der Tabelle DETAIL ein Record mit einem Verweis auf den MASTER (MASTER_ID=1234). Das darf nicht passieren.
Aus diesem Grund hält Oracle die Ausführung des INSERTs solange an, solange die Transaktion mit dem DML auf den Master noch aktiv ist. Hier ein detaillierteres Beispiel:
Session 1:
delete from detail where master_id=1234;
delete from master where master_id=1234;
Session 2:
insert into detail (detail_id, master_id, column_name)
values (0,1234,'Test');
... das Insert wird blockiert, solange Session 1 noch nicht commited hat ...
Session 1:
commit;
Session 2:
insert into detail (detail_id, master_id, column_name)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.DETAIL_MASTER_FK) violated - parent key not foundSoweit so gut. Das ist grundsätzlich auch mit einem vorhandenen FK Index so. Der Unterschied ist, dass Oracle beim DELETE auf den MASTER mit vorhandenem FK Index viel weniger Zeit braucht. Wenn die Transaktion danach sofort commited wird, ist der Zeitraum, in dem der INSERT in der zweiten Session blockiert, annähernd Null und bekommt den Fehler sofort.
Nehmen wir an, dass wir in zwei Sessions Records löschen:
Session 1:
delete from detail where master_id=1230;
delete from master where master_id=1230;
Session 2:
delete from detail where master_id=3210;
delete from master where master_id=3210;
Session 1:
insert into detail (detail_id, master_id, column_name)
values (0,3210,'Test Session 1');
... blockiert ...
Session 2:
insert into detail (detail_id, master_id, column_name)
values (0,1230,'Test Session 1');
... blockiert ...
Session 1:
insert into detail (detail_id, master_id, column_name)
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Session 2:
... immer noch blockiert, solange Session 1 nicht rollbacked wird ...
Session 1:
rollback;
Session 2:
1 row created.Auch hier gilt, das Verhalten ist mit oder ohne FK Index das gleiche, allerdings steigt die Wahrscheinlichkeit eines Problems mit der Laufzeit der Transaktionen – je länger das DELETE auf den Master benötigt, um so wahrscheinlicher entstehen diese.
Zusammenfassung
Indizes, die FK Constraints unterstützen, sorgen in der Regel für schnellere Statementverarbeitung und sind vor allen bei DML auf den Master-Tabellen hilfreich, da diese viel schneller ablaufen und so die Wahrscheinlichkeit für Locking Contention oder gar Deadlocks deutlich reduzieren.