Materialized View - Oracle

Materialized view creates a pre-join view that will help much on improving query performance.
With materialized, we have REFRESH FAST, COMPLETE, FORCE, NEVER options and ON COMMIT or ON DEMAND modes.
Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6002.htm#SQLRF01302
http://its-all-about-oracle.blogspot.com/2013/07/refreshing-materialized-view.html

With Refresh Fast

We have to create View log with Row Id to keep changes from master table. After that, these changes will be updated to materialized view.

For example:

create materialized view log on atm_program with rowid, sequence ;

create materialized view log on atm_programtitle with rowid, sequence ;

"sequence" here may be useful when there are many changes occur at the same time. We need assure that it is in sequence.

Then create materialized view (cannot using LEFT JOIN or INNER JOIN, have to use old style):

CREATE MATERIALIZED VIEW MV_PROGRAMTABLE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT t.ticketid,
    ts.statusname,
    p.programid,
    pt.title,
    p.programtype,
    p.originalstartairdate AS originalstartairdate,
    pt.language,
    t.statusid,
    t.createdate,
    t.tickettype,
    p.primaryimageurl,
    p.primarythumbnailurl,
    p.posterstatus,
    p.rowid p_rowid,
    pt.rowid pt_rowid,
    t.rowid t_rowid,
    ts.rowid ts_rowid
  FROM atm_program p, atm_programtitle pt, atm_ticket t, atm_ticketstatus ts

  WHERE p.programid = pt.programid(+)

  AND t.programid(+) = p.programid

  AND ts.statusid(+)              = t.statusid;

(+) mean optional. So p.programid = pt.programid(+) is the same LEFT JOIN. RowId is also need to be included.

Note: Any changes will be put in view log before update to view. It happens whenever INSERT, UPDATE, DELETE. So it will be very slow.

With Refresh on Demand

Data in view must be refresh manually. It can be done by code using Store procedure.
For example:

Create Materialized view: 
---------Create progam table view

CREATE MATERIALIZED VIEW MV_PROGRAMTABLE
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT /*+ PARALLEL(program, 4) PARALLEL(programtitle, 4) */
t.ticketid,
ts.statusname,
p.programid,
pt.title,
p.programtype,
p.originalstartairdate AS originalstartairdate,
pt.LANGUAGE,
t.statusid,
t.createdate,
t.tickettype,
p.primaryimageurl,
p.primarythumbnailurl,
p.posterstatus
FROM program p
LEFT JOIN programtitle pt
ON p.programid = pt.programid
LEFT JOIN ticket t
ON t.programid = p.programid
LEFT JOIN ticketstatus ts
ON ts.statusid = t.statusid;

 

Create Store procedure:

-- Create program refresh store procedure

create or replace
PROCEDURE PROC_REFRESH_MV_PROGRAMTABLE
IS
BEGIN
dbms_mview.refresh('MV_PROGRAMTABLE');
END PROC_REFRESH_MV_PROGRAMTABLE;

In code,

    public void refreshDataForProgramMView() {
       
        ConfigUtil config = ConfigUtil.getInstance();
       
        String programStoreName = config.getValue("storeProcedure.refresh.ProgramMV");
       
        Query query = em.createNativeQuery("{call "+ programStoreName +"()}");

        query.executeUpdate();

    }

In this case, "em" is entityManager of JPA. ConfigUtil points to configuration properties file. It's used to get the Store procedure name.

"refreshDataForProgramMView" function is available to call whenever needed.