Oracle - How to Create a Materialized View with Fast Refresh and Joins

Oracle - How to create a materialized view with FAST REFRESH and JOINS

To start with, from the Oracle Database Data Warehousing Guide:

Restrictions on Fast Refresh on Materialized Views with Joins Only

...

Rowids of all the tables in the FROM list must appear in the SELECT
list of the query.

This means that your statement will need to look something like this:

CREATE MATERIALIZED VIEW MV_Test
  NOLOGGING
  CACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  AS
    SELECT V.*, P.*, V.ROWID as V_ROWID, P.ROWID as P_ROWID 
    FROM TPM_PROJECTVERSION V,
         TPM_PROJECT P 
    WHERE P.PROJECTID = V.PROJECTID

Another key aspect to note is that your materialized view logs must be created as with rowid.

Below is a functional test scenario:

CREATE TABLE foo(foo NUMBER, CONSTRAINT foo_pk PRIMARY KEY(foo));

CREATE MATERIALIZED VIEW LOG ON foo WITH ROWID;

CREATE TABLE bar(foo NUMBER, bar NUMBER, CONSTRAINT bar_pk PRIMARY KEY(foo, bar));

CREATE MATERIALIZED VIEW LOG ON bar WITH ROWID;

CREATE MATERIALIZED VIEW foo_bar
  NOLOGGING
  CACHE
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT  AS SELECT foo.foo, 
                                    bar.bar, 
                                    foo.ROWID AS foo_rowid, 
                                    bar.ROWID AS bar_rowid 
                               FROM foo, bar
                              WHERE foo.foo = bar.foo;

How to create a materialized view with union(or union all) and joins

I don't know why, but - for materialized views in oracle - you have to use old syntax for joins. So put all tables in FROM separating them with commas, and join condition put in where clause (use "(+)" for outer joins).

That works for me:

CREATE MATERIALIZED VIEW MV_DEVDV_TYP_2
REFRESH FAST
ON COMMIT
AS
SELECT DEVDV.ROWID CROWID,
       CONDV.ROWID DROWID,
       DEVDV_ID,
       1 AS MARKER,
       DEVDV_SRC_DVISE_ID,
       DEVDV_CIB_DVISE_ID,
       CONDV_TX, 
       CONDV_DATE_DEB,
       CONDV_DATE_FIN
FROM
    DEVDV, CONDV
WHERE DEVDV_ID = CONDV_DEVDV_ID
UNION ALL
SELECT DEVDV.ROWID CROWID,
       CONDV.ROWID DROW_ID,
       DEVDV_ID,
       2 AS MARKER,
       DEVDV_CIB_DVISE_ID,
       DEVDV_SRC_DVISE_ID,
       1/CONDV_TX, 
       CONDV_DATE_DEB,
       CONDV_DATE_FIN
FROM
    DEVDV, CONDV 
WHERE DEVDV_ID = CONDV_DEVDV_ID;

Materialized view MV_DEVDV_TYP_2 created.

Create an oracle materialized view with fast refresh on aggregated join

It might not be applicable in your situation, but possibly you could denormalize your table.

For example, if you have multiple language dependent names, you could just have named columns for each language.

For example, if your access is index-based, consider varray or nested tables.

Another idea is to use triggers: On insert/update/delete, update another table (or tables), and use that table for the query. Possibly you can pre-calculate aggregates this way as well.

Materialized View by Joining View and Tables

First thing first, Unfortunately ANSI join syntax is not allowed for Oracle Materialized view, use the old Oracle join syntax. It is a bug in Oracle i believe so

Anything I am going to demonstrate with my answer is only regarding Materialized with FAST REFRESH option

Coming back to your original question whether we can use a normal view inside materialized view with incremental refresh capability:

Answer is No

Having said that, if we try will end up with error and unable to create it as I will demonstrate below,

Table Structure: (used only for demo as example and not actual normalized tables

CREATE TABLE emp(emp_id NUMBER primary key
                ,empname VARCHAR2(1000));
CREATE TABLE address_details(address_id NUMBER primary key
                            ,address_text VARCHAR2(1000)
                            ,emp_id NUMBER);
CREATE TABLE salary_details(sal_id NUMBER primary key
                           ,salary NUMBER
                           ,emp_id NUMBER);

Materialized view Logs:

--drop statements
DROP MATERIALIZED VIEW LOG ON emp;
DROP MATERIALIZED VIEW LOG ON address_details;
DROP MATERIALIZED VIEW LOG ON salary_details;

--create statements

--default
CREATE MATERIALIZED VIEW LOG ON emp;
CREATE MATERIALIZED VIEW LOG ON address_details;
CREATE MATERIALIZED VIEW LOG ON salary_details;

--with primary key (same as default above but I would stick to mention it explicitly for understanding and versioning (svn or git) purpose
CREATE MATERIALIZED VIEW LOG ON emp WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON address_details WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON salary_details WITH PRIMARY KEY;

--with primary key and rowid
CREATE MATERIALIZED VIEW log ON emp WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW log ON address_details WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW log ON salary_details WITH PRIMARY KEY, ROWID;

1. First try creating MV with normal view as you want:

CREATE OR REPLACE VIEW DETAILS_VIEW AS
SELECT sal_id
      ,salary
      ,address_id
      ,address_text
      ,sl.emp_id
      --,sl.rowid sl_rowid
      --,ad.rowid ad_rowid
FROM   salary_details sl
      ,address_details ad
WHERE  sl.emp_id = ad.emp_id;

DROP MATERIALIZED VIEW emp_mview;

CREATE MATERIALIZED VIEW EMP_MVIEW 
REFRESH FORCE ON COMMIT 
AS
SELECT emp.emp_id
      ,emp.empname
      ,dv.sal_id
      ,dv.salary
      ,dv.address_id
      ,dv.address_text
      --,emp.rowid emp_rowid
      --,dv.sl_rowid
      --,dv.ad_rowid
FROM   emp           emp
      ,details_view  dv
WHERE  emp.emp_id = dv.emp_id(+);

Result:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Believe me I have tried with all scenarios creating log with primary key and rowid and adding the rowid to the select clause and nothing works

Moving forward:

2. Second try creating MV with standard joins from oracle by putting all individual base tables in the from clause:

I have created the materialized view logs with option primary key as I mentioned in Materialized view Logs section above.

CREATE MATERIALIZED VIEW EMP_MVIEW 
REFRESH FORCE ON COMMIT 
AS
SELECT emp.emp_id
      ,emp.empname
      ,sl.sal_id
      ,sl.salary
      ,ad.address_id
      ,ad.address_text
FROM   emp             emp
      ,salary_details  sl
      ,address_details ad
WHERE  emp.emp_id = sl.emp_id(+)
AND    emp.emp_id = ad.emp_id(+);

BINGO: Materialized view created ,
But, wait wait... Does it mean now I can do a FAST REFRESH ? Lets check:

We can Analyzing Materialized View Capabilities using DBMS_MVIEW.EXPLAIN_MVIEW which will insert capabilities details to table called MV_CAPABILITIES_TABLE (available with @ $ORACLE_HOME/rdbms/admin/utlxmv.sql). If we don't have the script and grants from DBA you need to get it. However I will anyhow give the scripts below as well

HOW TO ANALYSE CAPABILITY OF MV:

    --table structure
    CREATE TABLE mv_capabilities_table (
        statement_id      VARCHAR2(30),
        mvowner           VARCHAR2(30),
        mvname            VARCHAR2(30),
        capability_name   VARCHAR2(30),
        possible          CHAR(1),
        related_text      VARCHAR2(2000),
        related_num       NUMBER,
        msgno             INTEGER,
        msgtxt            VARCHAR2(2000),
        seq               NUMBER
    );
    
    --delete always before analyzing for a view to have only rows for a specific and not to have where clause to filter :)
    DELETE FROM mv_capabilities_table;
    
    --run this script which will analyze and insert into mv_capabilities_table
    BEGIN
       dbms_mview.explain_mview('EMP_MVIEW');
    END;
    /

/***ANALYSIS RESULT:***/

    --I am intersted only with data related to FAST REFRESH category
    SELECT capability_name
         ,possible
         ,substr(msgtxt
                ,1
                ,60) AS msgtxt
    FROM   mv_capabilities_table
    WHERE  capability_name LIKE '%FAST%';
    
    /**
    CAPABILITY_NAME                    POSSIBLE    MSGTXT
    REFRESH_FAST                            N 
    REFRESH_FAST_AFTER_INSERT               N      the SELECT list does not have the rowids of all the detail t
    REFRESH_FAST_AFTER_INSERT               N      mv log must have ROWID
    REFRESH_FAST_AFTER_INSERT               N      mv log must have ROWID
    REFRESH_FAST_AFTER_INSERT               N      mv log must have ROWID
    REFRESH_FAST_AFTER_ONETAB_DML           N      see the reason why REFRESH_FAST_AFTER_INSERT is disabled
    REFRESH_FAST_AFTER_ANY_DML              N      see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
    REFRESH_FAST_PCT                        N      PCT FAST REFRESH is not possible if query contains LEFT OUTE
    **/

If you look at the result with text **mv log must have ROWID**Above result of analysis tells us the materialized view logs are missing the ROWID option and thus FAST REFRESH is not possible.

Note: Other columns in mv_capabilities_table will also tell you the exact tables which i have not included and you can check and test by yourself to see.

Moving forward..

3. Third try creating MV with standard joins from oracle and MV Logs with option ROWID included:

steps: (will not provide the scripts again but just providing the steps to simulate

I will drop and re-create the MV logs with option ROWID as mentioned in Materialized view Logs section above.
Then I will drop and re-create the same MV definition I used in my 2nd try which will be eventually created
Next I will try to repeat the steps to analyze the MV as described in HOW TO ANALYSE CAPABILITY OF MV section

What do i get in my analysis report:

/**
CAPABILITY_NAME                         POSSIBLE  MSGTXT
REFRESH_FAST                            N 
REFRESH_FAST_AFTER_INSERT               N         the SELECT list does not have the rowids of all the detail t
REFRESH_FAST_AFTER_ONETAB_DML           N         see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML              N         see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT                        N         PCT FAST REFRESH is not possible if query contains LEFT OUTE
**/

Ufffff: I am tired actually and again the MV is not having the FAST REFRESH capable yet and the reason tells us "the SELECT list does not have the rowids of all the detail tables"

What it means: the next criteria for FAST REFRESH is Rowids of all the tables in the FROM list must appear in the SELECT list of the query

So,

4. Fourth and last try creating MV with standard joins from oracle and MV Logs with option ROWID included and also the details tables rowids are now included in the select clause:

steps:

As with 3rd try the MV logs required for first refresh are in place I will drop and re-create the MV again but this time with adding the rowids of detail table.

MV Final Script:

CREATE MATERIALIZED VIEW EMP_MVIEW 
REFRESH FORCE ON COMMIT 
AS
SELECT emp.emp_id
      ,emp.empname
      ,sl.sal_id
      ,sl.salary
      ,ad.address_id
      ,ad.address_text
      ,emp.rowid emp_rowid
      ,sl.rowid sl_rowid
      ,ad.rowid ad_rowid
FROM   emp             emp
      ,salary_details  sl
      ,address_details ad
WHERE  emp.emp_id = sl.emp_id(+)
AND    emp.emp_id = ad.emp_id(+);

Now as the MV created , lets analyse the capabilities of the MV as described in HOW TO ANALYSE CAPABILITY OF MV section one more time. (fingers crossed)

Result:

SELECT capability_name
      ,possible
      ,substr(msgtxt,1,60) AS msgtxt
FROM   mv_capabilities_table
WHERE  capability_name LIKE '%FAST%';

/**
CAPABILITY_NAME                         POSSIBLE  MSGTXT
REFRESH_FAST                            Y 
REFRESH_FAST_AFTER_INSERT               Y 
REFRESH_FAST_AFTER_ONETAB_DML           Y 
REFRESH_FAST_AFTER_ANY_DML              Y 
REFRESH_FAST_PCT                        N         PCT FAST REFRESH is not possible if query contains LEFT OUTE
**/

Finally the REFRESH_FAST capability is possible as we can see POSSIBLE -> Y

Sorry for long answer but I thought I should put how I learn the things about in MV in past which could be useful to share.

Some Links I found always useful with respect to Oracle materialized view:

Official site from oracle
MV with Warehouse Oracle Official site
Oracle base
My Favorite from Alberto Dell'Era
One good SO answer about MV

Cheers!!

Oracle - How to Create a Materialized View with Fast Refresh and Joins