Views VS Materialized Views

Question

What's the actual difference between View and Materialized View?

I have gone through lot of links where they said normal View is a like a virtual table and data cannot be stored on a disk where in case materialized data will get stores on the disk. But from there I'm getting nothing like when to use normal view and when to use Materialized view. If I create both the views I can see in my schema. I want to know exact when and where we use the normal view and we use materialized view. Please provide me the solution with practical example.

Thanks

Answer 1

A view is just a stored query. It's a handy way of saving some complicated business logic (joins, filters, derived values) so it can be reused and shared with other users.

A materialized view is also a way of saving a query but it materializes the result set. That is, it creates a copy of the data on disk.

The main reason for using a materialized view is improved performance. Every time we select from a view we execute the whole query; if it's an expensive query that's a cost we pay each time. With a materialized view we trade disk space for time.

Furthermore, we can treat a materialized view like a table: we can index them, we can add key constraints, we can even reference them in a foreign key.

The main reason not to use a materialized view is cost. They take up disk space. They also have to be maintained. By default materialized views are static, which means their data gradually becomes stale over time. Refreshing a materialized view can be burdensome (depending on the underlying query). Whereas querying a view always gives us the most up-to-date view of the data.

The following are only guidelines, and neither complete or distinct

When to use a view

• when we want to make a query reusable, shareable and controllable
• when we want to have a simple interface over internal complexity
• when we want to enforce access restrictions to our data

When to use a materialized view

• when we want to run standard summarising (usually aggregating) queries against a large volume of data
• when we need data from another database and we can't guarantee its availability
• the view use cases when the volume of the base data is sufficiently large and the performance requirements sufficiently strict that it's cost effective to materialize the data

---

The answers to your questions are in the documentation(*). I have no intention of rewriting that fine manual but I will address your questions because having started I am condemned to continue.

1. A materialized view is a copy of data from one or more tables, perhaps in other schemas or even other databases.
2. As I said, copying data comes with overheads. Storage space and dealing with stale data are the big costs.
3. A view is just a query, there is literally nothing to index.
4. select * from user_extents where segment_name = 'name of mview'
5. Refresh on commit is not free. It costs system resources to execute (transactions over the source table will take longer). Besides, many materialized views can only support complete on demand refresh.

(*) This is Oracle's documentation because that's what your profile suggests but other RDBMS platforms have similar docs.