tailieunhanh - Using Materialized Views

After completing this lesson, you should be able to do the following: Create materialized views Refresh materialized views Create nested materialized views Create UNION ALL materialized views Explain the use of query rewrites Enable and control query rewrites | Using Materialized Views Objectives After completing this lesson, you should be able to do the following: Create materialized views Refresh materialized views Create nested materialized views Create UNION ALL materialized views Explain the use of query rewrites Enable and control query rewrites Materialized Views Instantiations of a SQL query May be used for query rewrites Refresh types: Complete or Fast Force or Never Refresh modes: Manual Automated (synchronous or asynchronous) Materialized Views A materialized view (MV) stores both the definition of a view and the rows resulting from the execution of the view. Like a view, it uses a query as the basis, but the query is executed at the time the view is created and the results are stored in a table. You can define the table with the same storage parameters as any other table and place it in the tablespace of your choice. You can also index and partition the materialized view table, like other tables, to improve the performance of . | Using Materialized Views Objectives After completing this lesson, you should be able to do the following: Create materialized views Refresh materialized views Create nested materialized views Create UNION ALL materialized views Explain the use of query rewrites Enable and control query rewrites Materialized Views Instantiations of a SQL query May be used for query rewrites Refresh types: Complete or Fast Force or Never Refresh modes: Manual Automated (synchronous or asynchronous) Materialized Views A materialized view (MV) stores both the definition of a view and the rows resulting from the execution of the view. Like a view, it uses a query as the basis, but the query is executed at the time the view is created and the results are stored in a table. You can define the table with the same storage parameters as any other table and place it in the tablespace of your choice. You can also index and partition the materialized view table, like other tables, to improve the performance of queries executed against them. When a query can be satisfied with data in a materialized view, the server transforms the query to reference the view rather than the base tables. By using a materialized view, expensive operations such as joins and aggregations do not need to be re-executed; instead the statement is rewritten to query the materialized view. Creating Materialized Views SQL> CREATE MATERIALIZED VIEW 2 depart_sal_sum AS 3 SELECT , SUM() 4 FROM d, e 5 WHERE = 6 GROUP BY ; Creating Materialized Views Materialized views have an advantage over regular views in that the result of the view is stored for future use. This means that the query does not have to be rebuilt every time the query is run. One of the problems with materialized views is that the data stored in the view can become outdated with the next modification to the underlying tables. Therefore, it is required to refresh