Question 26 - DEA-C01 discussion

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 material-ized view is faster than executing a query against the base table of the view. This performance dif-ference can be significant when a query is run frequently or is sufficiently complex. As a result, ma-terialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets.

Materialized views require Enterprise Edition.

Materialized views are designed to improve query performance for workloads composed of common, repeated query patterns. However, materializing intermediate results incurs additional costs.

As such, before creating any materialized views, you should consider whether the costs are offset by the savings from re-using these results frequently enough.

Materialized views are particularly useful when:

· Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).

· Query results contain results that require significant processing, including:

1. Analysis of semi-structured data.

2. Aggregates that take a long time to calculate.

· The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.

The view's base table does not change frequently.