Difference between views in Oracle

5 pts.
Tags:
Oracle
Oracle Views
What is the difference between view and metialized view in Oracle?

Software/Hardware used:
oracle

Answer Wiki

Thanks. We'll let you know when a new response is added.

I guess you meant “materialized views”

I would say the main difference is that materialized views store the results of their query in the database, while normal views only store the query itself, and thus materialized views need to be refreshed (manually or automatically) when the data in the base tables change.

Normal views and materialized views are used for different purposes.

Have a look at the following document: Views, Materialized Views and Synonyms

————- kccrosser
In very simplified terms, you could think of a view as a “macro” expression that is substituted in your sql query at run time. So when your query executes, the underlying view query is executed at that time.

When you define a materialized view, an actual database table is created to store the results of the view query. Since a real table is created, you can then apply indexes to the columns in the table.

As CarlosDL notes, with a materialized view, it is necessary to refresh the table. Depending on the system (SQL, Oracle, etc.) and the use of the materialized view, you might refresh it nightly, hourly, or even every time one of the underlying tables changes. If you choose to refresh it periodically, then you need to be aware that the materialized view may not reflect the current state of the data in the underlying tables in between refreshes, while a non-materialized view will always run the query against the current data.

What many people don’t realize is that the database query engine can usually only effectively use indexes when dealing with relatively simple single-level views. Once you create a complex view, especially one that references other views, most engines will give up trying to use indexes on the underlying tables, which can lead to performance problems. If you need to use complex views on large tables, materialized views let you create those layers and then apply appropriate indexes.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following