Materialized View by Joining View and Tables

Question

Can we create Materialized view by joining already existing view(normal)?. My requirement is to create a MVIEW on tables(A,B) and View(c). Is it possible? If yes, do we have see any performance issue or refresh issue. This need to done on Oracle DB.

CREATE MATERIALIZED VIEW EMP_MVIEW refresh force ON COMMIT
select 
EMP.ID, EMP.NAME, DV.*
EMP_TABLE EMP
LEFT OUTER JOIN DETAILS_VIEW dv ON DV.EMP_ID=EMP.EMP_ID

Answer 1

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!!