This week, I am teaching an Oracle Database 11g Data Warehouse Administration course in Munich. One of the focus areas of that course are Materialized Views, and I have developed some examples for that course that I like to share with the Oracle community. Other themes of that four days course are Partitioning, ETL and Parallelization.
Materialized Views have the ability to speed up queries (even dramatically) while being transparent for the queries in a similar way as indexes resp. partitioned tables are. That means that we do not have to modify our queries in order to benefit from these structures. Unlike an ordinary view which is only a stored select statement that runs if we use the view, a materialized view stores the result set of the select statement as a container table. Let me demonstrate the benefit of that. I have a (for my little database) relatively large table and do an aggregation query on it:
SQL> desc sales
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)
SQL> select count(*) from sales;
COUNT(*)
----------
7350744
SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES';
MB
----------
286
SQL> show sga
Total System Global Area 373293056 bytes
Fixed Size 1219496 bytes
Variable Size 134218840 bytes
Database Buffers 234881024 bytes
Redo Buffers 2973696 bytes
SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 210770739
4 109654416
3 463002085
9 2219410.08
Elapsed: 00:00:04.51
As you can see, the aggregation takes round 5 seconds. Now I will speed that up using a Materialized View:
SQL> create materialized view mv1 enable query rewrite
2 as select channel_id,sum(amount_sold) from sales group by channel_id;
Materialized view created.
Elapsed: 00:00:05.69
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 210770739
4 109654416
3 463002085
9 2219410.08
Elapsed: 00:00:00.01
The very same statement now takes way less time! Why is that so?
SQL> set autotrace on explain
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 210770739
4 109654416
3 463002085
9 2219410.08
Execution Plan
----------------------------------------------------------
Plan hash value: 2958490228
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV1 | 4 | 104 | 3 (0)| 00:00:01
--------------------------------------------------------------------------------
Because the optimizer transparently rewrote the query on the Materialized View! But what if we now change the content of the base table sales?
SQL> update sales set amount_sold=amount_sold+1 where rownum<2;
1 row updated.
SQL> commit;
Commit complete.
The materialized view is now
stale and will no longer be used for query rewrite (as we can already determine by query runtime):
SQL> select mview_name,staleness from user_mviews;
MVIEW_NAME STALENESS
------------------------------ -------------------
MV1 NEEDS_COMPILE
SQL> set timing on
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 210770739
4 109654416
3 463002086
9 2219410.08
Elapsed: 00:00:04.52
In order to get statements rewritten against the materialized view again, we must refresh it by some method.
One method is
on demand with a procedure call like in this example:
SQL> exec dbms_mview.refresh(list=>'MV1',method=>'C')
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.62
SQL> select channel_id,sum(amount_sold) from sales group by channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 210770739
4 109654416
3 463002086
9 2219410.08
Elapsed: 00:00:00.01
Now the MV1 can get used again, as we see by runtime. That was a
complete refresh – which can take a long time. We would like to get this done faster.
One comfortable method to get a
fast refreshable materialized view is the usage of the package DBMS_ADVISOR:
SQL> vari t varchar2(50)
SQL> begin
2 dbms_advisor.tune_mview(task_name=>:t,
3 mv_create_stmt=>'create materialized view mv1'
4 || ' refresh fast as'
5 || ' select channel_id,sum(amount_sold)'
6 || ' from sales group by channel_id');
7 end;
8/
PL/SQL procedure successfully completed.
SQL> set long 5000
SQL> select statement from user_tune_mview where task_name=:t order by action_id;
STATEMENT
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "ADAM"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID
","AMOUNT_SOLD") INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "ADAM"."SALES" ADD ROWID, SEQUENCE ("CHANNE
L_ID","AMOUNT_SOLD") INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW ADAM.MV1 REFRESH FAST WITH ROWID DISABLE QUERY REWRIT
E AS SELECT ADAM.SALES.CHANNEL_ID C1, SUM("ADAM"."SALES"."AMOUNT_SOLD") M1, COUN
T("ADAM"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM ADAM.SALES GROUP BY ADAM.SA
LES.CHANNEL_ID
STATEMENT
--------------------------------------------------------------------------------
DROP MATERIALIZED VIEW ADAM.MV1
SQL> exec dbms_advisor.delete_task(:t)
PL/SQL procedure successfully completed.
Usually, we need
Materialized View Logs on the base tables and special expressions in the materialized view query to be able to do a fast refresh.
I simply take the coding from above now:
SQL> CREATE MATERIALIZED VIEW LOG ON "ADAM"."SALES"
WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES; 2 3
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW ADAM.MV1
REFRESH FAST WITH ROWID
enable QUERY REWRITE AS
SELECT ADAM.SALES.CHANNEL_ID C1,
SUM("ADAM"."SALES"."AMOUNT_SOLD") M1,
COUNT("ADAM"."SALES"."AMOUNT_SOLD") M2,
COUNT(*) M3
FROM ADAM.SALES
GROUP BY ADAM.SALES.CHANNEL_ID;
Materialized view created.
After again modifying the base table sales, I will then try a
fast refresh of the MV1:
SQL> set timing off
SQL> update sales set amount_sold=amount_sold*1 where rownum<2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select mview_name,staleness from user_mviews;
MVIEW_NAME STALENESS
------------------------------ -------------------
MV1 NEEDS_COMPILE
SQL> set timing on
SQL> exec dbms_mview.refresh('MV1','F')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
It worked! But do I always have to refresh manually? No, we can also schedule a regular refresh like this:
SQL> alter materialized view mv1 refresh start with sysdate next sysdate + interval '1' minute;
Materialized view altered.
SQL> alter session set nls_date_format='hh24:mi:ss';
Session altered.
SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;
MVIEW_NAME LAST_REF LAST_REF
------------------------------ -------- --------
MV1 FAST 10:40:05
SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;
MVIEW_NAME LAST_REF LAST_REF
------------------------------ -------- --------
MV1 FAST 10:41:04
Of course, a one minute refresh interval is extraordinary short. An other possibility to refresh the MVs very comfortably is
on commit:
CREATE MATERIALIZED VIEW ADAM.MV1
REFRESH FAST on commit
WITH ROWID
enable QUERY REWRITE AS
SELECT ADAM.SALES.CHANNEL_ID C1,
SUM("ADAM"."SALES"."AMOUNT_SOLD") M1,
COUNT("ADAM"."SALES"."AMOUNT_SOLD") M2,
COUNT(*) M3
FROM ADAM.SALES
GROUP BY ADAM.SALES.CHANNEL_ID;
Materialized view created.
SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;
MVIEW_NAME LAST_REF LAST_REF
------------------------------ -------- --------
MV1 COMPLETE 11:16:28
SQL> update sales set amount_sold=amount_sold*1 where rownum<2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;
MVIEW_NAME LAST_REF LAST_REF
------------------------------ -------- --------
MV1 FAST 11:19:56
