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.

Useful SQL Hints to improve query performance

References:
http://ashokkumartj.blogspot.com/2011/01/useful-sql-hints-to-improve-query.html

Create Index and using Index hints

CREATE INDEX:

There are many types of index introduced by Oracle.
http://docs.oracle.com/cd/B19306_01/server.102/b14231/indexes.htm

Each type have the different way to create. Ex:

CREATE INDEX programId_index ON PROGRAM(programId) COMPUTE STATISTICS;

Put Index on columns that appear in WHERE statement.
Good for single column - one index.
Can put multi-columns in index but make sure that the all these columns appear in WHERE statement. If not, index does not help.

Ex: CREATE INDEX User_index ON PROGRAM(firstName, lastName, title) COMPUTE STATISTICS;

Great if: SELECT * from User WHERE firstName = ? AND lastName = ?

Bad if: SELECT * from User WHERE lastName = ?
       or SELECT * from User WHERE lastName = ? AND title = ?

Index will make DML (insert, update, delete) slow down (3-time slower than the table with no indexes). Should not index on column that value is too much duplicated.

INDEX HINTS:

Use Hints indicate Oracle using index:

SELECT /*+ index(table_name(column_has_indexed)) */  * FROM customer;

Oracle Semi-Joins and Anti-Joins

Using EXISTS and IN.

Example 1:

SELECT   D.deptno, D.dname
        FROM     dept D, emp E
        WHERE    E.deptno = D.deptno
        ORDER BY D.deptno;

Change to:

SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno; 

Example 2:

SELECT   D1.deptno, D1.dname
        FROM     dept D1
        MINUS
        SELECT   D2.deptno, D2.dname
        FROM     dept D2, emp E2
        WHERE    D2.deptno = E2.deptno
        ORDER BY 1;

Change to: 

SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    NOT EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno;

References:

http://www.dbspecialists.com/files/presentations/semijoins.html