Materialized Views By Anthony B DEFINITION OF MV

Materialized Views By Anthony B.

DEFINITION OF MV A materialized view, or snapshot as they were previously known, is a table segment (database object) whose contents are periodically refreshed based on a query, either against a local or remote table in which case we need a DB link. Queries to large databases often involve joins between tables, aggregations such as SUM , or both. These operations are expensive in terms of time and processing power. You can use materialized views in data warehouses to increase the speed of queries on very large databases.

MV FUNCTIONALITY

IMPORTANCE OF MV Materialized views can perform a number of roles, such as improving query performance or providing replicated data. The end user queries the tables and views at the detail data level. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism reduces response time for returning results from the query. Materialized views within the data warehouse are transparent to the end user or to the database application.

Cont. Although materialized views are usually accessed through the query rewrite mechanism, an end user or database application can construct queries that directly access the summary table or MV log. However, serious consideration should be given to whether users should be allowed to do this because any change to the summaries will affect the queries that reference them.

WHAT IS A MV VIEW LOG? When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and 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 Database must re-execute 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.

Cont. Since a complete refresh involves truncating the materialized view segment and re-populating it using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table – performance issue. To reduce the replication costs, materialized view logs can be created to capture all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view.

Cont. 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.

EXPLNATION OF PAARMETERS ØThe BUILD clause options are shown below. IMMEDIATE - The materialized view is populated immediately. DEFERRED - The materialized view is populated on the first requested refresh. ØThe following refresh types are available. FAST - A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails. COMPLETE - The table segment supporting the materialized view is truncated and repopulated completely using the associated query. Time consuming as it deletes data and repopulates FORCE - A fast refresh is attempted. If one is not possible a complete refresh is performed.

Cont. ØA refresh can be triggered in one of two ways. ON COMMIT - The refresh is triggered by a committed data change in one of the dependent tables. ON DEMAND - The refresh is initiated by a manual request or a scheduled task.

Cont. ØThe QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations if performance is poor. ØThe ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.

DEMONSTRATION OF MV CREATION I USED THE BELOW SCHEMA Ø HR SCHEMA Ø SCOTT SCHEMA MV is created based on master table which is located remotely or locally. Create MV on HR SCHEMA AND MASTER TABLE IS IN SCOTT Conn to SCOTT schema SELECT * FROM SCOTT. DEPT;

STEP 1 Create mv in HR schema which has master table (dept) in scott's schema create materialized view mv_dept BUILD IMMEDIATE REFRESH FAST ON DEMAND DISABLE QUERY REWRITE AS SELECT * FROM SCOTT. DEPT;

NOTE When doing a fast refresh the master or parent table should have a MV log table as Fast refresh focuses on data that is updated since our last refresh. The log table stores all updated or changes executed on the master table and provides it upon refresh.

STEP 2 Øcreate MV log on scott. dept table create MATERIALIZED VIEW LOG ON DEPT; ØYou can expand Scott's objects to view the MV log table MV vs VIEWS MV will hold actual data and needs to be refreshed a simple view is a named or stored SQL statement which holds no data. A view is a virtual table When you drop the MV, mv_dept WILL BE DROPPED Insert new rows on the scott. dept table, but you won't see the update on the MV until after refresh of MV The new data is temporarily stored on the MV log table

STEP 3 ØThere are 2 ways to refresh a MV Create a MV VIEW REFRESH GROUP and add the MV to the group, use the time option to refresh MV periodically as need be (every 5, 15, 20 mins) or Run stored procedure and package below in case you gave a single or few MV EXEC DBMS_MVIEW. REFRSH('MV_DEPT');

STEP 4 ØQuery MV to see its been updated SELECT * FROM MV-DEPT; You can schedule this as a job to run and refresh MV ØHow to drop MV - Once mv is dropped, MV TABLE (MV_DEPT) is dropped too. DROP mv <MV_NAME>; Configuration complete

Advantages ØMaterialized Views are useful for remote replication and performance tuning. Ølocal copy of the data in the view sql structure, Can be indexed and partitioned (tuned) Queries don't impact source tables (great for OLTP).

Disadvantages ØTakes space - Can only be based on a simple Select if you require real-time data. maintaining the MV Logs has an overhead on the master system. ØIn the real world, if you refresh periodically rather than use the real-time (FAST), there is little advantage over just creating a table and populating it with a script or PLSQL procedure. ØIf you do create an Mview, then you will need to monitor the refreshes using the DBA system views as they can fail to refresh/update and you won't realize until someone complains.

QUESTIONS?