RELY-Constraints als Hilfe für den Optimizer und die Datenmodellierung

Oft werden dem unbestrittenen Nutzen von Constraints Nachteile im Zeitbedarf für deren Einsatz gegenüber gestellt, dabei lassen sich diese Nachteile oftmals leicht umschiffen. Zudem kann die Geschwindigkeit von Abfragen wesentlich schlechter werden, wenn keine Constraints eingesetzt werden!

“Wir setzen keine Constraints in unserer [großen Datenbank|DWH-DB|...] ein, weil die Performance bei der Beladung dann zu schlecht ist.”
Diese Ausrede gilt spätestens seit der Verfügbarkeit von RELY-Constraints nicht mehr!

Constraints schützen nicht nur die Integrität Ihrer Daten, sie enthalten als Teil des Datenmodells auch Informationen über den Aufbau der Daten. Diese Informationen sind nicht nur für Anwendungsentwickler nützlich sondern dienen dem Optimizer auch dazu, den besten Ausführungsplan für eine Abfrage zu finden.

RELY-Constraints sind eine “Geheimwaffe” im Grabenkampf zwischen Befürwortern und Gegnern von Integrity Constraints, denn Ihre Erstellung erfordert nahezu keine Leistung vom DB-Server.

Wenn Sie Ihrer/Ihren Applikation/en so sehr vertrauen, daß diese völlig konsistente Daten liefern oder Sie sich bei Bestandsdaten auf deren Konsistenz verlassen können, dann können Sie dieses schon seit Oracle 8i verfügbare Attribut nutzen.

Hier eine Aufstellung der Eigenschaften und Einsatzmöglichkeiten von RELY-Constraints:

  • Ein RELY-Constraint wird nicht „enforced“, Oracle verlässt sich („rely“) auf die Angabe des Designers, dass die Daten valide sind. Das Constraint steht also nur informativ im Data Dictionary, ohne bei INSERTs oder UPDATEs aktiv zu werden.

  • RELY kann auf fast alle Arten von Constraints angewandt werden, außer NOT NULL.

  • Ein RELY auf Fremdschlüssel setzt voraus, daß der referenzierte Primärschlüssel ebenfalls im RELY-Modus ist.

  •  Hilft dem Optimizer (und auch Entwicklern), die Beziehungen zwischen Tabellen zu verstehen.
    • Wesentliche Voraussetzung für Query Rewrite, also ein wichtiges Einsatzszenario bei Materialized Views.

  • Voraussetzung für Table Elimination.

  • Praktisch im DWH-Umfeld, wenn die Integrität der Daten bereits durch Beladeprozesse gewährleistet ist und Constraints zu viel Aufwand beim Schreiben bedeuten.

  • Können auch auf Views angewandt werden (und zwar nur RELY-Constraints!). Wenn Abfragen hauptsächlich über Views laufen, ist dies für den Optimizer nützlich (Query Rewrite).
  • Externe Tools können diese Constraints benutzen, um die Zusammenhänge zwischen den Tabellen zu „verstehen“. In einem Praxisfall wurden RELY-Constraints eingesetzt, um zunächst das existierende Datenmodell im Schema klarer zu dokumentieren und zum Teil auch schon die Abfrage-Performance zu verbessern (ohne die Performance beim Schreiben zu beeinträchtigen). Anschließend konnte dieses Datenmodell dann per Reverse Engineering in ein Design-Tool eingelesen werden.

Um die Wirkung von RELY-Constraints zu demonstrieren, legen wir einige Tabellen an:

DROP TABLE demo_typ PURGE;
DROP TABLE demo PURGE;
CREATE TABLE demo
(
  id NUMBER NOT NULL
, name VARCHAR2(50)
, typ VARCHAR2(1)
, CONSTRAINT demo_typ_cc CHECK ( typ IN ('a','b') ) RELY ENABLE NOVALIDATE
);

INSERT INTO demo VALUES( 1, 'Asterix', 'a' );
INSERT INTO demo VALUES( 2, 'Oraculix', 'b' );

CREATE TABLE demo_typ
(
  typ VARCHAR2(1) NOT NULL
, detail VARCHAR2(50) NOT NULL
, CONSTRAINT typ_pk PRIMARY KEY ( typ ) RELY
);

INSERT INTO demo_typ VALUES( 'a', 'Gallier' );
INSERT INTO demo_typ VALUES( 'b', 'Informatiker' );

ALTER TABLE demo ADD
CONSTRAINT dem_demtyp_fk FOREIGN KEY ( typ ) REFERENCES demo_typ ( typ ) RELY ENABLE NOVALIDATE;

Table Elimination

Die Constraints sind innerhalb von Millisekunden angelegt, auch wenn die Tabellen Milliarden von Rows enthalten würden. Optimizer-Features wie Table Elimination oder Filterung über Check-Constraints können sofort genutzt werden. Noch einmal sei hier erwähnt, daß die Datenkonsistenz nun nicht mehr geprüft wird – im Falle inkonsistenter Daten können dann falsche Ergebnisse geliefert werden!

Der folgende Join würde normalerweise Leseoperationen auf beiden Tabellen verursachen. Nicht so, wenn der Optimizer sich aufgrund der Prädikate und der vorhandenen Constraints dagegen entscheiden kann:

SELECT name
 FROM demo d, demo_typ t
 WHERE t.typ = d.typ;

NAME
--------------------------------------------------
Asterix
Oraculix

Execution Plan
----------------------------------------------------------
Plan hash value: 4000794843
--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    2 |    58 |       3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | DEMO |    2 |    58 |       3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D"."TYP" IS NOT NULL)

Note
-----
 - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 8 consistent gets
 0 physical reads
 0 redo size
 467 bytes sent via SQL*Net to client
 419 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 2 rows processed

Der Optimizer hat sich also entschieden, den Join zu demo_typ gar nicht auszuführen, weil dieser für die Spalte “name” nicht nötig ist. Dies ging aber nur, weil

  1. Ein Foreign Key Constraint zwischen den Tabellen existiert und
  2. auf der Tabelle demo_typ ein validierter Primärschlüssel referenziert wird. Damit ist gewährleistet, daß für jeden typ in demo ein und nur ein typ in demo_typ existiert. Der Join würde also die Ergebnismenge aus demo weder vergrößern noch verkleinern.

“Aber die Constraints wurden doch mit NOVALIDATE angelegt?!” – richtig, aber RELY veranlasst den Optimizer, sich dennoch auf die Constraints zu verlassen als wären sie aktiviert und validiert.

Query Rewrite

Ein weiteres, zentrales Optimizer-Feature ist „Query Rewrite“. Es ermöglicht unter anderem, ein SQL im Hintergrund so umzuschreiben, daß anstelle von Tabellen eine Materialized View verwendet werden kann, wenn sie zum ursprünglichen SQL passt:

CREATE MATERIALIZED VIEW demo_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
  SELECT t.detail, count (*)
    FROM demo d, demo_typ t
   WHERE d.typ = t.typ
   GROUP BY t.detail;

Hier zunächst die einfachste Variante des Query Rewrite: Es wird dieselbe Abfrage wie in der Materialized View verwendet:

SELECT t.detail, count (*)
  FROM demo d, demo_typ t
 WHERE d.typ = t.typ
 GROUP BY t.detail;

DETAIL         COUNT(*)
-------------- ----------
Gallier                 1
Informatiker            1

--------------------------------------------------------
| Id | Operation                    | Name      | Rows |
--------------------------------------------------------
|  0 | SELECT STATEMENT             |           |    2 |
|  1 |  MAT_VIEW REWRITE ACCESS FULL| DEMO_MV   |    2 |
--------------------------------------------------------

Query Rewrite kann aber noch mehr:

ALTER SESSION set query_rewrite_integrity='TRUSTED';
SELECT count(*) FROM demo;

---------------------------------------------------------
| Id | Operation                   | Name        | Rows |
---------------------------------------------------------
|  0 | SELECT STATEMENT            |             |    1 |
|  1 | SORT AGGREGATE              |             |    1 |
|  2 | MAT_VIEW REWRITE ACCESS FULL| DEMO_MV     |    2 |
---------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Der Optimizer hat auch diese Abfrage umgeschrieben, da er über folgende Sachverhalte informiert ist:

  1. „typ“ ist der Primärschlüssel von „demo_typ“, d.h., jeder Datensatz in „demo“ passt zu maximal einem Datensatz in „demo_typ“.

  2. „typ“ in der Tabelle „demo“ hat eine Fremdschlüsselbeziehung zu „demo_typ“.

  3. „typ“ in der Tabelle „demo“ ist NOT NULL. Gemeinsam mit dem Fremdschlüssel bedeutet das: Es gibt nicht nur höchstens sondern auch mindestens eine Entsprechung zu jedem Datensatz von „demo“ in „demo_typ“. Im Umkehrschluss heißt das, daß der COUNT in der Materialized View verwendet werden kann, da durch den Join keine Datensätze aus „demo“ ausgelassen werden.

Überträgt man dieses Beispiel auf große Tabellen in einem DWH, dann sollte nun endgültig belegt sein, welches riesige Potential bei vergleichsweise geringen Kosten in Constraints steckt.

Weitere Beispiele zu Constraints finden sich im Script zu meinem Vortrag von der DOAG-Konferenz 2011.

Caveats

  • QUERY_REWRITE_INTEGRITY = STALE_TOLERATED oder TRUSTED einstellen, sonst findet kein Query Rewrite auf RELY-Constraints statt.
  • Noch einmal ACHTUNG: Oracle verlässt sich blind auf die Integrität von RELY-Constraints und kann daher fehlerhafte Antworten liefern, wenn die Integrität in Wahrheit nicht gewährleistet ist!
  • Um von einem RELY-Constraint auf ein reguläres, validiertes Constraint zurück zu wechseln, sollte man zuerst das Constraint auf NORELY umstellen, bevor ein “ENABLE VALIDATE” durchgeführt wird! Es kann sonst passieren, daß das Constraint anschließend als “validated” im Data Dictionary steht, obwohl die Daten inkonsistent sind. Siehe hier.

Weblinks

About these ads

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s