Chapter 8 - Materialized Views
“A Materialized View walked up to two tables at a bar and said, ‘Mind if I join you?’”
Tera-Tom Coffing
A materialized view reduces the width of a table by taking only the most important columns from the base table and creating a thin version of the table. The materialized view containing the sorted projection (columns) is physically stored in a table on disk and is a standard performance tuning technique. Materialized views transparently avoid scanning unreferenced columns. In other words, the blocks are much smaller in the materialized view because less columns are in the view than the base table. Less movement makes for faster queries. Materialized views require no change at the application level. The query planner/optimizer automatically checks for materialized views and takes advantage of them when they exist, and the optimizer deems that they would be faster to use than the original table.
Good Information to know about Materialized Views
Syntax/Example to Create a Materialized View
You can only pose read-only queries to materialized views. Also, if you direct a query to the base table, the optimizer checks for the existence of any associated materialized views. If materialized views exist, the optimizer determines whether to use a view based on its predicted cost (query time) and performance.
There As a best practice, do not drop and recreate the materialized view because those steps result in a new view with a different object ID, which could impact other objects that reference the materialized view. It is better to REPLACE the view. You can only pose read-only queries to materialized views. Also, if you direct a query to the base table, the optimizer checks for the existence of any associated materialized views. If materialized views exist, the optimizer determines whether to use a view based on its predicted cost (query time) and performance.
Zone Maps for Materialized Views
The system creates zone maps for all columns in materialized views that have data types integer, date, and timestamp. The system also creates zone maps for all ORDER BY columns in the materialized view, except for columns of numeric types that are larger than 8 bytes (19 decimal digits or more).
Materialized View Restrictions
Maintaining Materialized Views
Materialized View Best Practices
You can use MATERIALIZE REFRESH THRESHOLD <number> to set the threshold percentage of unsorted data in a materialized view. This threshold refreshes all materialized views associated with a base table that have exceeded the threshold limit. The threshold number is the percentage of unsorted data from 1 to 99 in the materialized view. The refresh threshold by default value is set to 20. When you use the ALTER VIEWS ON <base table> MATERIALIZE REFRESH command, the system refreshes all materialized views whose unsorted data has exceeded the refresh threshold.