Widoki zmaterializowane

W przeciwieństwie do zwykłych widoków, widoki zmaterializowane przechowują dane i wykorzystują fizyczną przestrzeń dyskową przydzieloną bazie danych. Widoki zmateializowane są wypełniane danymi generowanymi przez ich bazowe zapytania. Stosuje się je głównie w celu poprawy wydajności dla zapytań zawierających rozproszone dane (zapytania oparte o kilka tabel) lub zawierających podsumowania agregacji w wyniku grupowania. Dzięki nim możemy utworzyć kopię (zwaną też repliką) całej lub części tabeli źródłowej lub kopię wyniku zapytania opartego o kilka tabel. Widoków zmaterializowanych używa się również w celu replikowania danych pomiędzy bazami. Nie trzeba powielać całej tabeli lub ograniczać się do danych tylko jednej tabeli. Replikując pojedynczą tabelę, można zastosować klauzulę WHERE, aby określić jedynie rekordy przeznaczone do powielenia. Jeśli dane w tabeli (lub tabelach) źródłowej ulegną zmianie, możemy poprostu odświeżyć dane zawarte w widoku. Proceś odświeżania może odbywać się automatycznie, lub możemy go wywołać ręcznie. W wielu przypadkach baza może korzystać z dziennika widoku zmaterializowanego, aby przesyłać wyłącznie dane transakcji (zmian dokonanych w tabeli źródłowej). W przeciwnym razie baza danych dla lokalnego widoku zmaterializowanego będzie realizować operację pełnego odświeżania.

Aby utworzyć widok zmaterializowany należy posiadać uprawnienie systemowe CREATE MATERIALIZED VIEW, w przeciwnym razie, podczas próby utworzenia takiego widoku zobaczymy błąd ORA-01031.

GRANT CREATE MATERIALIZED VIEW TO HR;

Widoki zmaterializowane tworzymy tak samo jak zwykłe widoki, a więc na podstawie zapytania, którego wynik chcemy aby był przetrzymywany w widoku, dopisując dodatkowo frazę MATERIALIZED.

Poniżej przykład utworzenia prostego widoku zmaterializowanego, który będzie oparty o jedną tabelę, bez użycia funkcji agregujących, w związku z czym otrzymamy poprostu kopię tabeli źródłowej.

CREATE MATERIALIZED VIEW WZMAT_EMPLOYEES AS

SELECT * FROM EMPLOYEES ORDER BY 1;

Widoki zmaterializowane odpytujemy tak jak zwykłe tabele.

SELECT * FROM WZMAT_EMPLOYEES;

Informacje o tym jakie mamy utworzone widoki zmaterializowane znajdziemy w widoku USER_MVIEWS.

SELECT * FROM USER_MVIEWS;

Widoki zmaterializowane możemy także utworzyć na podstawie zapytania odnoszącego się do dwóch, lub większej ilości tabel.

CREATE MATERIALIZED VIEW TRZY_TABELE AS

SELECT EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_NAME, CITY

FROM EMPLOYEES

JOIN DEPARTMENTS USING (DEPARTMENT_ID)

JOIN LOCATIONS USING (LOCATION_ID);

Istnieje równiez mozliwość utworzenia widoku, który będzie zawierał zagregowane wartości z jednej lub kilku tabel.

CREATE MATERIALIZED VIEW AGREGATY AS

SELECT CITY, DEPARTMENT_NAME, COUNT(*)

FROM EMPLOYEES

JOIN DEPARTMENTS USING (DEPARTMENT_ID)

JOIN LOCATIONS USING (LOCATION_ID)

GROUP BY CITY, DEPARTMENT_NAME;

Domyślnym ustawieniem przy tworzeniu widoków zmaterializowanych jest to, iż są one już na wstępie załadowane danymi z bazowego zapytania, dzieje się tak za pomocą klauzuli BIULD IMMEDIATE, której nawet nie musimy uwzględniać. Widok zmaterializowany można również wygenerować tak aby powstał wstepnie bez żadnych danych, należy wtedy jednak już to zaznaczyć za pomocą klauzuli BUILD DEFERRED.

CREATE MATERIALIZED VIEW PUSTY

BUILD DEFERRED

AS SELECT * FROM EMPLOYEES;

SELECT * FROM PUSTY;

Powstaje zatem zasadnicze pytanie. Po co nam pusty widok?

Taki widok, jak i widoki utworzone już z danymi (dane są aktualne w momencie utworzenia widoku, potem dane w tabeli źródłowej mogą przecież ulec zmianie) możemy odświeżać, czyli uzupełnić aktualnymi danymi z tabel źródłowych.

Odświeżanie widoków zmaterializowanych.

W celu odświeżenia widoku zmaterializowanego wykorzystuje się procedurę REFRESH z pakietu DBMS.MVIEW, w której uwzględniamy dwa podstawowe parametry. Pierwszy z nich to nazwa widoku zmaterializowanego, który chcemy odświezyć, drugi zaś to metoda jakiej chcemy uzyć aby ten widok odświeżyć.

Do wyboru mamy dostępne metody:

- COMPLETE ('C') – polega na wyczyszczeniu, a następnie skopiowaniu całej zawartości tabel źródłowych do repliki.

- FAST ('F') – do repliki kopiowane są wyłącznie zmiany dokonane w tabelach źródłowych od czasu ostatniego odświeżania.

- FORCE ('?') - początkowo próbuje wykonać odświeżanie przyrostowe, a jesli to nie będzie możliwe to wykonywane jest odświeżanie pełne. Jeśli w ogóle nie podamy wartości drugiego parametru, to jest to ustawienie domyślne.

Aby jednak umożliwić systemowi Oracle odświeżanie widoków zmaterializowanych metodą FAST, należy dla widoku zmaterializowanego utworzyć dzienniki, które będą przechowywały informacje o zmianach tabeli źródłowej, jakie zaszły od ostatniego odświeżania.

Przykładowe odświeżanie widoku zmaterializowane ZMAT_EMPLOYEES.

EXECUTE DBMS_MVIEW.REFRESH('WZMAT_EMPLOYEES');

Nastąpiło odświeżanie całkowite, ponieważ nie istnieją dzienniki, w których przechowywana byłaby informacja, które wiersze w tabeli Employees się zmieniły, jakie doszły a jakie zostały w ogóle usunięte.

Dziennik nakładamy na tabelę źródłową:

CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

Informacje o utworzonych dziennikach znajdziemy w widoku USER_MVIEW_LOGS.

SELECT * FROM USER_MVIEW_LOGS;

Do takiego dziennika jak najbardziej możemy zajrzeć

SELECT * FROM MLOG$_EMPLOYEES;

Na razie jest on pusty, ponieważ nie zaszły jeszcze żadne zmiany na tabeli źródłowej. Wprowadzimy więc kilka zmian i zajrzymy do niego ponownie.

UPDATE EMPLOYEES SET SALARY = SALARY + 100 WHERE DEPARTMENT_ID = 90;

COMMIT;

UPDATE EMPLOYEES SET SALARY = SALARY + 500 WHERE DEPARTMENT_ID IS NULL;

COMMIT;

I jescze raz zaglądamy do dziennika.

SELECT * FROM MLOG$_EMPLOYEES;

Widzimy, że w dzienniku odłożone są informacje o czterech rekordach, w których uległy zmiany.

Przy użyciu procedury REFRESH możemy też odświezyć kilka widoków na raz. Wystarczy nazwy tych widoków podać jako pierwszy parametr, oddzielając je przecinkami, a nastepnie podać informację, w jaki sposób te widoki mają zostać odświeżone.

BEGIN

DBMS_MVIEW.REFRESH('WIDOK_ZMAT1,WIDOK_ZMAT2,WIDOK_ZMAT3','FC?');

END;

W powyższym przykładzie WIDOK_ZMAT1 zostanie odświezony metodą FAST, WIDOK_ZMAT2 zostanie odświezony metodą COMPLETE, zaś WIDOK_ZMAT3 metodą FORCE.

Mamy równiez możliwość skorzystania z procedury REFRESH_ALL, która odświeży wszystkie widoki zmaterializowane. Procedura ta nie przyjmuje żadnych parametrów i odświeża widoki jeden po drugim.

EXECUTE DBMS.MVIEW.REFRESH_ALL;

Tworząc widoki zmaterializowane z myślą o ich późniejszym odświeżaniu, należy podać informację czy mają one bazować na wartościach kluczy głównych czy też na wartościach pseudokolumny RowID. Domyślnie, jeśli tego nie uwzględnimy będą bazowały na kluczach głównych. Takie klucze oczywiście muszą istnieć na tabelach, na podstawie których tworzymy widoki zmaterializowane.

Przykładowe utworzenie obu wariantów widoków zmaterializowanych:

W oparciu o klucz główny

CREATE MATERIALIZED VIEW ZMAT_PK

REFRESH FORCE

WITH PRIMARY KEY

AS SELECT * FROM EMPLOYEES;

W oparciu o RowID

CREATE MATERIALIZED VIEW ZMAT_ROWID

REFRESH FORCE

WITH ROWID

AS SELECT * FROM EMPLOYEES;

Przy tym wyborze należy wziąć m.in. pod uwagę:

Stabilność systemu – jeśli na serwerze, gdzie znajdują się tabele źródłowe bardzo często następuje odtwarzanie bazy, np. Poprzez eksporty i importy, naleźy pamietać, że wartości kolumny RowID dla wierszy będą ulegały zmianom. Wtedy nalezy wykorzystywać widoki zmaterializowane bazujące na wartościach kluczy głównych.

Rozmiar tabeli dziennika widoku zmaterializowanego – jeśli klucz główny w tabeli nadrzędnej składa się z wielu kolumn, tabela dziennikamoże okazać się znacznie większa niż ta sama tabela bazująca na wartościach pseudokolumny RowID.

Integralność odwołać – aby w ogóle zastosować widok zmaterializowany w oparciu o klucz główny, nalezy taki klucz główny zdefiniowac dla tabeli nadrzędnej. Jeśli nie można tego zrobić, należy tworzyć widoki zmaterializowane bazujące na wartościach pseudokolumny RowID.

Automatyczne odświeżanie widoków zmaterializowanych

Tworząc widok zmaterializowany od razu możemy uwzględnić informacje dotyczące harmonogramu odświeżania. Domyślnym zapisam jest ON DEMAND, czyli odświeżamy ręcznie.

CREATE MATERIALIZED VIEW ZMAT_AUTO

REFRESH FORCE

START WITH SYSDATE NEXT SYSDATE + 1

WITH PRIMATY KEY

AS

SELECT * FROM EMPLOYEES;

Klauzula START WITH informuje o tym kiedy nalezy wykonać pierwszą replikację z tabeli nadrzędnej.

Klauzula NEXT oznacza odcinek czasu pomiędzy kolejnymi odświeżeniami. Biorąc pod uwagę powyższy przykład oznacza to, iż kolejne odświeżanie nastąpi dokładnie po upływie 24 godzin od ostatniego odświeżania. Możemy oczywiście dostosować taki harmonogram wg własnych potrzeb i np ustawić aby odświeżanie następowało np w każdą niedzielę o godzinie 12.

START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE),'NIEDZIELA')+12/24

Czas następnego odświeżania możemy oczywiście zmienić.

ALTER MATERIALIZED VIEW ZMAT_AUTO

REFRESH FORCE

NEXT NEXT_DAY(TRUNC(SYSDATE),'NIEDZIELA')+12/24;

To w jaki sposób i kiedy nastąpi kolejne odświeżanie możemy podejrzeć w widoku USER_SNAPSHOTS (tych informacji nie ma w widoku USER_MVIEWS, tam znajdziemy jedynie informację o tym kiedy widok był odświeżany po raz ostatni).

SELECT NAME, TABLE_NAME, REFRESH_METHOD, LAST_REFRESH, NEXT, START_WITH FROM USER_SNAPSHOTS

WHERE NAME = 'ZMAT_AUTO';

Do automatycznego odświeżania możemy również użyć zapisu ON COMMIT w klauzuli REFRESH. Spowoduje to odźwieżanie widoku zmaterializowanego za każdym razem po zatwierdzeniu transakcji w tabeli źródłowej. Należy jednak pamietać, że nie możemy wtedy już użyć klauzul START WITH oraz NEXT.

CREATE MATERIALIZED VIEW ZMAT_AUTO_ON_COMMIT

REFRESH FORCE ON COMMIT

WITH PRIMARY KEY

AS

SELECT * FROM EMPLOYEES;

Korzystanie z zapisu ON COMMIT wiąże się jednak z pewnymi obostrzeniami, można z niego korzystać jedynie gdy:

-zapytanie korzysta z tabel lokalnych

-widok zmaterializowany oparty jest o jedną tabelę, bez wyliczania agregatów

-widok zmaterializowany wylicza agregaty jedynie w oparciu o jedną tabelę

-widok zmaterializowany wykorzystuje łączenie tabel ale bez wyliczania agregatów.

Co więcej, nalezy posiadać uprawnienie obiektowe ON COMMIT dla wszystkich

tabel, do których odwołuje się widok zmaterializowany i posiadamy uprawnienie obiektowe ON COMMMIT.

W przypadku niespełnienia któregoś z powyższych warunków, próba utworzenia widoku zmaterializowanego zakończy się błędem ORA-12054.

Mechanizm query rewrite

Widoki zmaterializowane mogą być również wykorzystywane przez optymalizator kosztowy w celu modyfikacji ścieżek wykonywania zapytań. Właściwość ta znana jest właśnie jako QUERY REWRITE. Umożliwia ona optymalizatorowi wykorzystanie widoku zmaterializowanego zamiast tabeli lub tabel do których kierowane jest zapytanie, nawet w przypadku jeśli użytkownik nie wymienił widoku zmaterializowanego w zapytaniu.

Dla przykładu, mamy tabelę PRACOWNICY, która zawiera 2000 wierszy. Chcemy sprawdzić ilu pracowników pochodzi z danego miasta.

SELECT CITY, COUNT(*) ILOSC FROM PRACOWNICY

GROUP BY CITY;

Koszt uzyskania wyniku dla tego zapytania wynosi 14.

Nastepnie tworzymy widok zmaterializowany z dodatkowym zapisem ENABLE QUERY REWRITE, na podstawie zapytania które obliczy nam te same wartoście, co poprzednie zapytanie.

Właśnie dzięki tej klauzuli widok zmaterializowany może być zastosowany w operacji przepisywania zapytań.

CREATE MATERIALIZED VIEW WIDOK_PRACOWNICY

REFRESH FORCE ON COMMIT

WITH PRIMARY KEY

ENABLE QUERY REWRITE

AS

SELECT CITY, COUNT(*) ILOSC FROM PRACOWNICY

GROUP BY CITY;

A teraz jeszcze raz zapytamy bazę o ilość pracowników w danym mieście

SELECT CITY, COUNT(*) ILOSC FROM PRACOWNICY

GROUP BY CITY;

Jak widać optymalizator, mimo iż w ogóle w tym zapytaniu nie wskazaliśmy widoku zmaterializowanego, korzysta własnie z niego a nie z tabeli źródłowej a koszt z 14 spadł do 4.

Aby umożliwić zastosowanie widoków zmaterializowanych w celu przepisywania zapytań, wszystkie tabele nadrzędne muszą znajdować się w tym samym schemacie co widok zmaterializowany, a użytkownik musi posiadać uprawnienie systemowe QUERY REWRITE. W przypadku gdyby przynajmniej jedna z tabel znajdowała się poza schematem widoku zmaterializowanego, użytkownik musiałby posiadać uprawnienie systemowe GLOBAL QUERY REWRITE.

Użytkownik podczas pisania zapytania może sam okreslić czy chce w ogóle skorzystać z opcji przepisywania zapytań, czy też nie, używając hintów (REWRITE/NOREWRITE - podpowiedzi dla optymalizatora kosztowego.

Poniżej przykład w którym określimy, iż nie chcemy skorzystać z opcji przepisywania zapytań, właśnie przy uzyciu hinta NOREWRITE.

SELECT /*+NOREWRITE*/ CITY, COUNT(*) ILOSC FROM PRACOWNICY

GROUP BY CITY;

Nie używając hintów, nalezy pamiętać, że optymalizator głównie bazuje na kosztach różnych ścieżek zapytania, w związku z czym należy pamietać o utrzymywaniu aktualnych statystyk.

Aby w ogóle możliwe było skorzystanie z opcji przepisywania zapytań nalezy ustawić następujące parametry inicjalizacyjne:

- OPTIMIZER_MODE = ALL_ROWS lub FIRST_ROWS

- QUERY_REWRITE_ENABLED = TRUE

- QUERY_REWRITE_INTEGRITY = STALE_TOLERATED, TRUSTED lub ENFORCED

Wartość tych parametrów sprawdzimy w widoku V$PARAMETER (oczywiście należy mieć do niej uprawienie = SYSDBA)

SELECT NAME, VALUE FROM V$PARAMETER

WHERE NAME IN ('optimizer_mode','query_rewrite_enabled','query_rewrite_integrity');

Domyślnie parametr QUERY_REWRITE_INTEGRITY jest ustawiony na ENFORCED. W tym trybie muszą być sprawdzone wszelkie ograniczenia. Optymalizator wykorzystuje tylko dane z widoków zmaterializowanychoraz tylko te relacje, które bazują na ograniczeniach ENABLED VALIDATED primary, unique lub foreign key. W trybie TRUSTED optymalizator przyjmuje, że dane w widoku zmaterializowanym są aktualne oraz że relacje zadeklarowane w wymiarach i ograniczeniach są poprawne. W trybie STALE_TOLERATED optymalizator wykorzystuje widoki zmaterializowane ze świeżymi danymi oraz te kóre są poprawne, ale zawierają nieodświeżone dane. Możemy również ustawić wartość tego parametru na FORCE, wtedy optymalizator będzie przepisywał zapytania w celu skorzystania z widoku zmaterializowanego nawet w przypadku, jeśli koszt oryginalnego zapytania będzie niższy.