Some notes on materialized view after google here, google there, youtube here and there..
CREATE TABLE tab1 ( col1 NUMBER, col2 VARCHAR2 (20) ); CREATE TABLE tab2 ( col3 NUMBER, col4 VARCHAR2 (20) ); INSERT INTO tab1 VALUES(1,'tab1_row1'); INSERT INTO tab1 VALUES(2,'tab1_row2'); INSERT INTO tab2 VALUES(1,'tab2_row1'); INSERT INTO tab2 VALUES(2,'tab2_row2'); COMMIT;
— When DML changes are made to master table data, oracle database store rows describing those change in materialized view log then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh.
— Usually a fast refresh takes less time than a complete refresh. A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
— to fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.
CREATE MATERIALIZED VIEW LOG ON tab1 with ROWID; CREATE MATERIALIZED VIEW LOG ON tab2 with ROWID; CREATE MATERIALIZED VIEW v_mat AS SELECT t1.col1,t1.col2,t2.col4 FROM tab1 t1, tab2 t2 WHERE t1.col1 = t2.col3; SELECT * FROM v_mat;
— Use the WITH clause to indicate whether the materialized view log should record the primary key, rowid, object ID, or a combination of these row identifiers when rows in the master are changed. You can also use this clause to add a sequence to the materialized view log to provide additional ordering information for its records. This clause also specifies whether the materialized view log records additional columns that might be referenced as filter columns, which are non-primary-key columns that define a join in the subquery WHERE clause.
— ROWID specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.
EXECUTE DBMS_MVIEW.REFRESH ('v_mat','c');
— The first parameter to the procedure REFRESH is the name of the materialized view or snapshot, the second parameter specifies the type of refresh (c – complete refresh).
CREATE MATERIALIZED_VIEW v_mat_fast REFRESH FAST ON COMMIT AS SELECT t1.col1, t1.col2, t2.col4, t1.ROWID AS row_no1, t2.ROWID AS row_no2 FROM tab1 t1, tab2 t2 WHERE t1.col1 = t2.col3;
— specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occured to the master tables. The changes for conventional DML changes are stored in materialized view log associated with the master table. The changes for direct-path INSERT operations are stored in the direct loader log.
— if you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables. Oracle database creates the direct loader log automatically when a direct-path INSERT takes place. No user intervention is needed.
— Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit because the database performs the refresh operation as part of the commit process.
— ROWIDs of all the tables in the FROM list mst appear in the SELECT list of the query. (in above script t1.ROWID AS row_no1, t2.ROWID AS row_no2 )
— Restrictions for basc materialized view fast refresh:
— – the materialized view must not contain references tonon-repeating expressions like SYSDATE and ROWNUM
— – it cannot contain a SELECT list subquery
— – it cannot contain an analytic function
— – it cannot contain nested queries that have ANY, ALL, or NOT EXISTS
— DBMS_MVIEW.EXPLAIN_MVIEW() procedure enables you to learn what is possible with a materialized view or potential materialized view. For example, you can determine if a materialized view is fast refreshable and what types of query rewrite you can perform with a particular materialized view.