In many situations, when you base an MV on a query that joins multiple tables, it is deemed complex and therefore is available only for a complete refresh. However, in some scenarios, you can create a fast refreshable MV when you reference two tables that are joined together in the MV query.

This section describes how to use the EXPLAIN_MVIEW procedure in DBMS_MVIEW to determine whether it is possible to fast refresh a complex query. To help you completely understand the example, this section shows the SQL used to create the base tables. Say you have two base tables, defined as follows:

SQL> create table region( region_id number ,reg_desc varchar2(30),constraint region_pk primary key(region_id)); —

SQL> create table sales( sales_id number ,sales_amt number ,region_id number ,sales_dtt date,constraint sales_pk primary key(sales_id),constraint sales_fk1 foreign key (region_id) references region(region_id));

Additionally, REGION and SALES have MV logs created on them, as shown here:

SQL> create materialized view log on region with primary key; SQL> create materialized view log on sales with primary key;

Also, for this example, the base tables have these data inserted into them:

SQL> insert into region values(10,’East’), (20,’West’),(30,’South’), (40,’North’); —

SQL> insert into sales values (1,100,10,sysdate), (2,200,20,sysdate-20), (3,300,30,sysdate-30);

Suppose you want to create an MV that joins the REGION and SALES base tables as follows:

SQL> create materialized view sales_mv asselect a.sales_id,b.reg_desc from sales      a,region bwhere a.region_id = b.region_id;

Next, let’s attempt to fast refresh the MV:

SQL> exec dbms_mview.refresh(‘SALES_MV’,’F’);

This error is thrown:

ORA-12032: cannot use rowid column from materialized view log…

The error indicates that the MV has issues and cannot be fast refreshed. To determine whether this MV can become fast refreshable, use the output of the EXPLAIN_ MVIEW procedure of the DBMS_MVIEW package. This procedure requires that you first create an MV_CAPABILITIES_TABLE. Oracle provides a script to do this. Run this script as the owner of the MV:

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

After you create the table, run the EXPLAIN_MVIEW procedure to populate it:

SQL> exec dbms_mview.explain_mview(mv=>’SALES_MV’,stmt_id=>’100′);

Now, query MV_CAPABILITIES_TABLE to see what potential issues this MV may have:

SQL> select capability_name, possible, msgtxt, related_text from mv_capabilities_table

where capability_name like ‘REFRESH_FAST_AFTER%’ and statement_id = ‘100’order by 1;

Next is a partial listing of the output. The P (for possible) column contains an N (for no) for every fast refresh possibility:

CAPABILITY_NAME             P    MSGTXT                    RELATED_TEXT

REFRESH_FAST_AFTER_INSERT     N
the SELECT list does not have    Bthe rowids of all the detail tablesREFRESH_FAST_AFTER_INSERT     N

REFRESH_FAST_AFTER_INSERT   N
mv log must have ROWID mv log must have ROWID
MV_MAINT.REGION MV_MAINT.SALES

MSGTXT indicates the issues: The MV logs need to be ROWID based, and the ROWID of the tables must appear in the SELECT clause. So, first drop and re-create the MV logs with ROWID (instead of a primary key):

SQL> drop materialized view log on region; SQL> drop materialized view log on sales; —

SQL> create materialized view log on region with rowid; SQL> create materialized view log on sales with rowid; —

SQL> drop materialized view sales_mv; —

SQL> create materialized view sales_mv asselecta.rowid sales_rowid ,b.rowid region_rowid ,a.sales_id ,b.reg_descfrom sales     a ,region bwhere a.region_id = b.region_id;

Next, reset the MV_CAPABILITIES_TABLE, and repopulate it via the EXPLAIN_MVIEW procedure:

SQL> delete from mv_capabilities_table where statement_id=100; SQL> exec dbms_mview.explain_mview(mv=>’SALES_MV’,stmt_id=>’100′);

The output shows that it is now possible to fast refresh the MV:

CAPABILITY_NAME          P MSGTXT   RELATED_TEXT

REFRESH_FAST_AFTER_ANY_DML           Y REFRESH_FAST_AFTER_INSERT          Y

REFRESH_FAST_AFTER_ONETAB_DML  Y

Execute the following statement to see if the fast refresh works:

SQL> exec dbms_mview.refresh(‘SALES_MV’,’F’); PL/SQL procedure successfully completed.

The EXPLAIN_MVIEW procedure is a powerful tool that allows you to determine whether a refresh capability is possible and, if it is not possible, why it is not and how to potentially resolve the issue.

Oracle Database 23c also supports fast refresh on ANSI join syntax for MVs. Our query in the example had the following:

from sales     a ,region b

where a.region_id = b.region_id

But now this syntax is also valid:

from sales      a join region b

on (a.region_id = b.region_id)