The two most common configurations used are as follows:

•     Creating complete refresh MVs that are refreshed on demand

•     Creating fast refresh MVs that are refreshed on demand

It is important to understand these basic configurations. They lay the foundation for everything else you do with the MV feature.

The SALES table will be used as the basis for the MV examples.

SQL> create table sales( sales_id number primary key , sales_amt number, region_id number , sales_dtt date);

SQL> insert into sales values (1,101,10,sysdate-10), (2,511,20,sysdate-20),(3,11,30,sysdate-30); commit;

Keep in mind the performance of these queries, which are being executed thousands/millions of times a day and consuming a large amount of database resources. These examples will not be able to simulate that but will show how to create and maintain these views.

For creating an MV, you need both the CREATE MATERIALIZED VIEW and CREATE TABLE system privileges. If a user creating MVs doesn’t own the base table, then SELECT access on the base table is also required to perform an ON COMMIT REFRESH.

Suppose you wanted to create an MV that reports on daily sales. Use the CREATE MATERIALIZED VIEW…AS SELECT statement to do this. The following statement names the MV, specifies its attributes, and defines the SQL query on which the MV is based:

Note using an alias with GROUP BY is a new 23c feature.

Let’s look at the USER_MVIEWS data dictionary to verify that the MV was created as expected. Run this query:

SQL> select mview_name, refresh_method, refresh_mode , build_mode, fast_refreshable from user_mviews where mview_name = ‘SALES_DAILY_MV’;

MVIEW_NAME   REFRESH_ REFRES BUILD_MOD FAST_REFRESHABLE

————— ——– —— ——— —————–SALES_DAILY_MV COMPLETE DEMAND IMMEDIATE DIRLOAD_LIMITEDDML

If new data is inserted, this MV will be refreshed on demand only. To initiate a fast refresh of the MV, use the REFRESH procedure of the DBMS_MVIEW package.

This example passes two parameters to the REFRESH procedure: the name and the refresh method. C is for complete, and F is for fast, and to run a fast refresh, the MV needs to have a MV log.

Now, you attempt to initiate a fast refresh of the MV, using the REFRESH procedure of the DBMS_MVIEW package. This example passes two parameters to the REFRESH procedure: the name and the refresh method. The name is SALES_DAILY_MV, and the parameter is F (for fast):

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

Because this MV was not created in conjunction with an MV log, a fast refresh is not possible. The following error is thrown:

ORA-23413: table “MV_MAINT”.”SALES” does not have a materialized view log

Instead, a complete refresh is initiated. The parameter passed in is C (for complete):

SQL> exec dbms_mview.refresh(‘SALES_DAILY_MV’,’C’);

The output indicates success:

PL/SQL procedure successfully completed.

To make this MV fast refreshable, a log needs to be created. Here is the criteria for a fast refreshable MV:

1. A base table recommended with primary (rowid can be used if no primary key)

2. Create an MV log on the base table.

3. Create an MV as fast refreshable.

SQL> create materialized view log on sales with primary key; SQL> create materialized view sales_rep_mv segment creation immediate refresh with primary key fast on demand as select sales_id ,sales_amt,trunc(sales_dtt) sales_dtt from sales;

First, when an MV log is created, a corresponding table is also created that stores the rows in the base table that changed and how they changed (insert, update, or delete). The MV log table name follows the format MLOG$_<base table name>.

A table is also created with the format RUPD$_<base table name>. Oracle automatically creates this RUPD$ table when you create a fast refreshable MV, using a primary key.

The table is there to support the updatable MV feature. You do not have to worry about this table unless you are dealing with updatable MVs (see the Oracle Advanced Replication Guide for more details on updatable MVs). If you’re not using the updatable MV feature, then you can ignore the RUPD$ table.

Oracle creates an index with the format <base table name>_PK1. This index is automatically created for primary key-based MVs and is based on the primary key column(s) of the base table. If this is a ROWID instead of a primary key, then the index name has the format I_SNAP$_<table_name> and is based on the ROWID. If you do not explicitly name the primary key index on the base table, then Oracle gives the MV table primary key index a system-generated name, such as SYS_C008780.

Now that you understand the underlying architectural components, let’s look at the data in the MV:

SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_rep_mvorder by 2;Here is some sample output:

SALES_AMT TO_CHAR(SALES_DTT,’D ———- ——————–511 10-jan-2023 101 20-jan-2023 127 30-jan-2023

Let’s add two records to the base SALES table:

SQL> insert into sales values (6, 99, 20, sysdate-6), (7, 127, 30, sysdate-7);

SQL> commit;

At this point, it is instructional to inspect the M$LOG table. You should see two records that identify how the data in the SALES table have changed:

SQL> select count(1) from mlog$_sales;

There are two records:

Next, let’s refresh the MV. This MV is fast refreshable, so you call the REFRESH procedure of the DBMS_MVIEW package with the F (for fast) parameter:

SQL> exec dbms_mview.refresh(‘SALES_REP_MV’,’F’);A quick inspection of the MV shows two new records:

SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_rep_mvorder by 2; Here is some sample output:

SALES_AMT TO_CHAR(SALES_DTT,’D ———- ——————–511 10-jan-2023101 20-jan-2023 127 23-jan-2023 99 24-jan-2023 127 30-jan-2023

Additionally, the count of the MLOG$ has dropped to zero. After the MV refresh is complete, those records are no longer required:

SQL> select count(1) from mlog$_sales;

Here is the output:

COUNT(1)

———-0

You can verify the last method whereby an MV was refreshed by querying the USER_ MVIEWS view:

SQL> select mview_name, last_refresh_type, last_refresh_date from user_mviews order by 1,3; Here is some sample output:

MVIEW_NAME             LAST_REF    LAST_REFR

SALES_DAILY_MV COMPLETE 30-JAN-23

SALES_REP_MV        FAST    30-JAN-23