Von Constraints und Ausführungsplänen

01.
Februar
2018
Veröffentlicht von: Katja Werner

In diesem Monatstipp geht es um die Validierung von Constraints sowie das Zusammenspiel von Constraints und Ausführungsplänen.

CONSTRAINTS UND VALIDIERUNG

Constraints dienen der Sicherstellung bestimmter Bedingungen beim Einfügen von Daten in eine Spalte. So wird ein Primary Key Constraint dazu verwendet sicherzustellen, dass zum einen jeder Wert dieser Spalte einzigartig ist und zum anderen, dass auch wirklich jede Zeile mit einem Wert befüllt ist, dass also keine NULL-Werte vorkommen. Ein Foreign Key wird auf die Spalte einer Tabelle (= Child Table) gelegt und stellt sicher, dass es zu jedem Wert in dieser Spalte auch einen Wert einer referenzierten Tabelle (= Parent Table) gibt. Daneben gibt es auch weitere Constraints, zum Beispiel Not-NULL-Constraints – es darf keine NULL-Werte geben – oder Check-Constraints, die sicherstellen, dass nur bestimmte vordefinierte Werte in eine Spalte eingegeben werden können. 

Meist werden die Constraints beim Anlegen validiert. Das heißt, es wird geprüft, ob alle Werte, die bereits in der Spalte stehen, der Constraint-Bedingung genügen. Erst wenn die Prüfung erfolgreich ist, gilt der Constraint als erstellt. Damit wird bei allen zukünftig einzufügenden, zu ändernden oder zu löschenden Werten geprüft, dass die Bedingung eingehalten wird. Bei Werten, die nicht der Bedingung entsprechen, bricht die Transaktion mit einer Fehlermeldung ab (zum Beispiel ORA-02291: integrity constraint (string.string) violated - parent key not found). 

Constraints können jedoch auch ohne Validierung der bereits vorhandenen Daten erstellt werden (=novalidate). Die Prüfung erfolgt in diesem Fall nur für alle nachfolgend geänderten Werte. Nachteil ist natürlich, dass zum Beispiel – trotz eines Not-NULL-Constraints – Altdaten NULL sein können. Trotzdem finden sich nicht validierte Constraints in der Praxis immer mal wieder. Manchmal einfach deshalb, weil bereits Daten in der Tabelle gespeichert sind, die den Constraint-Bedingungen nicht genügen würden, man jedoch aktuell keine Zeit zur Bereinigung hat und aber doch ab sofort einen Constraint einsetzen möchte. 

NOVALIDATE wird auch häufig im Datawarehouse-Bereich bei Migrationen per Export und Import eingesetzt. Die Tabellen in Datawarehouse-Umgebungen sind oft riesig. Hier kann die Validierung eines Constraints beim Anlegen viele Minuten oder auch Stunden in Anspruch nehmen, die Zeitersparnis durch die Verwendung von NOVALIDATE ist immens.
Warum ist hier der Einsatz von NOVALIDATE sinnvoll?:
Die in der Source-Datenbank enthaltenen Daten entsprechen bereits den Constraints. Datenänderungen durch die Applikation können nicht stattfinden, denn die Applikation ist während der Migration gestoppt. Damit ist zum Migrationszeitpunkt eine weitere Datenprüfung durch etwaige Constraints nicht notwendig und kostet nur unnötig Zeit. Deshalb werden beim eigentlichen Datenimport die Constraints erstmal nicht mit angelegt bzw. sie sind disabled. Ist der Import beendet, werden die Constraints angelegt – und zwar mit NOVALIDATE um auch an dieser Stelle keine Zeit zu verlieren. 

Warum nicht validierte Constraints jedoch auch Nachteile auf die Performance haben können, wird im folgenden Test deutlich.

CONSTRAINTS UND DER OPTIMIZER

Man könnte denken, wenn zwei Tabellen miteinander gejoint werden, so muss im Ausführungsplan auch auf beide Tabellen zugegriffen werden. Dass dies nicht immer der Fall ist musste ich vor kurzem lernen, und zwar bei diesem Statement:

SELECT EMP.* FROM DEPT,EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO
      AND DEPT.DEPTNO=20;


DEPT
und EMP sind die Tabellen aus dem SCOTT-Beispielthema und werden so angelegt:

CREATE TABLE DEPT
( DEPTNO NUMBER(2,0),
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) USING INDEX
);


 CREATE TABLE EMP
( EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0), 
   CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
   CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO) ENABLE
) ;


Beide Tabellen besitzen einen Primary Key und es besteht zudem eine Foreign Key-Beziehung, in der die Tabelle EMP als Child und die Tabelle DEPT als Parent agiert.

In der Praxis sind solche Statements sehr selten, denn ein solches SELECT bei diesem Daten"modell" ist insofern sinnlos, als dass man die Ergebnisse auch allein aus der EMP-Tabelle ziehen könnte. Nichtsdestotrotz habe ich in der Praxis genau solche Joins erlebt - von Entwicklern geschrieben, die ihr Datenmodell nicht richtig kannten. In einem ähnlich gelagerten Fall wurden auch entsprechende Statements geschrieben, weil zu einem früheren Zeitpunkt noch keine Constraints eingesetzt wurden. Es ist damit also kein Beispiel für sinnvollen Code. Für diesen Monatstipp soll dieses Statement jedoch herhalten, um einerseits zu veranschaulichen, wie intelligent sich der Oracle Optimizer verhält und andererseits zu zeigen, welche Folgen das Validieren oder nicht-Validieren von Constraints nach sich ziehen können.

Ausgegangen war ich davon, dass der Oracle Optimizer für das obige Statement auf beide Tabellen zugreifen muss. Das SELECT zeigt zwar nur Daten aus der EMP-Tabelle, aber für die Erfüllung der WHERE-Bedingung (EMP.DEPTNO=DEPT.DEPTNO AND DEPT.DEPTNO=20 – nur Employees, die in beiden Tabellen vorhanden sind und zum Departement 20 gehören) muss auf die Tabelle DEPT zugegriffen werden. Also – Zugriff auf zwei Tabellen. Aber mitnichten! – Der Ausführungsplan sah so aus:

SQL_ID  08adu04csmtv8, child number 0
-------------------------------------
SELECT EMP.* FROM DEPT,EMP WHERE EMP.DEPTNO = DEPT.DEPTNO       AND
DEPT.DEPTNO=20
 
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      5 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      5 |      5 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("EMP"."DEPTNO"=20)


Folgendes ist hier zu sehen: Im ersten Schritt wird auf die Tabelle EMP zugegriffen. Hier ist kein Index vorhanden, deshalb erfolgt dieser Schritt als FULL TABLE SCAN. In diesem Schritt 1 wird auch gleich auf die Departement-Nummer=20 gefiltert. Soweit lief alles „normal“. An dieser Stelle fällt nun aber ins Auge, dass auf die Tabelle DEPT überhaupt nicht zugegriffen wird, obwohl in der WHERE-Klausel beide Tabellen gejoint werden und zusätzlich noch nach DEPT.DEPTNO=20 gesucht wird. Was ist hier los? Die Antwort dämmert nach einigem Nachdenken und die Spur führt uns zu den Constraints:

Der Optimizer „zaubert“:  Er weiß, dass es einen Foreign Key Constraint gibt, der sicherstellt, dass in der Child-Tabelle EMP nur Departement-Nummern stehen, die auch in der Parent-Tabelle DEPT enthalten sind. Somit muss der Optimizer in seinem Ausführungsplan gar nicht auf die DEPT-Tabelle zugreifen, um die WHERE-Bedingung sicherzustellen. Er spart sich also den Join sowie den Zugriff auf die DEPT-Tabelle und sucht stattdessen gleich in der EMP-Tabelle die DEPTNO=20. Dies ist es, was wir in Schritt 1 des Ausführungsplans in der „Predicate Information“ des sehen: 1 - filter("EMP"."DEPTNO"=20). Das Beispiel zeigt, dass der Oracle Optimizer tatsächlich mit allen ihm zur Verfügung stehenden Informationen unnötige Tabellenzugriffe vermeidet und zeitsparende Ausführungspläne entwickelt.

Gehen wir nun noch einen Gedankenschritt weiter: Wenn – wie eben gesehen – der Optimizer Zugriffe auf Tabellen einsparen kann, weil er von einer Foreign Key Beziehung weiß, was passiert dann, wenn dieser Foreign Key mit NOVALIDATE erstellt wurde? In diesem Fall dürfte der Optimizer nicht davon ausgehen, dass zum Beispiel alle Departement-Nummern der Tabelle EMP auch in der Tabelle DEPT existieren, denn es könnte ja noch alte, ungeprüfte Werte geben, die keinerlei Entsprechung in der DEPT-Tabelle haben. Probieren wir es aus, indem wir den Foreign Key auf NOVALIDATE setzen:

ALTER TABLE EMP ENABLE NOVALIDATE CONSTRAINT FK_DEPTNO;


Und hier ist der Ausführungsplan für das obige SELECT-Statement:

SQL_ID  08adu04csmtv8, child number 0
-------------------------------------
SELECT EMP.* FROM DEPT,EMP WHERE EMP.DEPTNO = DEPT.DEPTNO       AND
DEPT.DEPTNO=20
 
Plan hash value: 130586116
---------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      5 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS      |         |      1 |      5 |      5 |00:00:00.01 |       8 |
|*  2 |   INDEX UNIQUE SCAN| PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  3 |   TABLE ACCESS FULL| EMP     |      1 |      5 |      5 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPT"."DEPTNO"=20)
   3 - filter("EMP"."DEPTNO"=20)


Tatsächlich – der Optimizer verlässt sich nicht auf den Constraint und greift auf beide Tabellen (bzw. per INDEX UNIQUE SCAN auf den Primary Key Index PK_DEPT der Tabelle DEPT sowie per FULL TABLE SCAN auf die Tabelle EMP) zu.

Selbiges passiert sogar dann, wenn der Foreign Key Constraint zwar validiert ist, der Primary Key der Tabelle DEPT jedoch nicht. Dies zeigt der dritte Test:

ALTER TABLE EMP ENABLE VALIDATE CONSTRAINT FK_DEPTNO;
ALTER TABLE DEPT ENABLE NOVALIDATE CONSTRAINT PK_DEPT;
 
SQL_ID  08adu04csmtv8, child number 0
-------------------------------------
SELECT EMP.* FROM DEPT,EMP WHERE EMP.DEPTNO = DEPT.DEPTNO       AND
DEPT.DEPTNO=20
 
Plan hash value: 130586116
 
---------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      5 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS      |         |      1 |      5 |      5 |00:00:00.01 |       8 |
|*  2 |   INDEX UNIQUE SCAN| PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  3 |   TABLE ACCESS FULL| EMP     |      1 |      5 |      5 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPT"."DEPTNO"=20)
   3 - filter("EMP"."DEPTNO"=20)


Der Vollständigkeit halber soll hier auch noch ein vierter Test durchgeführt werden. Und zwar sollen diesmal auch Werte aus der  DEPT-Tabelle selektiert werden, die nicht im Foreign Key erfasst sind. Für diesen Test sind alle Constraints im Ursprungszustand, das heißt, alle sind validiert.

Das SQL-Statement lautet diesmal so:

SELECT EMP.*, DEPT.DNAME FROM DEPT,EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO
      AND DEPT.DEPTNO=20;

SQL_ID  cmptgg611fvy0, child number 0
-------------------------------------
SELECT EMP.*, DEPT.DNAME FROM DEPT,EMP WHERE EMP.DEPTNO = DEPT.DEPTNO   
    AND DEPT.DEPTNO=20
 
Plan hash value: 568005898
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      5 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                |         |      1 |      5 |      5 |00:00:00.01 |       9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  4 |   TABLE ACCESS FULL          | EMP     |      1 |      5 |      5 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPT"."DEPTNO"=20)
   4 - filter("EMP"."DEPTNO"=20)


Der abgebildete Ausführungsplan zeigt, dass bei dieser Form des SELECTS auch validierte Constraint nicht helfen. DEPT.DNAME ist nicht durch die vorhandenen Constraints abgedeckt und damit muss die Datenbank die Werte aus beiden Tabellen auslesen.

FAZIT

Soweit möglich sollte mit korrekt validierten Constraints gearbeitet werden, auch wenn das nicht-Validieren bei Migrationen in Umgebungen mit vielen Daten einen hohen Performance-Gewinn bietet. Wenn passende validierte Constraints vorhanden sind, erkennt der Optimizer, dass er Zugriffe auf weitere Tabellen einsparen kann und dadurch die Performance des Statements verbessert wird. Fairerweise muss hinzugefügt werden, dass dieser Effekt verpufft, sobald mehr Spalten selektiert werden, als durch den Foreign Key erfasst sind. 

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.