Wisdom of Helios

View and Materialized view

2 Comments

View –  View evaluates the data/dataset in the tables underlying the view definition when the view is queried.

The data/dataset of view is not stored anywhere.

View is a logical or virtual memory based.

Materialized view – Materialized View is physical duplication of data of  a Table.  The data inside the table may come from           join quires of other tables.

The Data or Dataset is stored in a table.

Materialized views are disk based.

(Materialized views are schema objects that can be used to summarize precompute replicate and distribute data. E.g. to construct a data warehouse.)

Advantage of using a view

View always returns the latest data. no REFRESH of the view is required.

Drawback of using a view

Since view collects data from join queries of multiple tables and when the view is queried it performs the join operations each time. So its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.

Advantage of using a Materialized view

when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once , rather than each time you select from the materialized view.

An updatable materialized view lets you insert update and delete.

Drawback of using a Materialized view

The data you get back from the materialized view is only as up to date as the last time the materialized view has been REFRESHed.

(Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables)

Author: Munir

I'm good for nothing

2 thoughts on “View and Materialized view

  1. Very nice post. very descriptive and easy to understand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s