1
Bancos de Dados IV
Visões Materializadas
Rogério Costa
Visões Materializadas
Visão contém cópia resultado da consulta
Auxilia no desempenho
Subconjunto dos dados originais
Junções / agregações já realizadas
Podemos construir índices sobre as cópias dos
Consultas em VM
SELECT *
FROM
Anivesariantes_de_Abril
WHERE matr > 100
matr
nome
dia
111 112 … João Maria … 05 10 …
Aniversariantes_de_Abril
matr nome dia
Empregado
matr nome data_nasc 005 Ana 01/03/1960 006 Maria 04/06/1975 … … …
Não é realizado
acesso a tabela
original
Visões Materializadas
Dados da visão podem estar desatualizados
Tem que ser estabelecida política de atualização
Freqüência de atualização dos dados originais e
necessidade de informação atualizada => importantes
fatores a serem considerados
Atualizações da visão freqüentes => queda no
desempenho
Dimensões
Podemos criar a relação lógica de hierarquia
através do comando CREATE DIMENSION
Pode ser em alguns SGBDs (ex. Oracle) em
Visões Materializadas
Sintaxe simplificada de criação
CREATE MATERIALIZED VIEW <name>
<build option>
REFRESH <refresh option> <refresh mode>
[ENABLE|DISABLE] QUERY REWRITE
AS SELECT <select clause>;
<build option> identifica quando a VM será
construída
BUILD IMMEDIATE: construída imediatamente
Visões Materializadas
Refresh Options
COMPLETE – atualiza totalmente a visão
FAST – atualiza a visão de forma incremental
Necessária a existência de um materialized view log
em cada visão acessada (dentre outros requisitos)
FORCE – tenta fazer de forma incremental. Se
Visões Materializadas
Refresh Modes
ON COMMIT – atualiza os dados a cada commit de
transações onde participem as tabelas utilizadas na visão
Mantém os dados atualizados – várias restrições para utilização
ON DEMAND – atualiza os dados somente sob demanda
(DBMS_MVIEW.REFRESH)
START WITH [NEXT] <date> - atualiza os dados em
períodos de tempos pré-determinados (exemplo: todos os
dias as 23h)
Exemplo VM - 1 (1/4)
SELECT p.prod_subcategory, t.calendar_month_desc,
c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold,
COUNT(s.amount_sold) AS count_amount_sold
FROM sales s, products p, times t, customers c
WHERE s.time_id=t.time_id
AND s.prod_id=p.prod_id
AND s.cust_id=c.cust_id
GROUP BY p.prod_subcategory, t.calendar_month_desc,
c.cust_city;
Exemplo VM - 1 (2/4)
---
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---
| 0 | SELECT STATEMENT | | 550K| 39M| | 67102 | | 1 | SORT GROUP BY | | 550K| 39M| 126M| 67102 | | 2 | HASH JOIN | | 1016K| 73M| | 2529 | | 3 | TABLE ACCESS FULL | TIMES | 1461 | 23376 | | 6 | | 4 | HASH JOIN | | 1016K| 58M| | 2373 | | 5 | TABLE ACCESS FULL | PRODUCTS | 10000 | 224K| | 38 | | 6 | HASH JOIN | | 1016K| 35M| 1320K| 1455 | | 7 | TABLE ACCESS FULL | CUSTOMERS | 50000 | 732K| | 106 | | 8 | PARTITION RANGE ALL| | | | | | | 9 | TABLE ACCESS FULL | SALES | 1016K| 21M| | 469 |
---
Exemplo VM - 1 (3/4)
CREATE MATERIALIZED VIEW sum_sales_pscat_month_city_mv ENABLE QUERY REWRITE
AS
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold, COUNT(s.amount_sold) AS count_amount_sold FROM sales s, products p, times t, customers c WHERE s.time_id=t.time_id
AND s.prod_id=p.prod_id AND s.cust_id=c.cust_id
GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;
Exemplo VM - 1 (4/4)
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold,
COUNT(s.amount_sold) AS count_amount_sold FROM sales s, products p, times t, customers c WHERE s.time_id=t.time_id
AND s.prod_id=p.prod_id AND s.cust_id=c.cust_id
GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;
--- | Id | Operation | Name | Rows | Bytes | --- | 0 | SELECT STATEMENT | | 121K| 9020K| | 1 | TABLE ACCESS FULL | SUM_SALES_PSCAT_MONTH_CITY_MV | 121K| 9020K| ---
Exemplo - Especificando
dimensões
Exemplo:
CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory) LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup ( product CHILD OF subcategory CHILD OF category
)
ATTRIBUTE product DETERMINES (products.prod_name, products.prod_desc, prod_weight_class, prod_unit_of_measure,
prod_pack_size,prod_status, prod_list_price, prod_min_price) ATTRIBUTE subcategory DETERMINES
(prod_subcategory, prod_subcat_desc) ATTRIBUTE category DETERMINES (prod_category, prod_cat_desc);
Exemplo VM - 2 (1/4)
CREATE MATERIALIZED VIEW items_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT l.slr_id ,
c.cal_date,
sum(l.gms) gms
FROM items l, calendar c
WHERE l.end_date=c.cal_date
GROUP BY l.slr_id, c.cal_date;
Exemplo VM - 2 (2/4)
SQL> select c.qtr_id, sum(l.gms) gms 2 from items l, calendar c
3 where l.end_date=c.cal_date 4 group by l.slr_id, c.qtr_id;
Execution Plan
---
SELECT STATEMENT Optimizer=CHOOSE (Cost=16174 Card=36258 Bytes=1160256)
SORT (GROUP BY) (Cost=16174 Card=36258 Bytes=1160256) HASH JOIN (Cost=81 Card=5611339 Bytes=179562848)
TABLE ACCESS (FULL) OF ’CALENDAR' (Cost=2 Card=8017 Bytes=128272)
TABLE ACCESS (FULL) OF ’ITEMS' (Cost=76 Card=69993 Bytes=1119888)
Exemplo VM - 2 (3/4)
CREATE DIMENSION time_dim
LEVEL CAL_DATE IS calendar.CAL_DATE LEVEL PRD_ID IS calendar.PRD_ID LEVEL QTR_ID IS calendar.QTR_ID LEVEL YEAR_ID IS calendar.YEAR_ID
LEVEL WEEK_IN_YEAR_ID IS calendar.WEEK_IN_YEAR_ID HIERARCHY calendar_rollup
(CAL_DATE CHILD OF PRD_ID CHILD OF
QTR_ID CHILD OF YEAR_ID) HIERARCHY week_rollup
(CAL_DATE CHILD OF
WEEK_IN_YEAR_ID CHILD OF YEAR_ID) ATTRIBUTE PRD_ID DETERMINES PRD_DESC ATTRIBUTE QTR_ID DETERMINES QTR_DESC;
Exemplo VM - 2 (4/4)
SQL> select c.qtr_id, sum(l.gms) gms2 from items l, calendar c 3 where l.end_date=c.cal_date 4 group by l.slr_id, c.qtr_id; Execution Plan
--- SELECT STATEMENT Optimizer=CHOOSE (Cost=3703 Card=878824
Bytes=44820024)
SORT (GROUP BY) (Cost=3703 Card=878824 Bytes=44820024) HASH JOIN (Cost=31 Card=878824 Bytes=44820024)
VIEW (Cost=25 Card=8017 Bytes=128272)
SORT (UNIQUE) (Cost=25 Card=8017 Bytes=128272)
TABLE ACCESS (FULL) OF ‘CALENDAR’ (Cost=2 Card=8017 Bytes=128272)
TABLE ACCESS (FULL) OF ‘ITEMS_MV’ (Cost=3 Card=10962 Bytes=383670)
