Pages

Monday, September 22, 2014

Materialized View by Example - Create Materialized View and Materialized Views Group.

============================
General
============================
This post describes how to create Materialized View, Materialized Views Group, and monitor Materialized View capabilities with DBMS_MVIEW.explain_mview.

============================
Flow
============================
In Master DB create two tables.
In Master DB create Materialized View Log.
In Remote DB create Materialized View on these two tables.

In Remote DB run DBMS_MVIEW.explain_mview().
In Remote DB run Refresh for Materialized View.

In Remote DB create Refresh Group.
In Remote DB run Refresh for Materialized View Group.

============================
In Master DB
============================
Create two tables, ADDRESS and EMPLOYEE.

CREATE TABLE ADDRESS(
  addr_id NUMBER(9) NOT NULL,
  addr_street VARCHAR2(100) NOT NULL,  
  addr_city VARCHAR2(100) NOT NULL,
  addr_country VARCHAR2(100) NOT NULL,
  addr_zip_code VARCHAR2(100)
);

ALTER TABLE ADDRESS ADD CONSTRAINT ADDRESS_PK PRIMARY KEY  (addr_id)


CREATE TABLE EMPLOYEE(
  emp_id NUMBER(9) NOT NULL,
  emp_first_name VARCHAR2(100) NOT NULL,
  emp_last_name VARCHAR2(100) NOT NULL,
  emp_address_id NUMBER(9),
  emp_birth_date DATE
);

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(emp_id);
============================
In Remote DB
============================
Create Materialized View on these two tables.
CREATE MATERIALIZED VIEW ADDRESS_MV REFRESH WITH PRIMARY KEY AS SELECT addr_id, addr_street, addr_city, addr_country, addr_zip_code FROM ADDRESS@ALEC_MASTER_LINK;

CREATE MATERIALIZED VIEW EMPLOYEE_MV REFRESH WITH PRIMARY KEY AS SELECT emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender FROM EMPLOYEE@ALEC_MASTER_LINK;


Try to refresh EMPLOYEE_MV in Fast mode.
This should not work without first creating Materialized View Log.

SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
BEGIN DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F'); END;

*
ERROR at line 1:
ORA-23413: table "ALEC_USER"."EMPLOYEE" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2058
ORA-06512: at line 1


In master DB:

SQL> CREATE MATERIALIZED VIEW LOG ON EMPLOYEE;
Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON ADDRESS;
Materialized view log created.

Refresh
In Remote DB
Start refresh
SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
BEGIN DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "ALEC_USER"."EMPLOYEE" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2058
ORA-06512: at line 1

The solution is to recreate the MATERIALIZED VIEW for EMPLOYEE and ADDRESS.

SQL> DROP MATERIALIZED VIEW EMPLOYEE_MV;

Materialized view dropped.

SQL> CREATE MATERIALIZED VIEW EMPLOYEE_MV REFRESH WITH PRIMARY KEY
AS SELECT emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date FROM EMPLOYEE@ALEC_MASTER_LINK;

Materialized view created.


SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
PL/SQL procedure successfully completed.

SQL> DROP MATERIALIZED VIEW ADDRESS_MV;
Materialized view dropped.

SQL> CREATE MATERIALIZED VIEW ADDRESS_MV REFRESH WITH PRIMARY KEY
AS SELECT addr_id, addr_street, addr_city, addr_country, addr_zip_code FROM ADDRESS@ALEC_MASTER_LINK;

Materialized view created.

Run the Fast refresh
SQL> EXEC DBMS_SNAPSHOT.refresh('ADDRESS_MV','F');

PL/SQL procedure successfully completed.
============================
MV_CAPABILITIES_TABLE
============================
In Remote DB
Work with table MV_CAPABILITIES_TABLE and DBMS_MVIEW.explain_mview stored Procedure that uses this table

To create table MV_CAPABILITIES_TABLE execute
$ORACLE_HOME/rdbms/admin/utlxmv.sql

create table MV_CAPABILITIES_TABLE

(

  statement_id      varchar(30) ,

  mvowner           varchar(30) ,

  mvname            varchar(30) ,

  capability_name   varchar(30) ,

  possible          character(1) ,

  related_text      varchar(2000) ,

  related_num       number ,

  msgno             integer ,

  msgtxt            varchar(2000) ,

  seq               number

) ;

STATEMENT_ID    VARCHAR(30),  -- Client-supplied unique statement identifier
MVOWNER         VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
MVNAME          VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
CAPABILITY_NAME VARCHAR(30),  -- A descriptive name of the particular capability:
POSSIBLE        CHARACTER(1), -- T = capability is possible
                                 F = capability is not possible
RELATED_TEXT    VARCHAR(2000),-- Owner.table.column, alias name, etc.
                                 The specific meaning of this column
                                 depends on the MSGNO column. 
RELATED_NUM     NUMBER,       -- A numeric value associated with a row.
                                 The specific meaning of this column depends on
                                 MSGNO column.
MSGNO           INTEGER,      -- When available, QSM message #
                                 Explaining why not possible or more
                                 details when enabled.
MSGTXT          VARCHAR(2000),-- Text associated with MSGNO.
SEQ             NUMBER);


Once table was created, it is possible to use the Stored Procedure DBMS_MVIEW.explain_mview to analyze Materialized Views.
Need to issue commit, after DBMS_MVIEW.explain_mview()!

EXEC DBMS_MVIEW.explain_mview(mv=>'EMPLOYEE_MV', stmt_id=>'103');
commit;

SELECT STATEMENT_ID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGTXT 
  FROM MV_CAPABILITIES_TABLE 
 WHERE STATEMENT_ID = 103;

STAT MVOWNER    MVNAME       CAPABILITY_NAME                     POSSIBLE RELATED_TEXT    MSGTXT
---- ---------- ------------ ----------------------------------- -------- --------------- ------------------
103  ALEC_USER  EMPLOYEE_MV  PCT                                 N
103  ALEC_USER  EMPLOYEE_MV  REFRESH_COMPLETE                    Y
103  ALEC_USER  EMPLOYEE_MV  REFRESH_FAST                        Y
103  ALEC_USER  EMPLOYEE_MV  REWRITE                             N
103  ALEC_USER  EMPLOYEE_MV  PCT_TABLE                           N        EMPLOYEE        relation is not a
                                                                                          partitioned table

103  ALEC_USER  EMPLOYEE_MV  REFRESH_FAST_AFTER_INSERT           Y
103  ALEC_USER  EMPLOYEE_MV  REFRESH_FAST_AFTER_ONETAB_DML       Y
103  ALEC_USER  EMPLOYEE_MV  REFRESH_FAST_AFTER_ANY_DML          Y

Per the MV_CAPABILITIES_TABLE - both Fast Refresh and Complete Refresh as possible for Materialized View EMPLOYEE_MV

SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','C');
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SNAPSHOT.refresh('EMPLOYEE_MV','F');
PL/SQL procedure successfully completed.

The same configuration is also done for Materialized View ADDRESS_MV.

============================
Example of Materialized view using REFRESH on ROWID
============================
The default of Materialized View refresh is WITH PRIMARY KEY.
It is possible to override this default behavior by explicitly stating WITH ROW ID.

CONTACT Materialized View is based on ROWID

SQL> CREATE TABLE CONTACT(
  2    cnt_id NUMBER(9) NOT NULL,
  3    cnt_name VARCHAR2(100) NOT NULL,
  4    cnt_addr_id NUMBER(9) NOT NULL,
  5    cnt_company VARCHAR2(100),
  6    cnt_dept VARCHAR2(100),
  7    cnt_position VARCHAR2(100)
  8  );
Table created.

SQL> ALTER TABLE CONTACT ADD CONSTRAINT CONTACT_PK PRIMARY KEY  (cnt_id);
Table altered.

CREATE MATERIALIZED VIEW LOG ON CONTACT;

The table MLOG$_XXX is created when CREATE MATERIALIZED VIEW LOG ON XXX is executed.

SELECT table_name FROM USER_TABLES WHERE TABLE_NAME LIKE 'MLOG$%';

TABLE_NAME

------------------------------

MLOG$_ADDRESS

MLOG$_CONTACT

MLOG$_EMPLOYEE

SQL> DROP TABLE CONTACT;

Table dropped.

SQL> SELECT table_name FROM USER_TABLES WHERE TABLE_NAME LIKE 'MLOG$%';

TABLE_NAME
------------------------------
MLOG$_ADDRESS
MLOG$_EMPLOYEE

The MLOG$_CONTACT was deleted automatically where the master table was dropped. 

Now - create again.

SQL> CREATE TABLE CONTACT(
  2    cnt_id NUMBER(9) NOT NULL,
  3    cnt_name VARCHAR2(100) NOT NULL,
  4    cnt_addr_id NUMBER(9) NOT NULL,
  5    cnt_company VARCHAR2(100),
  6    cnt_dept VARCHAR2(100),
  7    cnt_position VARCHAR2(100)
  8  );
Table created.

SQL> ALTER TABLE CONTACT ADD CONSTRAINT CONTACT_PK PRIMARY KEY  (cnt_id);
Table altered.

CREATE MATERIALIZED VIEW LOG ON CONTACT;

In Remote DB
SQL> CREATE MATERIALIZED VIEW CONTACT_MV REFRESH WITH ROWID AS
  2  SELECT cnt_id, cnt_name, cnt_addr_id, cnt_company, cnt_dept, cnt_position
  3  FROM CONTACT@ALEC_MASTER_LINK;
Materialized view created.

SQL> EXEC DBMS_MVIEW.explain_mview(mv=>'CONTACT_MV', stmt_id=>'111');
PL/SQL procedure successfully completed.

commit;

SQL> SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG
  2* FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 111
  
  
  
  SID MVOWNER    MVNAME     CAPABILITY_NAME                POSSIBLE MSG
--- ---------- ---------- ------------------------------ -------- ----------------------------------------
111 ALEC_USER  CONTACT_MV PCT                            N        -
111 ALEC_USER  CONTACT_MV REFRESH_COMPLETE               Y        -
111 ALEC_USER  CONTACT_MV REFRESH_FAST                   N        -
111 ALEC_USER  CONTACT_MV REWRITE                        N        -
111 ALEC_USER  CONTACT_MV PCT_TABLE                      N        CONTACT-relation is not a partitioned
                                                                  table
111 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_INSERT      N        ALEC_USER.CONTACT-mv log must have ROWID
111 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_ONETAB_DML  N        -see the reason why
                                                                   REFRESH_FAST_AFTER_INSERT is disabled
111 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_ANY_DML     N        -see the reason why
                                                                   REFRESH_FAST_AFTER_ONETAB_DML is disabled
111 ALEC_USER  CONTACT_MV REFRESH_FAST_PCT               N        -PCT is not possible on any of the detail
                                                                   tables in the materialized view
111 ALEC_USER  CONTACT_MV REWRITE_FULL_TEXT_MATCH        N        CONTACT-mv references a remote table
                                                                   orview in the FROM list
111 ALEC_USER  CONTACT_MV REWRITE_FULL_TEXT_MATCH        N        -query rewrite is disabled on the
                                                                   materialized view
111 ALEC_USER  CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH     N        -materialized view cannot support any type
                                                                   of query rewrite
111 ALEC_USER  CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH     N        -query rewrite is disabled on the
                                                                   materialized view
111 ALEC_USER  CONTACT_MV REWRITE_GENERAL                N        -materialized view cannot support any type
                                                                   of query rewrite
111 ALEC_USER  CONTACT_MV REWRITE_GENERAL                N        -query rewrite is disabled on the
                                                                   materialized view
111 ALEC_USER  CONTACT_MV REWRITE_PCT                    N        -general rewrite is not possible or PCT is
                                                                   not possible on any of the detail tables
111 ALEC_USER  CONTACT_MV PCT_TABLE_REWRITE              N        CONTACT-relation is not a partitioned
                                                                   table

Without explicitly including the ROWID in the selected fields the Fast refresh is not possible.

DROP MATERIALIZED VIEW CONTACT_MV;
 SQL> CREATE MATERIALIZED VIEW CONTACT_MV REFRESH WITH ROWID AS
  2   SELECT cnt_id, cnt_name, cnt_addr_id, cnt_company, cnt_dept, cnt_position
  3   FROM CONTACT@ALEC_MASTER_LINK;

Materialized view created.

EXEC DBMS_MVIEW.explain_mview(mv=>'CONTACT_MV', stmt_id=>'112');
commit;
SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 112;

SQL> SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG
  2* FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 112
  
  
  
SID MVOWNER    MVNAME     CAPABILITY_NAME                POSSIBLE MSG
--- ---------- ---------- ------------------------------ -------- ----------------------------------------
112 ALEC_USER  CONTACT_MV PCT                            N        -
112 ALEC_USER  CONTACT_MV REFRESH_COMPLETE               Y        -
112 ALEC_USER  CONTACT_MV REFRESH_FAST                   N        -
112 ALEC_USER  CONTACT_MV REWRITE                        N        -
112 ALEC_USER  CONTACT_MV PCT_TABLE                      N        CONTACT-relation is not a partitioned
                                                                  table
112 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_INSERT      N        ALEC_USER.CONTACT-mv log must have ROWID
112 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_ONETAB_DML  N        -see the reason why
                                                                   REFRESH_FAST_AFTER_INSERT is disabled
112 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_ANY_DML     N        -see the reason why
                                                                   REFRESH_FAST_AFTER_ONETAB_DML is disabled
112 ALEC_USER  CONTACT_MV REFRESH_FAST_PCT               N        -PCT is not possible on any of the detail
                                                                   tables in the materialized view
112 ALEC_USER  CONTACT_MV REWRITE_FULL_TEXT_MATCH        N        CONTACT-mv references a remote table
                                                                   orview in the FROM list
112 ALEC_USER  CONTACT_MV REWRITE_FULL_TEXT_MATCH        N        -query rewrite is disabled on the
                                                                   materialized view
112 ALEC_USER  CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH     N        -materialized view cannot support any type
                                                                   of query rewrite
112 ALEC_USER  CONTACT_MV REWRITE_PARTIAL_TEXT_MATCH     N        -query rewrite is disabled on the
                                                                   materialized view
112 ALEC_USER  CONTACT_MV REWRITE_GENERAL                N        -materialized view cannot support any type
                                                                   of query rewrite
112 ALEC_USER  CONTACT_MV REWRITE_GENERAL                N        -query rewrite is disabled on the
                                                                   materialized view
112 ALEC_USER  CONTACT_MV REWRITE_PCT                    N        -general rewrite is not possible or PCT is
                                                                   not possible on any of the detail tables
112 ALEC_USER  CONTACT_MV PCT_TABLE_REWRITE              N        CONTACT-relation is not a partitioned
                                                                   table

Still not good...

Need to fix on the master side - fix the ALEC_USER.CONTACT-mv log - it "must have ROWID" and recreate the Materialized View.
The CREATE MATERIALIZED VIEW on Remote DB should be in sync with CREATE MATERIALIZED VIEW LOG on Master DB. Both should have same WITH clause. In this case, it should be WITH ROWID.

SQL> DROP MATERIALIZED VIEW LOG ON CONTACT;

Materialized view log dropped.

SQL> CREATE MATERIALIZED VIEW LOG ON CONTACT WITH ROWID;

Materialized view log created.

  DROP MATERIALIZED VIEW CONTACT_MV;

 CREATE MATERIALIZED VIEW CONTACT_MV REFRESH WITH ROWID AS
  SELECT cnt_id, cnt_name, cnt_addr_id, cnt_company, cnt_dept, cnt_position
  FROM CONTACT@ALEC_MASTER_LINK;

Materialized view created.

EXEC DBMS_MVIEW.explain_mview(mv=>'CONTACT_MV', stmt_id=>'114');
commit;
SELECT STATEMENT_ID AS SID, MVOWNER, MVNAME, CAPABILITY_NAME, POSSIBLE, RELATED_TEXT||'-'||MSGTXT AS MSG FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID = 114



SID MVOWNER    MVNAME     CAPABILITY_NAME                POSSIBLE MSG
--- ---------- ---------- ------------------------------ -------- ----------------------------------------
114 ALEC_USER  CONTACT_MV PCT                            N        -
114 ALEC_USER  CONTACT_MV REFRESH_COMPLETE               Y        -
114 ALEC_USER  CONTACT_MV REFRESH_FAST                   Y        -
114 ALEC_USER  CONTACT_MV REWRITE                        N        -
114 ALEC_USER  CONTACT_MV PCT_TABLE                      N        CONTACT-relation is not a partitioned table
114 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_INSERT      Y        -
114 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_ONETAB_DML  Y        -
114 ALEC_USER  CONTACT_MV REFRESH_FAST_AFTER_ANY_DML     Y        -

Now CONTACT_MV can be Fast Refreshed.




CREATE MATERIALIZED VIEW LOG options 
When running command "CREATE MATERIALIZED VIEW LOG ON XXX" There are few options:
WITH PRIMARY KEY - This is the default.
                                             Indicates that Primary Key of all changed rows should be 
                                             recorded in the materialized view log.
WITH ROWID        - Indicates that rowid of all changed rows should be 
                                             recorded in the materialized view log.
WITH OBJECT ID   - Only for object table, not for storage tables.
WITH SEQUENCE    - Additional information, to provide ordering information to be
                                            recorded in the materialized view log. 

Combination: The Materialized View Log records changes to the master table or master materialized view based any combination of primary key, ROWID, and object identifier options. 

============================
Difference between WITH PRIMARY KEY and WITH ROWID
============================
Implementation:

WITH PRIMARY KEY - records affected rows using the primary key
WITH ROWID - records affected rows using the physical rowid
Practical Impact:
Using WITH ROWID makes reorganizing and truncating your master tables difficult because it prevents your ROWID materialized views from being fast refreshed. If you reorganize or truncate your master table, then your rowid materialized view must be COMPLETE refreshed because the rowids of the master table have changed.
Use:

The WITH PRIMARY KEY option is the preferred option, unless Primary Key is not available.

============================
Materialized Log info
============================
Materialized Log info is stored in USER_MVIEW_LOGS.

USER_MVIEW_LOGS
SELECT LOG_OWNER, MASTER, LOG_TABLE, ROWIDS, PRIMARY_KEY OBJECT_ID FROM USER_MVIEW_LOGS;

LOG_OWNER       MASTER          LOG_TABLE       ROWIDS OBJECT_ID
--------------- --------------- --------------- ------ ---------
ALEC_USER       ADDRESS         MLOG$_ADDRESS   NO     YES
ALEC_USER       CONTACT         MLOG$_CONTACT   YES    NO
ALEC_USER       EMPLOYEE        MLOG$_EMPLOYEE  NO     YES

MLOG$_<TABLE_NAME>
Lets check the structure of MLOG$_XXX tables.

SQL> DESC MLOG$_ADDRESS
 Name             Type
 ---------------- -----------------
 ADDR_ID          NUMBER(9)
 SNAPTIME$$       DATE
 DMLTYPE$$        VARCHAR2(1 CHAR)
 OLD_NEW$$        VARCHAR2(1 CHAR)
 CHANGE_VECTOR$$  RAW(255)

SQL> DESC MLOG$_EMPLOYEE
 Name             Type
 ---------------- ------------------
 EMP_ID            NUMBER(9)
 SNAPTIME$$       DATE
 DMLTYPE$$        VARCHAR2(1 CHAR)
 OLD_NEW$$        VARCHAR2(1 CHAR)
 CHANGE_VECTOR$$  RAW(255)

 SQL> DESC MLOG$_CONTACT
 Name             Type
 ---------------- ------------------
 M_ROW$$          VARCHAR2(255 CHAR)
 SNAPTIME$$       DATE
 DMLTYPE$$        VARCHAR2(1 CHAR)
 OLD_NEW$$        VARCHAR2(1 CHAR)
 CHANGE_VECTOR$$  RAW(255)

  The MATERIALIZED VIEW LOG ON CONTACT was created with "WITH ROWID" clause, rather than the default "WITH PRIMARY KEY".

The rowid of the new/modified row is stored in materialized view log M_ROW$$ column.


Create Refresh Group.
Refresh Group is useful when there is a need to refresh several Materialized Views in a synchronized way.

Step A. - Create the Materialized Group BEGIN
  DBMS_REFRESH.MAKE (
   name => 'ALEC_USER.MV_GROUP_EMP_INFO', 
   list => '',                  
   next_date => TRUNC(SYSDATE+1),
   interval => 'TRUNC(SYSDATE + 1) +1/24', 
   implicit_destroy => FALSE,    
   rollback_seg => '',           
   push_deferred_rpc => TRUE,   
   refresh_after_errors => FALSE                                  
  );
  commit;
END;
/
Note:
next_date - is DATE
interval - is VARCHAR2

Step B. - Add Materialized Views to the Materialized Group BEGIN
DBMS_REFRESH.ADD (
      name => 'MV_GROUP_EMP_INFO',
      list => 'EMPLOYEE_MV',      
      lax => TRUE); 
      commit;              
END;

/
BEGIN
DBMS_REFRESH.ADD (
      name => 'MV_GROUP_EMP_INFO',
      list => 'ADDRESS_MV',      
      lax => TRUE);               
      commit;
END;

/
Refresh command
BEGIN
  DBMS_REFRESH.REFRESH ('MV_GROUP_EMP_INFO');
END;

/

is equivalent to running:
BEGIN
  DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');
  DBMS_SNAPSHOT.REFRESH('ADDRESS_MV','F');
END;

/


Both EMPLOYEE_MV and ADDRESS_MV where refreshed.

============================
REFRESH GROUP TABLES
============================
The two table to look at are:
USER_REFRESH
USER_REFRESH_CHILDREN

 SQL> DESC USER_REFRESH
 Name                 Type
 -------------------- -----------------
 ROWNER               VARCHAR2(30)
 RNAME                VARCHAR2(30)
 REFGROUP             NUMBER
 IMPLICIT_DESTROY     VARCHAR2(1 CHAR)
 PUSH_DEFERRED_RPC    VARCHAR2(1 CHAR)
 REFRESH_AFTER_ERRORS VARCHAR2(1 CHAR)
 ROLLBACK_SEG         VARCHAR2(30)
 JOB                  NUMBER
 NEXT_DATE            DATE
 INTERVAL             VARCHAR2(200)
 BROKEN               VARCHAR2(1 CHAR)
 PURGE_OPTION         NUMBER(38)
 PARALLELISM          NUMBER(38)

 HEAP_SIZE            NUMBER(38)

 SQL> DESC USER_REFRESH_CHILDREN
 Name                 Type
 -------------------- -----------------
 OWNER                VARCHAR2(30)
 NAME                 VARCHAR2(30)
 TYPE                 VARCHAR2(30)
 ROWNER               VARCHAR2(30)
 RNAME                VARCHAR2(30)
 REFGROUP             NUMBER
 IMPLICIT_DESTROY     VARCHAR2(1 CHAR)
 PUSH_DEFERRED_RPC    VARCHAR2(1 CHAR)
 REFRESH_AFTER_ERRORS VARCHAR2(1 CHAR)
 ROLLBACK_SEG         VARCHAR2(30)
 JOB                  NUMBER
 NEXT_DATE            DATE
 INTERVAL             VARCHAR2(200)
 BROKEN               VARCHAR2(1 CHAR)
 PURGE_OPTION         NUMBER(38)
 PARALLELISM          NUMBER(38)
 HEAP_SIZE            NUMBER(38)

USER_REFRESH
SELECT ROWNER||'.'||RNAME AS REF_GROUP, REFGROUP, NEXT_DATE, INTERVAL FROM USER_REFRESH;

REF_GROUP                        REFGROUP NEXT_DATE INTERVAL
------------------------------ ---------- --------- ------------------------------

ALEC_USER.MV_GROUP_EMP_INFO           101 24-SEP-14 TRUNC(SYSDATE + 1) +1/24


USER_REFRESH_CHILDREN
SELECT OWNER||'.'||NAME AS MVIEW_NAME, TYPE, ROWNER||'.'||RNAME AS REFRESH_GROUP_NAME, REFGROUP AS 
FROM USER_REFRESH_CHILDREN;

MVIEW_NAME                TYPE       REFRESH_GROUP_NAME               REFGROUP
------------------------- ---------- ------------------------------ ----------
ALEC_USER.EMPLOYEE_MV     SNAPSHOT   ALEC_USER.MV_GROUP_EMP_INFO           101
ALEC_USER.ADDRESS_MV      SNAPSHOT   ALEC_USER.MV_GROUP_EMP_INFO           101


Refresh Materialized View, an example
On Master DB:
SQL> SELECT * FROM EMPLOYEE;
no rows selected

SELECT * FROM MLOG$_EMPLOYEE
no rows selected

INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1001,'Hetzel','Hertzel', 1, TO_DATE('18840524','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1002,'Haim Nachman','Bialik', 2, TO_DATE('18891124','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1003,'David','Ben Gurion', 3, TO_DATE('18740121','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1004,'Haim','Arlozorov', 4, TO_DATE('19000112','YYYYMMDD'),'M');
INSERT INTO EMPLOYEE (emp_id, emp_first_name, emp_last_name, emp_address_id, emp_birth_date, gender) VALUES (1005,'Sami','Samsonov', 5, TO_DATE('20140507','YYYYMMDD'),'M');

SQL> SELECT * FROM EMPLOYEE;

EMP_ID EMP_FIRST_NAME       EMP_LAST_NAME        EMP_ADDRESS_ID EMP_BIRTH GENDER
------ -------------------- -------------------- -------------- --------- -------
  1001 Hetzel               Hertzel                           1 24-MAY-84 M
  1002 Haim Nachman         Bialik                            2 24-NOV-89 M
  1003 David                Ben Gurion                        3 21-JAN-74 M
  1004 Haim                 Arlozorov                         4 12-JAN-00 M
  1005 Sami                 Samsonov                          5 07-MAY-14 M


SQL> SELECT * FROM MLOG$_EMPLOYEE

    EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
      1001 01-JAN-00 I       N       FE
      1002 01-JAN-00 I       N       FE
      1003 01-JAN-00 I       N       FE
      1004 01-JAN-00 I       N       FE
      1005 01-JAN-00 I       N       FE

on Remote DB:
BEGIN
  DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');  
END;
/   
On Master DB:
SELECT * FROM MLOG$_EMPLOYEE
no rows selected


When all the depending Materialized Views have refreshed data - this data is deleted from MLOG$_XXX table.



SQL> UPDATE EMPLOYEE SET emp_first_name = 'Nachum' WHERE emp_id = 1005;

1 row updated.

SQL> SELECT * FROM MLOG$_EMPLOYEE;

    EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
      1005 01-JAN-00 U       U       04

SQL> commit;

Commit complete.

The entry is created in table MLOG$_EMPLOYEE before the transaction commits.

SQL> UPDATE EMPLOYEE SET emp_first_name = 'Shlomo' WHERE emp_id = 1005;

1 row updated.

SQL> SELECT * FROM MLOG$_EMPLOYEE;

    EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
      1005 01-JAN-00 U       U       04
      1005 01-JAN-00 U       U       04

SQL> rollback;

Rollback complete.

SQL> SELECT * FROM MLOG$_EMPLOYEE;

    EMP_ID SNAPTIME$ DMLTYPE OLD_NEW CHANGE_VECTOR
---------- --------- ------- ------- -------------
      1005 01-JAN-00 U       U       04

How does Materialized View Refresh coup with TRUNCATE?

On Master DB:   
SQL> TRUNCATE TABLE EMPLOYEE;

Table truncated.

SQL>  SELECT * FROM MLOG$_EMPLOYEE;

no rows selected

SQL> SELECT * FROM EMPLOYEE;

no rows selected
 
On Remote DB:  
SELECT * FROM EMPLOYEE_MV 
 EMP_ID EMP_FIRST_NAME  EMP_LAST_NAME   EMP_ADDRESS_ID EMP_BIRTH GENDER
------- --------------- --------------- -------------- --------- -------
   1001 Hetzel          Hertzel                      1 24-MAY-84 M
   1002 Haim Nachman    Bialik                       2 24-NOV-89 M
   1003 David           Ben Gurion                   3 21-JAN-74 M
   1004 Haim            Arlozorov                    4 12-JAN-00 M
   1005 Nachum          Samsonov                     5 07-MAY-14 M  
   
   
What would happen after Fast refresh?
 
  
BEGIN
  DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','F');  
END;
/
PL/SQL procedure successfully completed.


 EMP_ID EMP_FIRST_NAME  EMP_LAST_NAME   EMP_ADDRESS_ID EMP_BIRTH GENDER
------- --------------- --------------- -------------- --------- -------
   1001 Hetzel          Hertzel                      1 24-MAY-84 M
   1002 Haim Nachman    Bialik                       2 24-NOV-89 M
   1003 David           Ben Gurion                   3 21-JAN-74 M
   1004 Haim            Arlozorov                    4 12-JAN-00 M
   1005 Nachum          Samsonov                     5 07-MAY-14 M
   
Now we are out of sync!!!
The table on Master DB was Truncated, but this change is not logged to MLOG$_EMPLOYEE and is not propagated to the  remote DB.
Need a COMPLETE Refresh to force refresh in the Remote DB.   

BEGIN
  DBMS_SNAPSHOT.REFRESH('EMPLOYEE_MV','C');
END;
/

SQL> SELECT * FROM EMPLOYEE_MV;

no rows selected


Now we are back in sync.

How to track refresh events
Check tables:
USER_MVIEWS
USER_MVIEW_REFRESH_TIMES
USER_MVIEW_ANALYSIS

USER_MVIEWS
SQL> DESC USER_MVIEWS;
 Name                   Type
 ---------------------- --------------------
 OWNER                  VARCHAR2(30)
 MVIEW_NAME             VARCHAR2(30)
 CONTAINER_NAME         VARCHAR2(30)
 QUERY                  LONG
 QUERY_LEN              NUMBER(38)
 UPDATABLE              VARCHAR2(1 CHAR)
 UPDATE_LOG             VARCHAR2(30)
 MASTER_ROLLBACK_SEG    VARCHAR2(30)
 MASTER_LINK            VARCHAR2(128)
 REWRITE_ENABLED        VARCHAR2(1 CHAR)
 REWRITE_CAPABILITY     VARCHAR2(9 CHAR)
 REFRESH_MODE           VARCHAR2(6 CHAR)
 REFRESH_METHOD         VARCHAR2(8 CHAR)
 BUILD_MODE             VARCHAR2(9 CHAR)
 FAST_REFRESHABLE       VARCHAR2(18 CHAR)
 LAST_REFRESH_TYPE      VARCHAR2(8 CHAR)
 LAST_REFRESH_DATE      DATE
 STALENESS              VARCHAR2(19 CHAR)
 AFTER_FAST_REFRESH     VARCHAR2(19 CHAR)
 UNKNOWN_PREBUILT       VARCHAR2(1 CHAR)
 UNKNOWN_PLSQL_FUNC     VARCHAR2(1 CHAR)
 UNKNOWN_EXTERNAL_TABLE VARCHAR2(1 CHAR)
 UNKNOWN_CONSIDER_FRESH VARCHAR2(1 CHAR)
 UNKNOWN_IMPORT         VARCHAR2(1 CHAR)
 UNKNOWN_TRUSTED_FD     VARCHAR2(1 CHAR)
 COMPILE_STATE          VARCHAR2(19 CHAR)
 USE_NO_INDEX           VARCHAR2(1 CHAR)
 STALE_SINCE            DATE

 SELECT OWNER, MVIEW_NAME, CONTAINER_NAME, UPDATABLE, UPDATE_LOG, REFRESH_MODE, REFRESH_METHOD, LAST_REFRESH_TYPE, LAST_REFRESH_DATE AS LAST_DATE
 FROM USER_MVIEWS

OWNER      MVIEW_NAME   CONTAINER_NAME U UPDATE_LOG REFRESH_MODE REFRESH_METHOD LAST_REFRESH_TYPE LAST_DATE
---------- ------------ -------------- - ---------- ------------ -------------- ----------------- ----------
ALEC_USER  CONTACT_MV   CONTACT_MV     N            DEMAND       FORCE          COMPLETE          22-SEP-14
ALEC_USER  EMP_ADDR_MV  EMP_ADDR_MV    N            DEMAND       FORCE          COMPLETE          17-SEP-14
ALEC_USER  EMPLOYEE_MV  EMPLOYEE_MV    N            DEMAND       FORCE          COMPLETE          23-SEP-14
ALEC_USER  ADDRESS_MV   ADDRESS_MV     N            DEMAND       FORCE          FAST              23-SEP-14


USER_MVIEW_REFRESH_TIMES
DESC USER_MVIEW_REFRESH_TIMES


SQL> DESC USER_MVIEW_REFRESH_TIMES

 Name         Type
 ------------ -------------
 OWNER        VARCHAR2(30)
 NAME         VARCHAR2(30)
 MASTER_OWNER VARCHAR2(30)
 MASTER       VARCHAR2(30)
 LAST_REFRESH DATE

 SELECT OWNER||'.'||NAME AS MVIEW_NAME, MASTER_OWNER||'.'||MASTER AS MASTER_TABLE, LAST_REFRESH 
 FROM USER_MVIEW_REFRESH_TIMES
 ORDER BY LAST_REFRESH DESC;

MVIEW_NAME            MASTER_TABLE       LAST_REFRESH
--------------------- ------------------ ------------
ALEC_USER.EMPLOYEE_MV ALEC_USER.EMPLOYEE 23-SEP-14
ALEC_USER.ADDRESS_MV  ALEC_USER.ADDRESS  23-SEP-14
ALEC_USER.CONTACT_MV  ALEC_USER.CONTACT  22-SEP-14
ALEC_USER.EMP_ADDR_MV ALEC_USER.ADDRESS  17-SEP-14
ALEC_USER.EMP_ADDR_MV ALEC_USER.EMPLOYEE 17-SEP-14

USER_MVIEW_ANALYSIS
DESC USER_MVIEW_ANALYSIS

SQL> DESC USER_MVIEW_ANALYSIS

 Name              Type
 ----------------- -----------------
 MVIEW_NAME        VARCHAR2(30)
 MVIEW_TABLE_OWNER VARCHAR2(30)
 CONTAINER_NAME    VARCHAR2(30)
 LAST_REFRESH_SCN  NUMBER
 LAST_REFRESH_DATE DATE
 REFRESH_METHOD    VARCHAR2(8 CHAR)
 SUMMARY           VARCHAR2(1 CHAR)
 FULLREFRESHTIM    NUMBER
 INCREFRESHTIM     NUMBER
 CONTAINS_VIEWS    VARCHAR2(1 CHAR)
 UNUSABLE          VARCHAR2(1 CHAR)
 RESTRICTED_SYNTAX VARCHAR2(1 CHAR)
 INC_REFRESHABLE   VARCHAR2(1 CHAR)
 KNOWN_STALE       VARCHAR2(1 CHAR)
 INVALID           VARCHAR2(1 CHAR)
 REWRITE_ENABLED   VARCHAR2(1 CHAR)
 QUERY_LEN         NUMBER
 QUERY             LONG
 REVISION          NUMBER

 SELECT MVIEW_NAME AS MVIEW_NAME, MVIEW_TABLE_OWNER TABLE_OWNER, CONTAINER_NAME, LAST_REFRESH_DATE AS LAST_REFRESH, REFRESH_METHOD AS METHOD,
    fullrefreshtim AS FULL_REFRESH_TIME,
   increfreshtim AS INCRE_REFRESH_TIME
 FROM USER_MVIEW_ANALYSIS;


MVIEW_NAME            TABLE_OWNER CONTAINER_NAME LAST_REFRESH METHOD   FULL_REFRESH_TIME INCRE_REFRESH_TIME
--------------------- ----------- -------------- ------------ -------- ----------------- -------------------
CONTACT_MV            ALEC_USER   CONTACT_MV     22-SEP-14    FORCE                    0                   0
EMP_ADDR_MV           ALEC_USER   EMP_ADDR_MV    17-SEP-14    FORCE                    0                   0
EMPLOYEE_MV           ALEC_USER   EMPLOYEE_MV    23-SEP-14    FORCE                    0                   0
ADDRESS_MV            ALEC_USER   ADDRESS_MV     23-SEP-14    FORCE                    0                   0

To display the start time and end time, use below calculation:

SELECT 
   MVIEW_NAME AS MVIEW_NAME,
   TO_CHAR(last_refresh_date,'YYYY-MM-DD hh24:mm:ss') "START_TIME",
   TO_CHAR(CASE
      WHEN fullrefreshtim <> 0 THEN
         LAST_REFRESH_DATE + fullrefreshtim/60/60/24
      WHEN increfreshtim <> 0 THEN
         LAST_REFRESH_DATE + increfreshtim/60/60/24
      ELSE
         LAST_REFRESH_DATE
   END,'YYYY-MM-DD hh24:mm:ss') "END_TIME",
   fullrefreshtim AS FULL_REFRESH_TIME,
   increfreshtim AS INCREMENT_REFRESH_TIME
FROM USER_MVIEW_ANALYSIS;

MVIEW_NAME            START_TIME           END_TIME            FULL_REFRESH_TIME INCREMENT_REFRESH_TIME

--------------------- -------------------- ------------------- ----------------- ----------------------
CONTACT_MV            2014-09-22 13:09:55  2014-09-22 13:09:55                 0                      0
EMP_ADDR_MV           2014-09-17 05:09:16  2014-09-17 05:09:16                 0                      0
EMPLOYEE_MV           2014-09-23 09:09:39  2014-09-23 09:09:39                 0                      0
ADDRESS_MV            2014-09-23 08:09:25  2014-09-23 08:09:25                 0                      0

Appendix
Useful Reference Sites
http://www.bluegecko.net/oracle/implementing-count-distinct-with-fast-refresh-materialized-views/
http://www.sqlsnippets.com/en/topic-12884.html

No comments:

Post a Comment