All about Materialized Views

Overview

Materialized views are a data warehousing/decision support system tool that can increase by many orders of magnitude the speed of queries that access a large number of records. They accomplish this by transparently using pre-computed summarizations and joins of data. These pre-computed summaries would typically be very small compared to the original source data. When utilizing materialized views, some decisions need to be made up front. These decisions depend on the frequency of updates, how stale the data can get between updates, or whether the view is to be updated at all. You can decide to populate the materialized view on creation or upon the first access of the materialized view.
Materialized views work best in a read-only, or read-intensive environment. They are not designed for use in a high-end OLTP environment. They will add overhead to modifications performed on the base tables in order to capture the changes.
There are four options to consider here: complete, fast, force, and never.
- The “complete” option rebuilds the materialized view data in its entirely.
- “Fast” requires a materialized view log (covered later in this section) that tracks the changes to the underlying tables.
- “Force” also requires a materialized view log, as this option will try to do a fast refresh; otherwise, it will do a complete refresh.
- The “never” option doesn’t do any rebuilds or refreshes at all.
You also need to decide whether to perform the refresh when data is committed to the data source (using the “refresh on commit” option); otherwise, you’d get the “refresh on demand” option where you’d then have to periodically run the DBMS_MVIEW.REFRESH procedure.

Setup of Materialized Views

In order to use materialized views, the Oracle DBA must set special initialization parameters and grant special authority to the users of materialized views. You start by setting these initialization parameters within Oracle to enable the mechanisms for materialized views and query rewrite
optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced

QUERY REWRITE INTEGRITY
This parameter controls how Oracle rewrites queries and may be set to one of three values:
ENFORCED - Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. There are mechanisms by which we can tell Oracle about other inferred relationships, and this would allow for more queries to be rewritten, but since Oracle does not enforce those relationships, it would not make use of these facts at this level.
TRUSTED - Queries will be rewritten using the constraints that are enforced by Oracle, as well as any relationships existing in the data that we have told Oracle about, but are not enforced by the database.
STALE TOLERATED - Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is ' stale ' (out-of-sync with the details). This might be useful in an environment where the summary tables are refreshed on a recurring basis, not on commit, and a slightly out-of-sync answer is acceptable.

The needed privileges are as follows:

Example

This example will demonstrate what a materialized view entails. The concept is that of reducing the execution time of a long running query transparently, by summarizing data in the database. A query against a large table will be transparently rewritten into a query against a very small table, without any loss of accuracy in the answer. For the example we create our own big table based on the system view ALL_OBJECTS.

Prepare the large table BIGTAB:

sqlplus scott/tiger
set echo on
set termout off

drop table bigtab;

create table bigtab
  nologging
  as
  select * from all_objects
  union all
  select * from all_objects
  union all
  select * from all_objects;

insert /*+ APPEND */ into bigtab select * from bigtab;
commit;
insert /*+ APPEND */ into bigtab select * from bigtab;
commit;
insert /*+ APPEND */ into bigtab select * from bigtab;
commit;

analyze table bigtab compute statistics;
select count(*) from bigtab;

   COUNT(*)
----------
    708456

Run query against this BIGTABLE

Initially this query will require a full scan of the large table.

set autotrace on
set timing on
select owner, count(*) from bigtab group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
CTXSYS                               6264
ELAN                                 1272
HR                                    816
MDSYS                                5640
ODM                                  9768
ODM_MTR                               288
OE                                   2064
OLAPSYS                             10632
ORDPLUGINS                            696
ORDSYS                              23232
OUTLN                                 168
PM                                    216
PUBLIC                             278184
QS                                    984
QS_ADM                                168
QS_CBADM                              576
QS_CS                                 552
QS_ES                                 936
QS_OS                                 936
QS_WS                                 936
SCOTT                                 264
SH                                   4176
SYS                                324048
SYSTEM                              15096
TEST                                 4536
WKSYS                                6696
WMSYS                                3072
XDB                                  6240

28 rows selected.
Elapsed: 00:00:07.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
          (Cost=2719 Card=28 Bytes=140)
   1    0   SORT (GROUP BY) (Cost=2719 Card=28 Bytes=140)
   2    1     TABLE ACCESS (FULL) OF 'BIGTAB'
              (Cost=1226 Card=708456 Bytes=3542280)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19815  consistent gets
      18443  physical reads
          0  redo size
        973  bytes sent via SQL*Net to client
        510  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         28  rows processed

In order to get the aggregate count, we must count 700,000+ records on over 19800 blocks. If you need this summary often per day, you can avoid counting the details each and every time by creating a materialized view of this summary data.

Create the Materialized View

sqlplus scott/tiger

grant query rewrite to scott;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

create materialized view mv_bigtab
  build immediate
  refresh on commit
  enable query rewrite
as
select owner, count(*)
  from bigtab
 group by owner;

analyze table mv_bigtab compute statistics;

Basically, what we've done is pre-calculate the object count, and define this summary information as a materialized view. We have asked that the view be immediately built and populated with data. You'll notice that we have also specified REFRESH ON COMMIT and ENABLE QUERY REWRITE. Also notice that we may have created a materialized view, but when we ANALYZE, we are analyzing a table. A materialized view creates a real table, and this table may be indexed, analyzed, partitioned, you can define in which tablespace to store it, and so on.

Now let's see the materialized view in action by issuing the same query again

set timing on
set autotrace traceonly
select owner, count(*) from bigtab group by owner;
set autotrace off
set timing off

28 rows selected.
Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
          (Cost=2 Card=28 Bytes=252)
   1    0   TABLE ACCESS (FULL) OF 'MV_BIGTAB'
            (Cost=2 Card=28 Bytes=252)

Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        973  bytes sent via SQL*Net to client
        510  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         28  rows processed

No physical I/O this time around as the data was found in the cache. Our buffer cache will be much more efficient now as it has less to cache. We could not even begin to cache the previous query's working set, but now I can. Notice how our query plan shows we are now doing a full scan of the MV_BIGTAB table, even though we queried the detail table BIGTAB. When the SELECT OWNER, ... query is issued, the database automatically directs it to the materialized view.

Now, add a new row to the BIGTAB table and commit the change

insert into bigtab
  (owner, object_name, object_type, object_id)
  values ('Martin', 'Zahn', 'Akadia', 1111111);

commit;

set timing on
set autotrace traceonly
select owner, count(*)
  from bigtab
 where owner = 'Martin'
 group by owner;
set autotrace off
set timing off

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
          (Cost=2 Card=1 Bytes=9)
   1    0   TABLE ACCESS (FULL) OF 'MV_BIGTAB'
            (Cost=2 Card=1 Bytes=9)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        439  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The analysis shows that we scanned the materialized view MV_BIGTAB and found the new row. By specifying REFRESH ON COMMIT in our original definition of the view, we requested that Oracle maintain synchronization between the view and the details, the summary will be maintained as well.

Index selection for Materialized Views
Depending on the number of rows in the materialized view it may be necessary to create indexes on the materialized views. Therefore, consideration should be given to first creating a unique, local index which contains all of the materialized view keys. Other indexes could include single-column bitmap indexes on each materialized view key column. The index required for fast refresh is automatically created when the materialized view is first defined. When creating indexes, don’t forget to consider the storage space requirements of each index and the impact those indexes will have on the refresh time. If you are unsure which indexes to create then consider running the SQL Access Advisor to see what it recommends.

Uses of Materialized Views

This is relatively straightforward and is answered in a single word - performance. By calculating the answers to the really hard questions up front (and once only), we will greatly reduce the load on the machine, We will experience:

There are concurrency issues with regards to rising the REFRESH ON COMMIT option. Consider the summary example from before. Any rows that are inserted or deleted from this table will have to update one of 28 rows in the summary table in order to maintain the count in real time. This does not preclude the use of materialized views in an OLTP environment. For example if you use full refreshes on a recurring basis (during off-peak time) there will be no overhead added to the modifications, and there would be no concurrency issues. This would allow you to report on yesterday's activities, for example, and not query the live OLTP data for reports.


Refreshing materialized views
Starting in Oracle9i, if you specify REFRESH FAST for a single-table aggregate materialized view, you must have created a materialized view log for the underlying table, or the refresh command will fail. When creating a materialized view, you have the option of specifying whether the refresh occurs manually (ON DEMAND) or automatically (ON COMMIT, DBMS_JOB). To use the fast warehouse refresh facility, you must specify the ON DEMAND mode. To refresh the materialized view, call one of the procedures in DBMS_MVIEW.
The DBMS_MVIEW package provides three types of refresh operations:
    * DBMS_MVIEW.REFRESH—Refreshes one or more materialized views
    * DBMS_MVIEW.REFRESH_ALL_MVIEWS—Refreshes all materialized views
    * DBMS_MVIEW.REFRESH_DEPENDENT—Refreshes all table-based materialized views

Manual complete refresh

A complete refresh occurs when the materialized view is initially defined, unless it references a prebuilt table, and a complete refresh may be requested at any time during the life of the materialized view. Because the refresh involves reading the detail table to compute the results for the materialized view, this can be a very time-consuming process, especially if huge amounts of data need to be read and processed.


Manual fast (incremental) refresh
If you specify REFRESH FAST (which means that only deltas performed by UPDATE, INSERT, DELETE on the base tables will be refreshed), Oracle performs further verification of the query definition to ensure that fast refresh can always be performed if any of the detail tables change. These additional checks include the following:
    * A materialized view log must be present for each detail table.
    * The RowIDs of all the detail tables must appear in the SELECT list of the MVIEW query definition.
    * If there are outer joins, unique constraints must be placed on the join columns of the inner table.
You can use the DBMS_MVIEW package to manually invoke either a fast refresh or a complete refresh, where F equals Fast Refresh and C equals Complete Refresh:
    EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F');


Automatic fast refresh of materialized views
The automatic fast refresh feature is completely new in Oracle9i, so you can refresh a snapshot with DBMS_JOB in a short interval according to the snapshot log. With Oracle 9i, it's possible to refresh automatically on the next COMMIT performed at the master table. This ON COMMIT refreshing can be used with materialized views on single-table aggregates and materialized views containing joins only. ON COMMIT MVIEW logs must be built as ROWID logs, not as primary-key logs. For performance reasons, it's best to create indexes on the ROWIDs of the MVIEW. Note that the underlying table for the MVIEW can be prebuilt. Example of a materialized view with an ON COMMIT refresh:

CREATE MATERIALIZED VIEW empdep
ON PREBUILT TABLE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
  AS SELECT empno, ename, dname, loc,
            e.rowid emp_rowid,
            d.rowid dep_rowid
    FROM emp e, dept d
   WHERE e.deptno = d.deptno;


One comfortable method to get a fast refreshable materialized view is the usage of the package DBMS_ADVISOR:
vari t varchar2(50)
begin
dbms_advisor.tune_mview(task_name=>:t,
                         mv_create_stmt=>'create materialized view mv1'
                             || ' refresh fast as'
                             || ' select channel_id,sum(amount_sold)'
                             || ' from sales group by channel_id');
end;
/ 

set long 5000
select statement from user_tune_mview where task_name=:t order by action_id;

STATEMENT
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "SCOTT"."BIGTAB" WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON "SCOTT"."BIGTAB" ADD ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW SCOTT.MV1 REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SCOTT.BIGTAB.CHANNEL_ID C1,
SUM("SCOTT"."BIGTAB"."AMOUNT_SOLD") M1, COUNT("SCOTT"."BIGTAB"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SCOTT.BIGTAB GROUP BY SCOTT.BIGTAB.CHANNEL_ID

STATEMENT
--------------------------------------------------------------------------------
DROP MATERIALIZED VIEW ADAM.MV1

SQL> exec dbms_advisor.delete_task(:t)

Monitoring materialized views
Oracle provides information in the data dictionary to monitor the behavior of materialized views. When you’re monitoring materialized views, it’s critical that you check the refresh interval in the dba_jobs view. Here is a SQL statement to check the generated job status for materialized views:
Monitoring materialized views

Oracle provides information in the data dictionary to monitor the behavior of materialized views. When you’re monitoring materialized views, it’s critical that you check the refresh interval in the dba_jobs view. Here is a SQL statement to check the generated job status for materialized views:
SELECT
  SUBSTR(job,1,4) "Job", SUBSTR(log_user,1,5)          "User",
  SUBSTR(schema_user,1,5)                              "Schema",
  SUBSTR(TO_CHAR(last_date,'DD.MM.YYYY HH24:MI'),1,16) "Last Date",
  SUBSTR(TO_CHAR(next_date,'DD.MM.YYYY HH24:MI'),1,16) "Next Date",
  SUBSTR(broken,1,2) "B", SUBSTR(failures,1,6)        "Failed",
  SUBSTR(what,1,20)                                    "Command"
FROM dba_jobs;

Job  User  Schem Last Date         Next Date       B Fail Command
---- ----- ----- ---------------- ---------------- - ---- -------------
90   SCOTT SCOTT 28.01.2000 11:33 28.01.2000 13:33 N 0    dbms_refresh.refresh


What can this Materialized View Do?
Prior to creating a materialized view or once it is created, the DBA may wonder what is possible with this materialized view, such as it is fast refreshable and if not, why not. The procedure DBMS_MVIEW.EXPLAIN_MVIEW can provide this information. As an example, consider the following Materialized View:
CREATE MATERIALIZED VIEW costs_mv
PCTFREE 0 STORAGE (initial 8k next 8k pctincrease 0)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS SELECT time_id, prod_name, SUM( unit_cost) AS sum_units, COUNT(unit_cost) AS count_units, COUNT(*) AS cnt
          FROM costs c, products p
          WHERE c.prod_id = p.prod_id
          GROUP BY time_id, prod_name;

If we remove the COUNT(*) from the definition and then call the procedure DBMS_MVIEW.EXPLAIN_MVIEW, it will tell us that Partition Change Tracking (PCT) is available because the costs table is partitioned and all types of query rewrite are possible However, Fast Refresh is not possible after DML because COUNT(*) is missing from the materialized view.

TRUNCATE TABLE mv_capabilities_table;

EXEC DBMS_MVIEW.EXPLAIN_MVIEW (‘ SELECT time_id, prod_name, SUM( unit_cost) AS sum_units, COUNT(unit_cost) AS count_units, FROM costs c, products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_name’);
SELECT capability_name, possible, related_text, msgtxt FROM mv_capabilities_table;

PCT_TABLE: Y COSTS:
PCT_TABLE: N PRODUCTS: relation is not a partitioned table
REFRESH_COMPLETE: Y
REFRESH_FAST: Y
REFRESH_FAST_AFTER_ANY_DML: N see the reason why
REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_INSERT: Y
REFRESH_FAST_AFTER_ONETAB_DML: N COUNT(*) is not present in the select list
REFRESH_FAST_PCT: Y
REWRITE: Y
REWRITE_FULL_TEXT_MATCH: Y
REWRITE_GENERAL: Y
REWRITE_PARTIAL_TEXT_MATCH: Y
REWRITE_PCT: Y

Tuning a Materialized View
The EXPLAIN_MVIEW procedure advises what is possible with a materialized view, but it doesn’t show you how to fully optimize the materialized view to its full potential. This is when the procedure TUNE_MVIEW should be used. Given the definition of a materialized view, it will show you how to create your materialized view so that it is fast refreshable and can take advantage of as many types of query rewrite as possible.
Referring once again to the materialized view that we previously created. We know from EXPLAIN_MVIEW that fast refresh is not possible if the COUNT(*) is not present. If this materialized view is given as input to TUNE_MVIEW, as illustrated in next example, it will generate a new materialized view definition.
In order to use TUNE_MVIEW a directory path must be specified using the CREATE DIRECTORY command, which specifies where the results will be stored. The full CREATE MATERIALIZED VIEW statement is given as input to TUNE_MVIEW and the results are stored in a unique task.

CREATE DIRECTORY TUNE_RESULTS AS '/tuning/';
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
VARIABLE task_mv VARCHAR2(30);
VARIABLE create_mv_ddl VARCHAR2(4000);
EXECUTE :task_mv := 'cust_mv';
EXECUTE :create_mv_ddl := ' -
CREATE MATERIALIZED VIEW cust_mv –
REFRESH FAST ENABLE QUERY REWRITE AS -
SELECT time_id, prod_name, SUM( unit_cost) AS sum_units, COUNT(unit_cost) AS count_units -
FROM costs c, products p GROUP BY time_id, prod_name'; WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_name’);
EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_mv, :create_mv_ddl);

The recommendations from TUNE_MVIEW are stored in an advisor task. They can easily be retrieved, by calling the procedure GET_TASK_SCRIPT, and placing them in a file, using the procedure CREATE_FILE, as illustrated below.
EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT(:task_mv),'TUNE_RESULTS', 'mv_create.sql');

The next example shows the complete output from TUNE_MVIEW, which includes the new materialized view statement and the required materialized view logs.
CREATE MATERIALIZED VIEW LOG ON "SH"."COSTS" WITH ROWID, SEQUENCE("TIME_ID","UNIT_COST") INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COSTS" ADD ROWID, SEQUENCE("TIME_ID","UNIT_COST") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS" WITH ROWID, SEQUENCE("PROD_NAME")INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS" ADD ROWID, SEQUENCE("PROD_NAME") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SH.PRODUCTS.PROD_NAME C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST")M1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.COSTS GROUP BY SH.PRODUCTS.PROD_NAME, SH.COSTS.TIME_ID;


LOADING & REFRESHING THE MATERIALIZED VIEW
Historically, one of the problems of using summary tables has been the initial loading and subsequent updating of the summary. These issues are now addressed because summary management provides mechanisms to:
• fully refresh the data
• perform a fast refresh, that is add/merge only the changes
• automatically update a materialized view when ever changes are made
Therefore the DBA must consider how much time is required to create and maintain each materialized view and balance that against the performance gains achieved by using this materialized view.

Oracle Database 10g provides the following refresh methods:
• complete
• fast (only the changes are applied)
• force, do a fast if possible, otherwise perform a complete refresh
• partition change tracking (fast refresh rows in changed partitions only)
These operations may be performed:
• on demand refreshing by:
    • specific materialized views (DBMS_MVIEW.REFRESH)
    • those materialized views dependent on a table (DBMS_MVIEW.REFRESH_DEPENDENT)
    • all materialized views (DBMS_MVIEW.REFRESH_ALL_MVIEWS)
• on commit, whenever the tables on which the materialized view is defined are changed
The ON DEMAND refresh is achieved by calling one of the procedures listed above, thus giving the DBA total control over when a materialized view is update.
If the ON COMMIT refresh method is chosen, whenever a materialized view is affected by changes made to the source data, the materialized view will automatically be updated to reflect this data. However, it should be remembered that this update to the materialized view occurs as part of the commit processing in the transaction where the changes to the base table are made. Therefore, the commit will take slightly longer, because changes are being made to both the original table and then any materialized view whose definition includes that table.

Conclusion

Summary table management, another term for the materialized view, has actually been around for some time in tools such as Oracle Discoverer. If you ran a query in SQL*PLUS, or from your Java JDBC client, then the query rewrite would not (could not) take place. Furthermore, the synchronization between the details (original source data) and the summaries could not be performed or validated for you automatically, since the tool ran outside the database.
Furthermore, since version 7.0, the Oracle database itself has actually implemented a feature with many of the characteristics of summary tables - the Snapshot. This feature was initially designed to support replication, but many would use it to ' pre-answer ' large queries. So, we would have snapshots that did not use a database link to replicate data from database to database, but rather just summarized or pre-joined frequently accessed data. This was good, but without any query rewrite capability, it was still problematic. The application had to know to use the summary tables in the first place, and this made the application more complex to code and maintain. If we added a new summary then we would have to find the code that could make use of it, and rewrite that code.




Another FULL Example

Extracted from http://uhesse.com/2009/07/08/brief-introduction-into-materialized-views/
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

But this may (although done in asynchronous fashion) slow down the DML on the base table, so it requires testing whether the slow down is noticeable or neglectable. So far  for now about Materialized Views. There is of course much more to say, but I think that this is enough for a brief introduction of that theme.