8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Home » Articles » Misc » Here
A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.
Using materialized views against remote tables is the simplest way to achieve replication of data between sites. The example code in this article assumes DB1 is the master instance and DB2 is the materialized view site.
- Basic Syntax
- Check Privileges
- Create Materialized View
- Create Materialized View Logs
- Refresh Materialized Views
- Cleaning Up
- Aggregations and Transformations
- Real-Time Materialized Views
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
The full syntax description for the CREATE MATERIALIZED VIEW command is available in the documentation. Here we will only concern ourselves with the basics.
-- NormalCREATE MATERIALIZED VIEW view-nameBUILD [IMMEDIATE | DEFERRED]REFRESH [FAST | COMPLETE | FORCE ]ON [COMMIT | DEMAND ][[ENABLE | DISABLE] QUERY REWRITE]ASSELECT ...;-- Pre-BuiltCREATE MATERIALIZED VIEW view-nameON PREBUILT TABLEREFRESH [FAST | COMPLETE | FORCE ]ON [COMMIT | DEMAND ][[ENABLE | DISABLE] QUERY REWRITE]ASSELECT ...;
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.
- FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.
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.
QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. An example of the query rewrite functionality is shown below.
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.
Check the user who will own the materialized views has the correct privileges. At minimum they will require the
CREATE MATERIALIZED VIEW privilege. If they are creating materialized views using database links, you may want to grant them
CREATE DATABASE LINK privilege also.
CONNECT sys@db2GRANT CREATE MATERIALIZED VIEW TO scott;GRANT CREATE DATABASE LINK TO scott;
Create Materialized View
Connect to the materialized view owner and create the database link and the materialized view itself.
CONNECT scott/tiger@db2CREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'DB1.WORLD';CREATE MATERIALIZED VIEW emp_mvBUILD IMMEDIATE REFRESH FORCEON DEMANDASSELECT * FROM email@example.com;
Alternatively, we could have used a prebuilt table, as shown below.
-- Create the table first. This could be populated-- using an export/import.CREATE TABLE emp_mv ASSELECT * FROM firstname.lastname@example.org;-- Build the materialized view using the existing table segment.CREATE MATERIALIZED VIEW emp_mvREFRESH FORCEON DEMANDON PREBUILT TABLEASSELECT * FROM email@example.com;
Remember to gather stats after building the materialized view.
BEGIN DBMS_STATS.gather_table_stats( ownname => 'SCOTT', tabname => 'EMP_MV');END;/
Create Materialized View Logs
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. 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.
To take advantage of the of the fast refresh, connect to the master instance and create the materialized view log.
CONNECT scott/tiger@db1CREATE MATERIALIZED VIEW LOG ON scott.empTABLESPACE usersWITH PRIMARY KEYINCLUDING NEW VALUES;
Refresh Materialized Views
If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.
For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.
The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it.
BEGIN DBMS_REFRESH.make( name => 'SCOTT.MINUTE_REFRESH', list => '', next_date => SYSDATE, interval => '/*1:Mins*/ SYSDATE + 1/(60*24)', implicit_destroy => FALSE, lax => FALSE, job => 0, rollback_seg => NULL, push_deferred_rpc => TRUE, refresh_after_errors => TRUE, purge_option => NULL, parallelism => NULL, heap_size => NULL);END;/BEGIN DBMS_REFRESH.add( name => 'SCOTT.MINUTE_REFRESH', list => 'SCOTT.EMP_MV', lax => TRUE);END;/
Information about refresh groups and the materialize views in a refresh group can be queried from the
DBA_RCHILD views respectively.
A materialized view can be manually refreshed using the
Rather than using a refresh group, you can schedule
DBMS_MVIEW.REFRESH called using the Oracle Scheduler
To clean up we must remove all objects.
CONNECT scott/tiger@db2DROP MATERIALIZED VIEW emp_mv;DROP DATABASE LINK DB1.WORLD;BEGIN DBMS_REFRESH.destroy(name => 'SCOTT.MINUTE_REFRESH');END;/CONNECT scott/tiger@db1DROP MATERIALIZED VIEW LOG ON scott.emp;
Aggregations and Transformations
Materialized views can be used to improve the performance of a variety of queries, including those performing aggregations and transformations of the data. This allows the work to be done once and used repeatedly by multiple sessions, reducing the total load on the server.
The following query does an aggregation of the data in the
CONN scott/tigerSET AUTOTRACE TRACE EXPLAINSELECT deptno, SUM(sal)FROM empGROUP BY deptno;---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 || 1 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------
Create a materialized view to perform the aggregation in advance, making sure you specify the
ENABLE QUERY REWRITE clause.
CREATE MATERIALIZED VIEW emp_aggr_mvBUILD IMMEDIATE REFRESH FORCEON DEMANDENABLE QUERY REWRITE ASSELECT deptno, SUM(sal) AS sal_by_deptFROM empGROUP BY deptno;EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');
The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.
--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; --ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;SET AUTOTRACE TRACE EXPLAINSELECT deptno, SUM(sal)FROM empGROUP BY deptno;--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 21 | 3 (0)| 00:00:01 || 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV | 3 | 21 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------------------------
Before using materialized views and materialized view logs, consider the following:
- Populating a materialized view adds load to both servers involved. The source server is queried to capture the data, which is inserted into the destination server. Be sure the additional load does not adversely affect your primary system.
- Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DML on the base table. Check the additional work does not adversely affect performance on the primary system.
- If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits.
- Depending on the Oracle version and the complexity of the associated query, fast refreshes may not be possible.
- When using materialized views to improve performance of transformations and aggregations, the
QUERY_REWRITE_ENABLEDparameters must be set or the server will not be able to automatically take advantages of query rewrites. These parameters may be set in the pfile or spfile file if they are needed permanently. Later releases have them enabled by default.
For more information see:
- CREATE MATERIALIZED VIEW
- CREATE MATERIALIZED VIEW LOG
- Real-Time Materialized Views
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...
Back to the Top.
What is the use of materialized view in Oracle? ›
Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.What is difference between view and materialized view? ›
Views are used when data is to be accessed infrequently and the data in a table gets updated on a frequent basis. In contrast, Materialized Views are used when data is to be accessed frequently and data in table not get updated on frequent basis.What is the purpose of materialized view? ›
A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is pre-computed, querying a materialized view is faster than executing a query against the base table of the view.What is the difference between Oracle view and materialized view? ›
Unlike an ordinary Oracle Database View which does not contain any data, a materialized view is an Oracle Database object that contains the results of a query. Materialized views can be stored in the same database as their base tables or views, or in a separate database.What is the advantage of materialized view? ›
Faster response times – A materialized view responds faster in comparison to a view. This is because the materialized view is precomputed and therefore doesn't waste time resolving the query or joins in the query that create the materialized view.Do materialized views need to be refreshed? ›
Unlike indexes, materialized views are not automatically updated with every data change. They must explicitly be refreshed, either on every commit, on a periodically time schedule or – typically in data warehouses – at the end of an ETL job.Can I query from a materialized view? ›
You can query your materialized views directly, the same way you query a regular table or standard view. Queries against materialized views are always consistent with queries against the view's base tables, even if those tables have changed since the last time the materialized view was refreshed.Can materialized view be updated? ›
To update the data in a materialized view, you can use the REFRESH MATERIALIZED VIEW statement at any time. When you use this statement, Amazon Redshift identifies changes that have taken place in the base table or tables, and then applies those changes to the materialized view.What are the disadvantages and advantage of using materialized view? ›
Advantages: 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 realtime data. maintaining the MV Logs has an overhead on the master system.When should I use materialized view? ›
- Pre-aggregate data. Aggregation of streaming data.
- Pre-filter data. Run queries that only read a particular subset of the table.
- Pre-join data. Query joins, especially between large and small tables.
- Recluster data.
What is an example of a materialized view? ›
For example, let's say you have a database with two tables: one contains the number of employees in your business, and the other contains the number of departments in your business. Using a materialized view, you could query the database to retrieve all the employees who are associated with a particular department.Can we delete data from materialized view? ›
You cannot delete rows from a read-only materialized view. If you delete rows from a writable materialized view, then the database removes the rows from the underlying container table. However, the deletions are overwritten at the next refresh operation.What happens to materialized view if table is dropped? ›
Usage Notes. Dropping a materialized view does not update references to that view. For example, if you create a view named “V1” on top of a materialized view, and then you drop the materialized view, the definition of view “V1” will become out of date.Can we perform DML on materialized view? ›
Users cannot perform data manipulation language (DML) statements on read-only materialized views, but they can perform DML on updatable and writeable materialized views.Where are materialized views most often used? ›
Materialized views find use especially in data warehousing scenarios, where frequent queries of the actual base tables can be expensive. In a materialized view, indexes can be built on any column.What is materialized view in ETL? ›
Materialized views are physical structures that improve data access time by pre- computing intermediary results. The ETL process bridging the online transaction processing (OLTP) system and the online analytical processing (OLAP) system is often modeled as a separate and independent process.What is the difference between MV and table? ›
A materialized view is built on top of existing tables, whilst a table is the original data storage object that is used. Another major difference is that a materialized view is self updating whenever the underlying tables change, whilst to update a table a job has to be run to append or replace the data.Can we create materialized view on multiple tables? ›
Like SELECT statements, materialized views can join on several tables.Can we insert data in materialized view? ›
You can't insert data into a materialized view as you can with a table. To update the contents of a materialized view, you can execute a query to refresh it. This will re-execute the query used to create it.Can we truncate materialized view? ›
As such, we do not generally recommend truncating a materialized view. Although each query on the view will still show up-to-date results, the query might run more slowly as Snowflake updates the materialized view or looks up data in the base table.
Are materialized views read only? ›
In a basic configuration, materialized views can provide read-only access to the table data that originates from a master site or master materialized view site. Applications can query data from read-only materialized views to avoid network access to the master site, regardless of network availability.How do I fetch data from materialized view? ›
You can use a materialized view in any SQL query by referencing the materialized view name as the data source, like a table or standard view. When a query accesses a materialized view, it sees only the data that is stored in the materialized view as of its most recent refresh.Can materialized view and table have same name? ›
When you choose a name for the materialized view, note that a schema cannot contain a table and view with the same name. CREATE [ MATERIALIZED ] VIEW produces an error if a table with the same name already exists in the schema.Does materialized view have primary key? ›
You can select data from a materialized view as you would from a table or view. In replication environments, the materialized views commonly created are primary key, rowid, object, and subquery materialized views.How to refresh data in materialized view in Oracle? ›
To refresh the Oracle materialized view, call one of the procedures in DBMS_MVIEW. The DBMS_MVIEW package provides three types of refresh operations: DBMS_MVIEW. REFRESH: Refreshes one or more Oracle materialized views.How to execute materialized view in Oracle? ›
- Query Rewrite Method 1: Text Match Rewrite.
- Query Rewrite Method 2: Join Back.
- Query Rewrite Method 3: Aggregate Computability.
- Query Rewrite Method 4: Aggregate Rollup.
- Query Rewrite Method 5: Rollup Using a Dimension.
- Query Rewrite Method 6: When Materialized Views Have Only a Subset of Data.
An update of a row in a materialized view might be done as a DELETE+INSERT. A refresh of a materialized view might involve a delete + insert of every row. A refresh might involve a truncate plus direct path load of ever row.What is the downside of materialized view? ›
In particular, Materialized Views have a few drawbacks to consider: They take up additional storage space. Data storage costs money. Materialized Views will often contain orders of magnitude less rows than the original data tables, but this is still something to keep in mind.Does materialized view create table? ›
Physically the materialized view is a table. The materialized view objects contains the metadata about the refresh mode, type, method and other properties. If you check the ALL_OBJECTS you see that MATERIALIZED VIEW object doesn't have its segment.How often should you refresh materialized view? ›
Automatic refresh. By default, materialized views are automatically refreshed within 5 minutes of a change to the base tables, but no more frequently than every 30 minutes.
Where is materialized view stored in Oracle? ›
A materialized view can be stored in the same database as its base tables or in a different database. Materialized views are often used to improve performance, especially when storing data locally that is sourced from tables or views using a database link.What is the difference between dynamic view and materialized view? ›
Views are the virtual projection of an output query or the dynamic view of the data in a database that is presented to the user whenever requested. Materialized views, on the other hand, are a non-virtual schema. It is a common part of database warehousing.How many materialized view can be created on single table? ›
The columns that you specify in the selection list must exist in the base table. You must specify at least one column in the selection list. You can select up to 64 columns for your materialized view. The columns in the ORDER BY list must be specified in the selection list.How do you refresh a materialized view daily? ›
So if you want to refresh mview daily, you need to keep it refresh on demand and set the next refresh time as sysdate + 1 . You can set any interval although. Once you do this the materialized view is created and a job is set in Oracle that will refresh mview every 24 hrs (sysdate + 1) .Does materialized view lock table? ›
PostgreSQL will lock the materialized view table while refreshing. You won't be able to execute any queries until it gets all the data updated, and that takes time.What are the refresh types in materialized view? ›
Materialized view refresh can be performed in incremental or a complete refresh. Incremental refresh contains two methods known as log-based refresh and partition change tracking (PCT) refresh. Incremental refresh also known as FAST refresh because it usually performs faster than complete refresh.How to check if materialized view is refreshed in Oracle? ›
While a job is running, a SELECT * FROM V$SESSION_LONGOPS statement will tell you the progress of each materialized view being refreshed. To look at the progress of which jobs are on which queue, use a SELECT * FROM DBA_JOBS_RUNNING statement.Can we automate refresh of materialized views? ›
You can set autorefresh for materialized views using CREATE MATERIALIZED VIEW. You can also use the AUTO REFRESH clause to refresh materialized views automatically.What is an ideal use case of a materialized view? ›
You can use materialized views to increase the speed of queries on very large databases. 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.Why do we use views instead of tables? ›
In certain situations, views can provide multiple advantages over regular tables: Views can contain (and usually do) joins between multiple tables, producing a single result set, thus reducing the complexity.
How does materialized view improve performance in Oracle? ›
Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request.Can we insert data into materialized view? ›
You can't insert data into a materialized view as you can with a table. To update the contents of a materialized view, you can execute a query to refresh it. This will re-execute the query used to create it.What is the limitations of materialized view? ›
Materialized views use a restricted SQL syntax and a limited set of aggregation functions. For more information, see Supported materialized views. Materialized views cannot be nested on other materialized views. Materialized views cannot query external tables.How do you refresh a materialized view? ›
To update the data in a materialized view, you can use the REFRESH MATERIALIZED VIEW statement at any time. When you use this statement, Amazon Redshift identifies changes that have taken place in the base table or tables, and then applies those changes to the materialized view.Can we update data in materialized view Oracle? ›
A materialized view can be either read-only, updatable, or writeable. Users cannot perform data manipulation language (DML) statements on read-only materialized views, but they can perform DML on updatable and writeable materialized views.How to remove duplicates in SQL? ›
According to Delete Duplicate Rows in SQL, you can also use the SQL RANK feature to get rid of the duplicate rows. Regardless of duplicate rows, the SQL RANK function returns a unique row ID for each row. You need to use aggregate functions like Max, Min, and AVG to perform calculations on data.What are the disadvantages of views in SQL? ›
Although there are many advantages to views, the main disadvantage to using views rather than real tables is performance degradation. Because views only create the appearance of a table, not a real table, the query processor must translate queries against the view into queries against the underlying source tables.Is it better to query a view or a table? ›
Because you store data in a table on the database, it can be quicker to access. Once you open the application, you can quickly access the information you seek. Data in a view can take longer to access because you have to run a query first. If you want results for data from multiple tables, this can take even longer.Can you edit a materialized view? ›
Use the ALTER MATERIALIZED VIEW statement to modify an existing materialized view in one or more of the following ways: To change its storage characteristics. To change its refresh method, mode, or time.