{マテビュー}高速リフレッシュ、クエリリライト可能なマテリアライズドビューを作成

演習

 

https://docs.oracle.com/cd/F19136_01/dwhsg/basic-materialized-views.html#GUID-63E162FE-8957-4C1D-9494-041A8B2B7917

 

git clone https://github.com/oracle-samples/db-sample-schemas.git
cd db-sample-schemas

sql
@./customer_orders/co_install.sql
@./human_resources/hr_install.sql
@./sales_history/sh_install.sql

conn sh/sh

           集計を含む
           
create table products1 as select * from products;
create table sales1 as select * from sales;

CREATE MATERIALIZED VIEW LOG ON products1 WITH SEQUENCE, ROWID
(prod_id, prod_name)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON sales1
WITH SEQUENCE, ROWID
(prod_id, amount_sold)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW product_sales_mv
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales,
COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt
FROM sales1 s, products1 p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;

 

           結合のみを含む
           
create table sales2 as select * from sales;
create table times2 as select * from times;
create table customers2 as select * from customers;

CREATE MATERIALIZED VIEW LOG ON sales2 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times2 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers2 WITH ROWID;

CREATE MATERIALIZED VIEW detail_sales_mv 
BUILD IMMEDIATE
REFRESH FAST
enable query rewrite
AS SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
       c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales2 s, times2 t, customers2 c 
WHERE  s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);


           ネステッド
           
create table sales3 as select * from sales;
create table customers3 as select * from customers;
create table times3 as select * from times;


CREATE MATERIALIZED VIEW LOG ON sales3 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers3 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times3 WITH ROWID;

CREATE MATERIALIZED VIEW join_sales_cust_time 
BUILD IMMEDIATE
REFRESH FAST
enable query rewrite
AS SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
       t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid 
FROM sales3 s, customers3 c, times3 t
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;

CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time WITH SEQUENCE, ROWID
(cust_last_name, day_number_in_week, amount_sold)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sum_sales_cust_time 
BUILD IMMEDIATE
REFRESH FAST
enable query rewrite
AS SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold) cnt_sales,
          cust_last_name, day_number_in_week
FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;