Materialized Views für Data-Warehouses Art der Info

Betrifft
DWH1: Materialized Views für Data-Warehouses
Art der Info Technische Info, Oracle8i
Quelle
Aus dem AI8-EF Kurs der Trivadis (Enterprise Features)
Autor
Andri Kisseleff ([email protected])
Schlüsselworte
Datawarehouses, Materialized Views, Materialized View Logs, Query Rewrite
Ziel dieses Artikels
Mit Oracle8i wurden diverse neue Möglichkeiten im Warehousing-Bereich eingeführt. In
diesem ersten Artikel der Warehousing-Artikel-Serie werden Materialized Views und Query
Rewrite erläutert, welche typischerweise in Data-Warehouses von grossem Nutzen sein
können.
Der einfachen Verständlichkeit halber werden die neuen Features anhand von Beispielen mit
EMP/DEPT (oder BIG_EMP/DEPT) erläutert.
Im zweiten Artikel dieser Serie werden weitere Möglichkeiten im Zusammenhang mit
Materialized Views unter Verwendung von normalisierten und denormalisierten Dimensionen
und Hierarchien geschildert. Im dritten Artikel werden diverse Neuerungen wie ROLLUP,
CUBE, TOP-n und SAMPLE erläutert.
Weitere für Warehouses relevante Themen werden in weiteren Artikeln abgehandelt werden.
Materialized Views: das Konzept
Oracle8i erlaubt die Ergebnismenge einer View abzuspeichern, das heisst zu materialisieren.
Diese abgespeicherten Redundanzen (auch Summary-Tables genannt) können von Oracle
automatisch (COMPLETE, FAST oder ON COMMIT) oder on-demand nachgeführt
werden. Der Optimizer ist in der Lage SQL-Befehle, welche ein bereits vorberechnetes und
redundant abgespeichertes Resultat verwenden können zu identifizieren und umzuschreiben.
Das Resultat einer Abfrage wird somit nicht aus den Basis-Tabellen berechnet, sondern aus
der Summary-Table gelesen. Dieser Vorgang wird QUERY REWRITE genannt und ist für
die Applikation transparent, das heisst die SQL-Befehle müssen nicht angepasst oder mit
Hints versehen werden.
Solche MVIEWs können indexiert werden und können auch partitioniert abgespeichert
werden.
Privilegien und Voraussetzungen
Für das Erstellen von MVIEWs ist das CREATE MATERIALIZED VIEW oder das
CREATE ANY MATERIALIZED VIEW System-Privileg notwendig. Um Query-Rewrite zu
ermöglichen ist das QUERY REWRITE oder das GLOBAL QUERY REWRITE SystemPrivileg notwendig. Der INIT.ORA-Parameter QUERY_REWRITE_ENABLED muss auf
TRUE gesetzt sein oder auf Session-Ebene aktiviert werden.
GRANT CREATE MATERIALIZED VIEW TO olap;
GRANT QUERY REWRITE TO olap;
QUERY_REWRITE_ENABLED=true/false
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
Will man ein automatisches Refresh der MVIEWs (inkrementell (FAST) oder komplett
(COMPLETE)) verwenden, so ist mindestens ein Job-Queue-Prozess zu konfigurieren.
JOB_QUEUE_INTERVAL
JOB_QUEUE_PROCESSES
= n
= n
# Wakeup-Interval in Sek.
# Anzahl Prozesse
Damit MVIEWs verwendet werden können muss mit dem Cost-based Optimizer gearbeitet
werden und die Tabellen, sowie die MVIEWs müssen über Optimizer-Statistiken verfügen.
Materialized Views: die Möglichkeiten
MVIEWs können bei Oracle8i 8.1.5 für Query Rewrite für Aggregationen, für Joins und für
die Kombination von beidem verwendet werden.
Beispiel Aggregation:
CREATE MATERIALIZED VIEW emp_summary
ENABLE QUERY REWRITE
AS SELECT deptno, job, SUM(sal)
FROM
emp
GROUP BY deptno, job;
Beispiel Join:
CREATE MATERIALIZED VIEW emp_dept
ENABLE QUERY REWRITE
AS SELECT e.*, dname, loc
FROM
emp e, dept d
WHERE e.deptno = d.deptno;
Beispiel Aggregation und Join:
CREATE MATERIALIZED VIEW emp_dept_summary
ENABLE QUERY REWRITE
AS SELECT dname, job, SUM(sal) AS sum_sal
FROM
emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname, job;
In diesen Beispielen wurden die MVIEWs direkt bei der Erstellung für QUERY REWRITE
freigegeben. Es ist auch möglich, diese erst nachträglich mit ENABLE freizugeben,
respektive mit DISABLE das Query Rewrite zu unterbinden:
ALTER MATERIALIZED VIEW emp_dept_summary
ENABLE QUERY REWRITE;
QUERY REWRITE ist nicht unterstützt für Materialized Views, welche eine einfache
Selektion (ohne Aggregation) auf eine einzelne Tabelle (ohne Join) darstellen. Folgende
MVIEW wird für Query Rewrite nicht verwendet:
CREATE MATERIALIZED VIEW emp10
ENABLE QUERY REWRITE
AS SELECT empno, ename, job, sal, comm
FROM
emp
WHERE deptno = 10;
Bei der Erstellung von Materialized Views ist es ebenfalls möglich Tablespace, Storage
Parameter, etc. zu spezifizieren.
Ebenfalls unterstützt ist das erstellen der Tabelle (z.B. Parallel, SQL*Loader Direct Path, etc.)
und das nachfolgende Definieren der MVIEW auf die bereits existierende Tabelle:
CREATE MATERIALIZED VIEW emp_summary
ON PREBUILT TABLE
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT deptno, job, sum(sal) AS sum_sal,
COUNT(sal) count_sal, COUNT(*) count_all
FROM
emp
GROUP BY deptno, job
Materialized Views: Query-Rewrite
Sind alle Voraussetzungen erfüllt (siehe Integritätsfragen und Restriktionen weiter unten), so
kann so eine MVIEW nun vom Oracle-Optimizer für Query Rewrite verwendet werden.
Führt man z.B. den folgenden Befehl aus, so wird wie im Execution Plan erkennbar nicht die
EMP- und die DEPT-Tabelle für das Join und die Aggregation gelesen, da das Resultat ja in
der MVIEW EMP_DEPT_SUMMARY vorberechnet abgespeichert ist.
SELECT dname, job, SUM(sal) AS sum_sal
FROM
emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname, job;
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE
1
0
TABLE ACCESS (FULL) OF 'EMP_DEPT_SUMMARY'
Dass hier bei grossen Datenmengen ein erheblicher Performancevorteil vorliegt ist
selbstsprechend. Interessant ist aber ebenfalls, dass die Funktionalität (die Verwendbarkeit)
von MVIEWs auch mit kleinsten Datenmengen getestet werden kann.
Materialized Views: Refreshes
Falls auf den Basis-Tabellen, in unserem Beispiel EMP und DEPT, Transaktionen ausgeführt
werden, so ergibt sich die Notwendigkeit die redundant in den MVIEWs abgespeicherten
Resultate mit- oder nachzuberechnen.
Diese Refreshes können automatisch (mit internen Triggern und/oder Job-Queue Prozessen),
oder manuell (on-demand) nachgeführt werden.
Für das Refresh "ON COMMIT" ist weiter unten ein Kapitel reserviert.
Complete Refresh "on-demand"
Für das Refresh on-demand steht ein Package DBMS_MVIEW zur Verfügung. Dieses kann
komplette oder inkrementelle Refreshes durchführen. Bei einem COMPLETE Refresh wird
der Inhalt der MVIEW mit per Default mit DELETE gelöscht und die MVIEW vollständig
neu berechnet (INSERT INTO SELECT FROM). Dieses Vorgehen ist dann angebracht, wenn
die Datenmengen nicht allzu gross sind und die Refreshes nicht allzu häufig nachgeführt
werden müssen. Für diese Art von Refresh sind keine LOG-Tabellen auf den Basis-Tabellen
notwendig.
EXECUTE DBMS_MVIEW.REFRESH('emp_dept_summary');
(Wer "Snapshots" von Oracle7 und Oracle8 kennt: DBMS_MVIEW ist ein PUBLIC
SYNONYM für DBMS_SNAPSHOT! "Snapshots" im Sinne von "Snapshots für
Replikation", wie wir diese von früher her kennen, heissen übrigens neu ebenfalls
"Materialized Views".)
Fast Refresh "on-demand"
Bei FAST-Refreshes, werden nur die Änderungen ( INSERT, UPDATE, DELETE), welche
auf der oder den Basistabellen ausgeführt werden inkrementell auf die MVIEW appliziert,
diese also nicht komplett neu aufgebaut. Damit Oracle in der Lage ist nur die Änderungen zu
applizieren muss auf den betroffenen Basis-Tabellen je ein Materialized View LOG erstellt
werden. Dieses Log (eine Tabelle), wird von Oracle durch jede Transaktion auf der BasisTabelle durch interne Trigger automatisch nachgeführt.
Ein Beispiel der Erstellung eines solchen MVIEW LOGs für inkrementelle FAST-Refreshes
ist:
CREATE MATERIALIZED VIEW LOG ON emp
WITH ROWID (deptno,job,sal) INCLUDING NEW VALUES;
Für welche Art von MVIEW welche Art von MVIEW-Log supported ist (Primary Key,
ROWID, INCLUDING NEW VALUES, etc.) ist in der Tabelle 29-1 des Tuning Guide
beschrieben.
Für intelligente FAST-Refreshes z.B. bei aggregierenden Funktionen, sollte Oracle die
Möglichkeit haben für die Neuberechnung der Aggregate nur die DELTAs zu berücksichtigen
und nicht immer bei jedem Refresh die ganze Aggregation für die ganze Basis-Tabelle(n) neu
durchführen zu müssen. Hierzu sind je nach Funktion, welche verwendet wird (AVG, MAX,
MIN, SUM, etc.) Zusatzattribute in der MVIEW-Definition zwingend notwendig. Die Tabelle
29-2 im Tuning Guide zeigt die notwendigen Zusatzattribute in den MVIEWs für FAST
REFRESHES.
Ein Beispiel wo ein FAST-Refresh on-demand mit einem entsprechenden MVIEW-LOG
(siehe oben) funktioniert ist:
CREATE MATERIALIZED VIEW emp_summary
REFRESH FAST /* on-demand, but fast */
ENABLE QUERY REWRITE
AS SELECT deptno, job, sum(sal) as sum_sal,
COUNT(sal) count_sal, COUNT(*) count_all
FROM
emp
GROUP BY deptno, job;
Wie auch bei den Materialized Views, können selbstverständlich auch hier TABLESPACE,
STORAGE-Clause, etc. spezifiziert werden.
Das individuelle Refresh für diese MVIEW würde dann mit DBMS_MVIEW.REFRESH
durchgeführt (F=FAST, C=COMPLETE, ?=was möglich ist, je nach dem ob es ein LOG gibt
oder nicht):
SQL> EXECUTE DBMS_MVIEW.REFRESH('emp_summary','F');
PL/SQL procedure successfully completed.
SQL>
Ebenfalls besteht die Möglichkeit alle MVIEWs zu refreshen:
SQL> EXECUTE DBMS_MVIEW.REFRESH_ALL;
PL/SQL procedure successfully completed.
SQL>
Oder so:
SQL> DECLARE v_nbr_of_failures INTEGER;
2 BEGIN
3
DBMS_MVIEW.REFRESH_ALL_MVIEWS
4
(
5
NUMBER_OF_FAILURES
=> v_nbr_of_failures,
6
METHOD
=> '?',
7
ROLLBACK_SEG
=> 'RS01',
8
REFRESH_AFTER_ERRORS
=>
FALSE,
-only
TRUE
if
ATOMIC_REFRESH=FALSE
9
ATOMIC_REFRESH
=> TRUE
-- in one single TX for all MVIEWs
10
);
11
DBMS_OUTPUT.PUT_LINE('Errors: '||TO_CHAR(v_nbr_of_failures));
12 END;
13 /
Errors: 0
PL/SQL procedure successfully completed.
SQL>
Weitere Procedures für z.B. alle "dependent" MVIEWs, etc. bestehen ebenfalls in
DBMS_MVIEW.
Die in den MVIEW-Logs nicht mehr notwendigen Transaktions-Einträge können nach Bedarf
(wo notwendig) gepurged werden:
SQL> EXECUTE DBMS_MVIEW.PURGE_LOG('emp');
PL/SQL procedure successfully completed.
SQL>
etc., etc.
Automatisches Refresh
Anstelle der Möglichkeit das Refresh manuell gezielt mit DBMS_MVIEW durchzuführen,
besteht auch die Möglichkeit diese Aufgabe mit JOBs zu automatisieren. Beim Erstellen der
MVIEW werden der erstmalige REFRESH-Zeitpunkt und das REFRESH-Intervall
spezifiziert.
Folgendes Beispiel macht ein erstes Refresh heute Nacht um 22:00 und dann jeweils wieder
ein Refresh jede Nacht um 22:00.
CREATE MATERIALIZED VIEW emp_summary
ON PREBUILT TABLE
REFRESH FAST
START WITH to_date(to_char(sysdate,'dd-mon-yy')||' 22:00','DD-MON-YY HH24:MI')
NEXT trunc(sysdate+1)+1/24*22
ENABLE QUERY REWRITE
AS SELECT deptno, job, sum(sal) as sum_sal,
count(sal) count_sal, count(*) count_all
FROM
emp
GROUP BY deptno, job;
Es wird hierbei ein JOB mit DBMS_JOB submitted, welcher sich dann jeweils selber wieder
neu submitted:
Jobs of current Schema in Job-Queue
DB1.TTCNT01.TTC.TRIVADIS.COM
25.10.1999 13:55 - 02.11.1999 14:32 (192.6 h)
JOB LAST_DATE LAST_SEC THIS_SEC NEXT_DATE NEXT_SEC BROKEN FAIL
------ --------- -------- -------- --------- -------- ------ ---INTERVAL
-------------------------------------------------------------------------------WHAT
-------------------------------------------------------------------------------81
02-NOV-99 22:00:00 N
trunc(sysdate+1)+1/24*22
dbms_refresh.refresh('"OLAP"."EMP_SUMMARY"');
Refresh Fast on COMMIT
Bei Refresh Fast on Commit ändert eine Transaktion auf der Basis-Tabelle in der selben
Transaktion auch die MVIEW. Die Daten in der MVIEW sind somit immer up-to-date.
Diverse Einschränkungen sind zu berücksichtigen und einzuhalten:
ON COMMIT Refreshes sind nur bei Single Table Aggregationen oder join-only
MVIEWs erlaubt, nicht aber deren Kombination
Single Table Aggregationen bei Refresh ON COMMIT:
SUM, AVG, STDDEV und VAR brauchen neben COUNT(*) noch zwingend weitere
COUNT und SUM Ausdrücke in der SELECT Clause
Immer COUNT(*) und in den meisten Fällen COUNT(expr) (Tabelle 29-2 im Tuning
Guide)
Ein REFRESH ON COMMIT ohne "FAST" und/oder ohne MATERIALIZED VIEW
LOG ist auf jeden Fall zu vermeiden
Oracle vermeidet dies leider nicht automatisch, so kann man falsche (für die Performance
untaugliche) REFRESH ON COMMIT definieren
Das folgende Beispiel zeigt das falsche Aufsetzen von einer ON COMMIT MVIEW und
das Resultat aus V$SQLAREA, welches erklärt, weshalb man beim COMMIT auf der
BIG_EMP Tabelle einige Minuten warten muss...
CREATE MATERIALIZED VIEW big_emp_aggr
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS SELECT deptno,SUM(sal) SUM_SAL, COUNT(*) COUNT_ALL,
COUNT(SAL) COUNT_SAL
FROM big_emp
GROUP BY deptno
/
UPDATE BIG_EMP SET SAL=SAL+1 WHERE ROWNUM = 1;
SET TIMING ON
COMMIT; -- seeeeehr lange warten
In V$SQLAREA sieht man, wie das Refresh gemacht wird (!):
delete from "ORA8I"."BIG_EMP_AGGR";
INSERT INTO "ORA8I"."BIG_EMP_AGGR"("DEPTNO","SUM_SAL","COUNT_ALL","COUNT_SAL")
SELECT "BIG_EMP"."DEPTNO",SUM("BIG_EMP"."SAL"),COUNT(*),COUNT("BIG_EMP"."SAL") FROM
"BIG_EMP" "BIG_EMP" GROUP BY "BIG_EMP"."DEPTNO";
ALTER SUMMARY "ORA8I"."BIG_EMP_AGGR" COMPILE;
Für ein richtiges Refresh on COMMIT sind zusätzlich wesentlich:
Das MATERIALIZED VIEW LOG muss für MVIEWs welche mit ON COMMIT
nachgeführt werden sollen immer ein ROWID-MVIEW-Log sein
Wenn die MVIEW aggregierende Funktionen beinhaltet so müssen alle Attribute
(alte und neue Werte), welche in der MVIEW sind auch im MVIEW-Log
vorhanden sein
Das MVIEW-Log und die MVIEW müssen somit richtigerweise so aufgebaut werden:
CREATE MATERIALIZED VIEW LOG ON big_emp WITH ROWID (deptno,sal) INCLUDING NEW
VALUES;
CREATE MATERIALIZED VIEW big_emp_aggr
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT deptno,SUM(sal) sum_sal, COUNT(*) count_all, COUNT(sal) count_sal
FROM big_emp
GROUP BY deptno;
Für eine ON COMMIT MVIEW mit einem Join ist zu berücksichtigen, dass die ROWIDs der
Basistabellen in der MVIEW ebenfalls spezifiziert werden müssen.
Das folgende Beispiel zeigt:
Die richtige Syntax für zwei MVIEW-Logs und eine MVIEW (FAST ON
COMMIT) für eine MVIEW mit Join
Hier wird die Tabelle vorkreiert und dann auf die bestehende Tabelle die MVIEW
generiert
Die Indexes auf den ROWID-Attributen in der MVIEW sind für eine gute
Performance beim COMMIT auf die Master-Tables von grösster Bedeutung
Das Beispiel von A-Z:
CREATE MATERIALIZED VIEW LOG ON big_emp
WITH ROWID;
CREATE MATERIALIZED VIEW LOG on dept
WITH ROWID;
REM Tabelle vorkreieren:
CREATE TABLE big_emp_dept STORAGE (INITIAL 10M NEXT 1M)
AS SELECT empno, ename, dname,
e.rowid big_emp_rowid, d.rowid dept_rowid
FROM big_emp e, dept d
WHERE e.deptno = d.deptno;
REM Indexes auf die ROWIDs:
CREATE UNIQUE INDEX big_emp_emp_rowid
ON big_emp_dept(big_emp_rowid)
STORAGE (INITIAL 10M NEXT 1M);
CREATE INDEX big_emp_dept_rowid
ON big_emp_dept(dept_rowid)
STORAGE (INITIAL 10M NEXT 1M);
REM weitere Indexes auf der MVIEW:
CREATE UNIQUE INDEX big_emp_emp_empno ON big_emp_dept(empno)
STORAGE (INITIAL 10M NEXT 1M);
REM MVIEW erstellen:
CREATE MATERIALIZED VIEW big_emp_dept
ON PREBUILT TABLE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS select empno, ename, dname,
e.rowid big_emp_rowid, d.rowid dept_rowid
FROM big_emp e, dept d
WHERE e.deptno = d.deptno;
Wird eine ON COMMIT zu refreshende MVIEW richtig aufgesetzt, so verfügt man bei
Transaktionen auf den Basis-Tabellen über eine hervorragende Performance.
Integrität der Materialized Views
Wird eine MVIEW mit REFRESH FAST ON COMMIT nachgeführt, so ist diese per
Definition zu jeder Zeit integer, das heisst die Daten sind konsistent mit den Daten in der oder
den Basis-Tabellen.
In Warehouses ist das ON COMMIT aber nicht die typische Verwendung, da vielleicht die
Daten des aktuellen Monates erst nach der Monatsendverarbeitung und –Bereinigung im
Warehouse sichtbar sein sollen.
Hier stellt sich nun die Frage, ob Oracle eine MVIEW für QUERY REWRITE verwendet,
falls die in der MVIEW abgespeicherten Daten nicht mit den Basis-Daten übereinstimmen.
Dies kann mit einem INIT.ORA-Prameter, respektive auch auf Session-Ebene (mit ALTER
SESSION) beeinflusst werden:
QUERY_REWRITE_INTEGRITY=ENFORCED/TRUSTED/STALE_TOLERATED
ENFORCED (Default):
Nur MVIEWs mit aktuellen Daten (up-to-date) und Beziehungen, welche auf Enforced
Constraints beruhen, werden für Query Rewrite berücksichtigt.
TRUSTED:
Die Daten in MVIEWs müssen up-to-date sein, getraut wird aber den Dimensionen und
den Constraints die mit NOVALIDATE aktiviert wurden und mit RELY als "trusted"
markiert wurden. (ALTER CONSTRAINT xxx RELY;)
STALE_TOLERATED:
Alle vorhanden MVIEWs, Dimensionen und Constraints werden für QUERY
REWRITE wenn möglich genutzt, auch wenn die Daten nicht up-to-date sind.
Restriktionen für MVIEWs
Wir haben mit all diesen Beispielen gesehen, dass Materialized Views eine sehr mächtige
Feature darstellen. Die Technik ist nicht ganz einfach zu erlernen, aber hat man diese einmal
im Griff, so werden Materialized Views für sehr viele Applikationen verwendet werden
können.
Dass eine MVIEW mit Oracle8i 8.1.5 für Query Rewrite verwendet werden kann sind noch
folgende Restriktionen zu berücksichtigen:
Nicht-deterministische Ausdrücke in der Query (SYSDATE, etc.) sind nicht erlaubt
Referenzen auf RAW Datentypen oder Objekt REFs sind nicht zugelassen
Nur Single Block Queries sind möglich, keine Mengenfunktionen wie UNION, etc.
Falls die Materialized View auf einer existierenden Tabelle erstellt wird, muss die
Spaltenpräzision gleich oder kleiner als bei der Originaltabelle sein
Nur lokale Tabellen können als Basistabellen für Materialized Views verwendet
werden
Weder Basistabellen noch MVIEW dürfen dem User SYS gehören
Die FROM Zeile einer Materialized View Definition darf eine Tabelle nur einmal
enthalten
GROUP BY Listen müssen in der Query und in der Materialized View Definition
identisch sein (Ausnahme: Optimierung mit Dimensionen, siehe Artikel 2 dieser
DWH-Serie)
Es sind keine Ausdrücke in den Spalten (z.B. ||) einer Materialized View Definition
erlaubt
Aggregationsfunktionen sind nur auf äusserster Ausdrucksebene, also z.B. AVG(a+b),
nicht aber AVG(a) + AVG(b) erlaubt
Die WHERE Clause einer Materialized View Definition darf nur AND verbundene
inner und outer Joins enthalten
HAVING und CONNECT BY sind nicht erlaubt
Zusammenfassung
In diesem Artikel haben wir aufgezeichnet, dass Oracle8i mit der Einführung der Materialized
Views mächtige neue Möglichkeiten in Warehouses und anderen Applikationen unterstützt.
Zu hoffen ist, dass einige der doch recht einschränkenden Restriktionen in den nächsten
Releases eliminiert werden.
Oracle8i: OLAP – Fazit
Oracle8i: Die erste Datenbank fürs Internet Computing.....
Oracle8i: Die erste Datenbank fürs Warehousing!
Literatur
Oracle8i Tuning: Part No. A67775-01, Kapitel 29-31
Oracle8i SQL Reference: Part No. A67779-01
Oracle8i Application Developer's Guide – Fundamentals: Part No. A68003-01
Trivadis AG
Andri Kisseleff
Rebhaldenweg 1
CH-5507 Mellingen
Tel: +41 (0)56 470 61 31
Fax: +41 (0)56 470 61 32