15. PL/SQL Views & Materialized Views | PL/SQL Developer Course | TechView Team | Hindi
#techviewteam #plsql
In this video we discuss about PL/SQL Views & Materialized Views.
This is 15th video from series of videos about PL/SQL. so, you are requested to watch complete playlist for better understanding.
---video notes start from here-----
CREATE MATERIALIZED VIEW LOG ON TECHVIEW.CUSTOMERS
WITH PRIMARY KEY, ROWID(NAME, ADDRESS, WEBSITE, CREDIT_LIMIT, EMAIL_ID)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TECHVIEW.ORDERS
WITH PRIMARY KEY, ROWID(CUSTOMER_ID, STATUS, SALESMAN_ID, ORDER_DATE)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TECHVIEW.ORDER_ITEMS
CACHE
WITH PRIMARY KEY, ROWID(PRODUCT_ID, QUANTITY, UNIT_PRICE)
INCLUDING NEW VALUES;
---materalised view-------
CREATE MATERIALIZED VIEW MVIEW1
build IMMEDIATE
refresh fast
on demand
disable query rewrite
as
select o.customer_id,c.name,c.address,count(o.order_id)as total_order,
sum(oi.unit_price*oi.quantity) as total_sale
from techview.order_items oi
inner join techview.orders o on o.order_id=oi.order_id
inner join techview.customers c on c.customer_id=o.customer_id
where o.status='Shipped'
group by o.customer_id,c.name,c.address;
------
SELECT * FROM MV_SALE WHERE CUSTOMER_ID=1;
EXEC DBMS_SNAPSHOT.REFRESH('MV_SALE','COMPLETE');
----
Regards
TechView Team