Materialized Views

edit | delete

Autor: Vilma Plum

eingetragen: Montag, 17. August 2009 um 08:08 Uhr (34/2009 Kalenderwoche)

geändert: Montag, 17. August 2009 um 08:08 Uhr (34/2009 Kalenderwoche)

Keywords: perfomance view

Kategorien: DB: Oracle,

Text:

Haben Sie gewusst, dass Materialized Views nicht nur zur Datenreplikation geeignet sind, sondern sich dadurch auch Performanceverbesserungen Ihrer Abfragen erzielen lassen?

Das folgende Beispiel soll Ihnen zeigen, wie nützlich eine Materialized View in Verbindung mit der Option "Query Rewrite" sein kann.

Dabei wird die Abfrage auf eine große Tabelle in eine Abfrage auf eine wesentlich kleinere Tabelle (MView) umgewandelt, wodurch sich die Ausführungszeit und die Kosten der Abfrage deutlich verringern lassen.

Zu diesem Zweck erstellen wir uns zunächst eine große Tabelle BIG_EMP, basierend auf der Tabelle EMP, und analysieren diese.

CREATE TABLE bigemp NOLOGGING AS
SELECT e.* FROM emp, emp, emp, emp, emp e;

INSERT INTO bigemp SELECT * FROM bigemp;

ANALYZE TABLE bigemp COMPUTE STATISTICS;

SELECT COUNT() FROM big_emp;

COUNT()
--------
1075648

Nun muss die SQLPlus-Umgebung entsprechend eingerichtet werden, damit die Zeitmessung und der Ausführungsplan mitausgegeben werden.

SET TIMING ON AUTOTRACE ON EXPLAIN

Hinweis:

Falls an dieser Stelle eine Fehlermeldung zurückgegeben wird, muss das Skript utlxplan.sql im ?\RDBMS\ADMIN-Verzeichnis aufgerufen werden, das die PLAN_TABLE-Tabelle erzeugt.
Gegebenenfalls muss über das Skript plustrce.sql im ?\SQLPLUS\ADMIN-Verzeichnis noch die PLUSTRACE-Rolle angelegt und dem Benutzer zugewiesen werden.

Anschließend erfolgt die Abfrage auf die Tabelle BIG_EMP.

SELECT job, count() FROM bigemp GROUP BY job;

JOB COUNT(*)
--------- --------
ANALYST 153664
CLERK 307328
MANAGER 230496
PRESIDENT 76832
SALESMAN 307328

Abgelaufen: 00:00:03.02

Ausführungsplan
---------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2397 Card=5 Bytes=35)
1 0 SORT(GROUP BY)(Cost=2397 Card=5 Bytes=35)
2 1 TABLE ACCESS (FULL) OF 'BIGEMP'
(Cost=618 Card=1075648 Bytes=7529536)

Der Ausführungsplan verdeutlicht, dass ein Full Table Scan auf die BIGEMP-Tabelle mit Gesamtkosten von 2397 durchgeführt worden ist.

Damit Sie nun eine Materialized View mit der Möglichkeit des "Abfrage-Rewritings" erstellen können, müssen Ihnen folgende Berechtigungen zugewiesen werden:

GRANT QUERY REWRITE, CREATE SNAPSHOT TO ;

In Ihrer eigenen Sitzung müssen Sie noch den Parameter QUERYREWRITEENABLED einschalten mittels:

ALTER SESSION SET queryrewriteenabled=true;

Daraufhin erstellen Sie sich eine Materialized View in der Sie den SELECT-Befehl auf die Tabelle BIGEMP hinterlegen und führen eine Analyse durch.

CREATE MATERIALIZED VIEW mvbigemp
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT job, COUNT() FROM big_emp GROUP BY job;

ANALYZE TABLE mv_big_emp COMPUTE STATISTICS;

Bevor Sie nun die SELECT-Abfrage auf die BIG_EMP-Tabelle wiederholt ausführen, schalten Sie erneut das Autotracing ein und leeren Sie den Shared Pool, damit das Statement neu geparst werden muss.

SET AUTOTRACE TRACEONLY EXPLAIN

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT job, COUNT() FROM bigemp GROUP BY job;

Abgelaufen: 00:00:00.06

Ausführungsplan
--------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=60)
1 0 TABLE ACCESS (FULL) OF 'MVBIGEMP' (Cost=2 Card=5 Bytes=60)

Vergleicht man nun die beiden Ausführungspläne, fällt auf, dass die zweite Abfrage einen Full Table Scan auf MVBIGEMP vornimmt, und demzufolge die Ausführungszeit und die Kosten auch deutlich geringer ausfallen.

Durch die Option ENABLE QUERY REWRITE beim Anlegen der Materialized View, leitet die Datenbank den SELECT-Befehl auf die Tabelle BIGEMP automatisch an die Materialized View MVBIG_EMP um.