Create a Fast Refreshable Materialized View on a Complex Query

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View

This is an example of a Fast Refreshable Materialized View on more than one table. Fast Refreshable Materialized Views are not possible on complex query's. This usually means more than one table or using functions in the SQL clause. It is possible to form a Fast Refreshable Materialized View on 2 tables albeit generally not very useful, but in trying to do so, I can demonstrate how to determine if it is at all possible with examples of using the MV_CAPBILITIES Table.

Here I will form a 2 table join and use it in a Fast Refreshable Materialized View. The first step is to create te Materialized View Log on both base tables.
 
SQL> create materialized view log on experiment_types with primary key;
SQL> create materialized view log on experiment_runs with primary key;

Create the Materialized View as FAST refresh, with PRIMARY KEY, and ON COMMIT options.

 create materialized view EXPERIMENTS_MV
 refresh
 with primary key
 fast
 on commit
 as
 select USERNAME,EXP_NAME
 from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
 where A.EXPID = B.EXPID
 order by USERNAME,EXP_NAME;
     *
ERROR at line 8:
ORA-12052: cannot fast refresh materialized view EXPERIMENTS_MV

We get an error saying it cannot be Fast Refresh. Well okay then. Instead let's create the Materialized View with no options using only the defaults. This will let us test the SQL portion of the Materialized View.

create materialized view EXPERIMENTS_MV
as
select USERNAME,EXP_NAME
from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
where A.EXPID = B.EXPID
order by USERNAME,EXP_NAME;

This works. Now let's query USER_MVIEWS to see what kind of Materialized View was created
Force means try FAST first and if this is not possible then do a COMPLETE refresh.

SQL> select mview_name,refresh_method,refresh_mode
from user_mviews
where mview_name = 'EXPERIMENTS_MV';

MVIEW_NAME         REFRESH_METHOD     REFRESH_MODE
--------------     -------------       -----------------
EXPERIMENTS_MV     FORCE                 DEMAND    

We see that our Materialized View was created as FORCE and DEMAND. Let's try and refresh it as FAST using the DBMS_MVIEW.REFRESH procedure. We pass in F for Fast as the refresh method.

SQL> exec dbms_mview.refresh('EXPERIMENTS_MV','F');
ORA-12032: cannot use rowid column from materialized view log on
"EXPERIMENT_RUNS"

Crikey! We get an error.
This error is also telling us that the Materialized View Log needs to be defined using ROWID instead of Primary Key. Before we do that let's see if it refreshes at all by using the default refresh method.

Refresh again without the REFRESH_METHOD parameter.

SQL> exec dbms_mview.refresh('EXPERIMENTS_MV');
PL/SQL procedure successfully completed.

That works. Now let's look for some help using the Materialized View Capibilities Table.

Create the mv_capabilities_table in your schema.

SQL> @?/rdbms/admin/utlxmv.sql
Table created.

Now execute dbms_mview.explain_mview to populate the mv_capabilities_table

SQL> exec dbms_mview.explain_mview(mv=>'EXPERIMENTS_MV',stmt_id=>'100');
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

Select from mv_capabilities_table:
select capability_name
,possible
,msgtxt
from mv_capabilities_table
where capability_name like 'REFRESH_FAST_AFTER%'
and statement_id = '100'
order by 1;

CAPABILITY_NAME            POSSIBLE    MSGTXT                RELATED_TEXT
-------------------------    --------    ---------------------        ----------------
REFRESH_FAST_AFTER_INSERT    N        mv log must have ROWID    EXPERIMENT_RUNS
REFRESH_FAST_AFTER_INSERT    N        mv log must have ROWID    EXPERIMENT_TYPES

The MSGTXT says that our Materialized View logs need to be ROWID Based.
Drop and recreate the Materialized View and Materialized View logs and recreate the Materialized View logs with ROWID.

SQL> drop materialized view EXPERIMENTS_MV;
SQL> drop materialized view log on EXPERIMENT_TYPES;
SQL> drop materialized view log on EXPERIMENT_RUNS;

Recreate the Materialized View Logs with rowed this time.
SQL> create materialized view log on experiment_types with rowid;
SQL> create materialized view log on experiment_runs with rowid;
Recreate Materialized View
create materialized view EXPERIMENTS_MV
as
select USERNAME,EXP_NAME
from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
where A.EXPID = B.EXPID
order by USERNAME,EXP_NAME;

Delete existing records from mv_capabilities_table.
SQL> delete from mv_capabilities_table;
SQL> commit;

SQL> exec dbms_mview.explain_mview(mv=>'EXPERIMENTS_MV',stmt_id=>'100');
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

Select from mv_capabilities_table:
CAPABILITY_NAME            POSSIBLE    MSGTXT       
-------------------------    --------    --------------------------------       
REFRESH_FAST_AFTER_INSERT    N       
the SELECT list does not have the rowids of all the detail tables

The MSGTXT now says that our Materialized View select list does not contain rowids.
Remember that the select clause in a Fast Refresh Materialized View must contain either PK or ROWID.

Again we drop and recreate the Materialized View and Materialized View logs. We recreate the Materialized View logs with ROWID and we include the ROWID in the select clause.

SQL> drop materialized view EXPERIMENTS_MV;
SQL> drop materialized view log on EXPERIMENT_TYPES;
SQL> drop materialized view log on EXPERIMENT_RUNS;
SQL> create materialized view log on experiment_types with rowid;
SQL> create materialized view log on experiment_runs with rowid;

create materialized view EXPERIMENTS_MV
as
select A.ROWID AS AROWID
, B.ROWID AS BROWID
,USERNAME
,EXP_NAME AS EXPERIMENT_NAME
from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
where A.EXPID = B.EXPID
order by USERNAME,EXP_NAME;

SQL> delete from mv_capabilities_table;
SQL> commit;

SQL> exec dbms_mview.explain_mview(mv=>'EXPERIMENTS_MV',stmt_id=>'100');
PL/SQL procedure successfully completed.

SQL> commit;

Finally we select from mv_capabilities_table one more time:

CAPABILITY_NAME                POSSIBLE    MSGTXT       
------------------------------    -----        --------------------------------       
REFRESH_FAST_AFTER_ANY_DML        Y   
REFRESH_FAST_AFTER_INSERT        Y   
REFRESH_FAST_AFTER_ONETAB_DML    Y   

Now we can refresh the Materialized View:
SQL> exec dbms_mview.refresh('EXPERIMENTS_MV','F');
PL/SQL procedure successfully completed.

Finally drop and recreate both the Materialized View and the Materialized View Logs. This time though you can create your Materialized View using the ROWID, FAST, and ON COMMIT options.

SQL> create materialized view EXPERIMENTS_MV
 refresh
 with rowid
 fast
 on commit
 as
 select A.ROWID AS EXP_TYPES_ROWID
 , B.ROWID AS EXP_RUNS_ROWID
 ,USERNAME,EXP_NAME
 from EXPERIMENT_TYPES A, EXPERIMENT_RUNS B
 where A.EXPID = B.EXPID
 order by USERNAME,EXP_NAME;

Materialized view created.

More on Materialized Views

Materialized Views

What is a Materialized View?

Creating a Complete Refreshable Materialized View

Creating a Fast Refreshable Materialized View