Materialized views
A materialized view (sometimes called a sorted, projected, and materialized view or SPM view) is a view whose columns have been sorted, projected, and materialized (that is, stored physically in a unique table).
In each materialized view, the system automatically adds a column that specifies, for each record in the view, the block number of the corresponding record in the base table. The system also automatically creates a zone map for each materialized view.
- It reduces the amount of data that must be transferred from the disk during scans.
- Because its data is sorted, the resulting zone map for ORDER BY columns is more efficient than it otherwise would be.
- Because its data is sorted and it has a zone map, a query that targets only a few records can retrieve the block locations of the records in the base table more quickly than by other means.
Materialized view for efficient ETL and BI
The goal is to minimize the number of rows ingested. The best way to do this is to ensure that your ETL tools are only copy in data that has changed since the last time (CDC).
Avoid joins between fact tables to the minimum by having redundant data in fact tables.
Minimize non-numeric attributes, Minimize the no of concurrent write transactions to avoid load queue up.
A Materialized View is used for vertical partitioning of the base table. It reduces the width of data being scanned in a base table by creating a thin version of the base table that contains a small subset of frequently queried columns. The Materialized View containing the sorted projection is stored in a table on disk and is used to increase query performance.
SELECT few columns and WHERE condition columns ORDER BY on the most restrictive column and periodically manually REFRESH the Materialized Views. This updates the sort order.