Представления (10 мин.)

Смотреть код

create view film_amount as 
select
f.film_id,
sum(p.amount) as amount
from

film f
left join inventory i 
on
i.film_id = f.film_id
left join rental r 
on
r.inventory_id = i.inventory_id
left join payment p 
on
p.rental_id = r.rental_id
group by
f.film_id;

explain
select
*
from
film_amount;

explain
select
f.film_id,
sum(p.amount) as amount
from
film f
left join inventory i 
on
i.film_id = f.film_id
left join rental r 
on
r.inventory_id = i.inventory_id
left join payment p 
on
p.rental_id = r.rental_id
group by
f.film_id;

create materialized view film_amount_mat as 
select
f.film_id,
sum(p.amount) as amount
from
film f
left join inventory i 
on
i.film_id = f.film_id
left join rental r 
on
r.inventory_id = i.inventory_id
left join payment p 
on
p.rental_id = r.rental_id
group by
f.film_id;

explain
select
*
from
film_amount_mat;

explain
select
f1.*
from
film_amount f1
join film_amount f2
on
f1.film_id = f2.film_id;

explain
select
f1.*
from
film_amount_mat f1
join film_amount_mat f2
on
f1.film_id = f2.film_id;

refresh materialized view film_amount_mat;