Hurtownie Danych (Data Warehuses)

Perspektywy zmaterializowane i przepisywanie zapytań: zadania

Zad.1.
Utwórz schemat hurtowni danych do ćwiczeń, wykonując skrypt.
Wczytaj dane do utworzonych tabel, wykonując skrypt

Zad.2.
Utwórz perspektywę materializowaną o nazwie PURCHASES_SUM_MV, która będzie przechowywała zagregowane dane o sumarycznej kwocie (PURCHASE_PRICE) i liczbie sprzedanych produktów (PRODUCT_ID). Perspektywa powinna wykorzystywać tylko tabelę PURCHASES.

Parametry odświeżania perspektywy to:

pierwsze odświeżenie natychmiast po utworzeniu,
odświeżanie automatyczne co 1 minutę,
sposób odświeżania: domyślny (force),
wybierz samodzielnie sposób identyfikowania rekordów (na podstawie definicji tabeli PURCHASES i zapytania definiującego perspektywę).

Sprawdź w słowniku danych informacje o zdefiniowanej perspektywie (sposób odświeżania, zapytanie definiujące, świeżość danych, itd.) oraz o momencie i typie ostatniego odświeżenia perspektywy. Odczytaj zawartość perspektywy.

Zad.3.
Spróbuj dokonać ręcznego odświeżenia perspektywy PURCHASES_SUM_MV w trybie FAST (wykorzystaj procedurę DBMS_MVIEW.REFRESH). Co zaobserwowałaś/eś?

Zad.4.
Zdefiniuj perspektywę zmaterializowaną PURCHASES_SUM_FAST_MV, udostępniającą te same dane co perspektywa PURCHASES_SUM_MV. Zdefiniuj tą perspektywę w taki sposób, aby było możliwe odświeżanie przyrostowe. Udowodnij poprawność działania perspektywy odświeżając ją ręcznie w trybie FAST.

Zad.5.
Przeanalizuj strukturę dziennika perspektywy zmaterializowanej z zadania 4.

Wstaw rekord to tabeli bazowej perspektywy z zadania 4, zmodyfikuj rekord i usuń rekord z tej tabeli. Przeanalizuj zawartość dziennika.

Utwórz dziennik z klauzulami SEQUENCE i INCLUDING NEW VALUES. Przeanalizuj strukturę tego dziennika.

Wstaw rekord to tabeli bazowej perspektywy z zadania 4, zmodyfikuj rekord i usuń rekord z tej tabeli. Przeanalizuj zawartość dziennika.

Zad.6.
Utwórz tabelę PURCHASES_AVG o następującej stukturze:

PRODUCT_ID          varchar2(8) not null
AVG_PURCHASE_PRICE  number

Wypełnij tabelę PURCHASES_AVG danymi nt. średniej ceny sprzedaży produktów (wykorzystaj polecenie CREATE TABLE ... AS SELECT). Następnie dokonaj transformacji tej tabeli do perspektywy zmaterializowanej (użyj opcji ON PREBUILT TABLE). Spróbuj dokonać modyfikacji danych w PURCHASES_AVG. Następnie usuń perspektywę PURCHASES_AVG. Co się dzieje z wcześniej utworzoną tabelą?

Zad.7.
Sprawdź, czy zapytanie:

SELECT t.year, t.month, p.product_id,
       sum (ps.purchase_price) as sum_of_sales,
       count (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, t.month, p.product_id;

może służyć jako zapytanie definiujące perspektywę zmaterializowaną odświeżaną przyrostowo? Użyj procedury DBMS_MVIEW.EXPLAIN_MVIEW. Przygotuj najpierw środowisko – utwórz tabelę MV_CAPABILITIES_TABLE skryptem utlxmv.sql.

Zad.8.
Uwaga: zadnie jest opcjonalne. Przygotowuje środowisko do następnych zadań, w których są analizowane plany wykonania zapytań. Nie we wszystkich wersjach Oracle tabela PLAN_TABLE jest potrzebna w schemacie każdego użytkownika; szczegółowych informacji udziela prowadzący zajęcia.

Jeśli tabels PLAN_TABLE jest wymagana, wówczas utwórz ją w swoim schemacie skryptem utlxplan.sql.

Przydatne polecenia:

SET AUTOTRACE ON – włączenie trybu wyjaśniania zapytania, wyświetlany jest plan wykonania zapytania, statystyki oraz wynik zapytania
SET AUTOTRACE TRACEONLY – włączenie trybu wyjaśniania zapytania, wyświetlany jest plan wykonania zapytania oraz statystyki, nie są wyświetlanie wyniki zapytania
SET AUTOTRACE EXPLAIN – włączenie trybu wyjaśniania zapytania, wyświetlany jest tylko plan wykonania zapytania
SET AUTOTRACE OFF – wyłączenie trybu wyjaśniania zapytania

Zad.9.
Utwórz poniższą perspektywę MV_Prod_Purchase.

create materialized view MV_Prod_Purchase
build immediate
refresh force
next sysdate + (1/24)
with rowid
enable query rewrite
as
SELECT p.product_name, p.product_id, category,
       sum (pu.purchase_price) as sum_of_sales, count(*)
FROM product p, purchases pu
WHERE p.product_id = pu.product_id
GROUP BY p.product_name, p.product_id, p. category;

Zbadaj plan poniższego zapytania i stwierdź, czy zostało przepisane.

SELECT p.product_name,
       sum (pu.purchase_price) as sum_of_sales
FROM product p, purchases pu
WHERE p.product_id = pu.product_id
GROUP BY p.product_name;

Zad.10.
Zmodyfikuj definicję perspektywy MV_Prod_Purchase z zadania 9, tak aby mogła być odświeżana przyrostowo. Wykonaj wszystkie inne niezbędne do tego celu polecenia. Potwierdź, że perspektywa jest odświeżana przyrostowo.

Zad.11.
Utwórz taką perspektywę zmaterializowaną, która zostanie wykorzystana do przepisania każdego z poniższych zapytań. Zweryfikuj działanie mechanizmu przepisywania dla zaproponowanej perspektywy. Nie zapomnij o zebraniu statystyk dla tabel.

SELECT t.year, t.month, p.product_id,
       sum (ps.purchase_price) as sum_of_sales,
       count (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, t.month, p.product_id;

SELECT t.month, t.year, p.product_id,
       AVG(ps.purchase_price) avg_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, t.month, p.product_id;

SELECT t.year, p.product_id,
       SUM (ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, p.product_id;

SELECT t.year, t.month, p.product_name,
       SUM (ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, t.month, p.product_name;

Zad.12.
Dana jest perspektywa zmaterializowana CUST_SALES_MV (jak niżej) i przykładowe zapytanie. Wykonaj następujące zadania:

sprawdź, czy plan wykonania zapytania wykorzystuje perspektywę,
usuń definicję klucza podstawowego tabeli CUSTOMER i powtórz podpunkt a),
zdefiniuj obiekt typu wymiar, który powinien ponownie umożliwić wykorzystanie perspektywy przy wykonaniu zapytania.

CREATE MATERIALIZED VIEW cust_sales_mv
ENABLE QUERY REWRITE
AS
SELECT c.customer_id, SUM(ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM customer c, purchases ps
WHERE c.customer_id = ps.customer_id
GROUP BY c.customer_id;

SELECT c.occupation, SUM(ps.purchase_price) as sum_of_sales
FROM purchases ps, customer c
WHERE c.customer_id = ps.customer_id
GROUP BY c.occupation;

Zad.13.
Utwórz perspektywę zmaterializowaną SALES_MV, której definicję przedstawiono poniżej. Zmodyfikuj zawartość tabeli PURCHASES i zatwierdź transakcję. Sprawdź aktualność danych perspektywy SALES_MV (kolumna STALENESS perspektywy USER_MVIEWS).

CREATE MATERIALIZED VIEW SALES_MV
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT SUM (purchase_price), state_id
FROM purchases
GROUP BY state_id;

Następnie wykonaj poniższe polecenia i wyciągnij wnioski.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=STALE_TOLERATED;
SELECT SUM (purchase_price), state_id
FROM purchases
GROUP BY state_id;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED;

SELECT SUM (purchase_price), state_id
FROM purchases
GROUP BY state_id;

Co należy zrobić, aby perspektywa SALES_MV była znowu używana przy przepisywaniu zapytań w trybie poziomu integralności ENFORCED?

Zad.14.
Utwórz perspektywę zmaterializowaną SALES_CPGT, której definicję przedstawiono poniżej. Zapewnij, aby poniższe zapytanie było przepisywane w oparciu o perspektywę SALES_CPGT.

create materialized view SALES_CPGT
enable query rewrite
as
select c.customer_id, p.category, t.month, g.state_name,
       sum(pu.purchase_price)
from customer c, product p, time t, geography g, purchases pu
where pu.product_id=p.product_id
and pu.time_key=t.time_key
and pu.customer_id=c.customer_id
and pu.state_id=g.state_id
group by c.customer_id, p.category, t.month, g.state_name;

select c.county, p.category, t.quarter, g.state_name,
       sum(pu.purchase_price)
from customer c, product p, time t, geography g, purchases pu
where pu.product_id=p.product_id
and pu.time_key=t.time_key
and pu.customer_id=c.customer_id
and pu.state_id=g.state_id
group by c.county, p.category, t.quarter, g.state_name;

Zad.15.
Korzystając z odpowiedniej perspektywy słownikowej, wyświetl informacje o utworzonych perspektywach zmaterializowanych w schemacie bieżącego użytkwnika.
Napisz skrypt, który wygeneruje polecenie usuwające wszystkie perspektywy zmaterializowane z twojego schematu.

Zadania dodaktowe

Zad.16.
Korzystając z Access Advisor, uzyskaj rekomendacje dot. indeksów i perspektyw zmaterializowanych optymalizujących zbiór poniższych zapytań analitycznych.

select c.county, p.category, t.quarter, sum(pu.purchase_price)
from customer c, product p, time t, purchases pu
where pu.product_id=p.product_id
and pu.time_key=t.time_key
and pu.customer_id=c.customer_id
group by rollup (c.county, p.category, t.quarter);

select g.region, p.category, t.year,
       sum(pu.purchase_price), sum(pu.shipping_charge)
from geography g, product p, time t, purchases pu
where pu.state_id=g.state_id
and pu.product_id=p.product_id
and pu.time_key=t.time_key
group by g.region, p.category, t.year;

select g.region, p.category, sum(pu.purchase_price)
from geography g, product p, purchases pu
where pu.state_id=g.state_id
and pu.product_id=p.product_id
group by g.region, p.category;

Zad.17.
Zdefiniować obciążenie zawierające wszystkie zapytania z SQL Cache użytkownika, z którego korzystamy wykonując ćwiczenia. Wyświetlić treść wczytanych zapytań.

Hurtownie Danych i Przetwarzanie Analityczne

Data Warehouses and Analytical Processing

dr hab. inż. Robert Wrembel, prof. nadzw.

Perspektywy zmaterializowane i przepisywanie zapytań: zadania