Optymalizacja zapytań, perspektywy zmaterializowane, Access Advisor

1. Utwórz schemat relacji do ćwiczeń, wykonując skrypt.

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

Parametry odświeżania perspektywy to:

• pierwsze odświeżenie natychmiast po utworzeniu,
• kolejne odświeżenie co 1 minutę,
• sposób odświeżania: wybór automatyczny,
• 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.),
• momencie i typie ostatniego odświeżenia perspektywy.

Odczytaj zawartość perspektywy.

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ś?

4. Zdefiniuj perspektywę materializowaną PURCHASES_SUM_FAST_MV, udostępniającą te same informacje, 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.

5. 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 materializowanej (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ą?

6. 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 (skrypt utlxmv.sql).

7. Przygotuj środowisko do następnych zadań. Utwórz tabelę PLAN_TABLE (skrypt utlxplan.sql). Uwaga, nie we wszystkich wersjach Oracle tabela ta jest potrzebna w schemacie każdego użytkownika; szczegółowych informacji udziela prowadzący zajecia.

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

8. 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;

9. Dane są: perspektywa zmaterializowana CUST_SALES_MV (jak niżej) i przykładowe zapytanie.

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;

a) sprawdź, czy plan wykonania zapytania wykorzystuje perspektywę

b) usuń definicję klucza podstawowego tabeli CUSTOMER i powtórz podpunkt a)

c) zdefiniuj obiekt typu wymiar, który powinien ponownie umożliwić wykorzystanie perspektywy przy wykonaniu zapytania

10. 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?

11. 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;

12. 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;

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