Differencebetween Views and Materialized Views in Oracle

What is the difference between Views and Materialized Views in Oracle?

Materialized views are disk based and are updated periodically based upon the query definition.

Views are virtual only and run the query definition each time they are accessed.

Views VS Materialized Views

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.

What is the difference between Views and Materialized Views in Oracle?

Materialized views are disk based and are updated periodically based upon the query definition.

Views are virtual only and run the query definition each time they are accessed.

difference between view and indexed view or materialized view

The key difference is that materialized view is well, materialized. This basically means that data is being persisted into a virtual table which is maintained by SQL Server itself.

This has both benefits and issues. Notable benefits:

  • Commonly used queries can be encapsulated in a view and indexed in order to improve read performance (compare running select from a single table versus, for instance, 5 tables that are joined)
  • Aggregations can be precomputed and would also improve read performance

Drawbacks:

  • It will certainly impact write performance because with each DML operation, SQL Server will have to update view. This can be observed in execution plans
  • It can negatively impact replication performance if subscriber creates a material view from a replicated table
  • A lot of restrictions in order to create an indexed view
  • If you're using a non-enterprise SQL Server version, WITH (NOEXPAND) hint must be added, otherwise SQL Server will expand view and will just run SQL statement within it and totally ignore index.
  • DBAs usually tend to avoid them because they add extra maintenance.

SQL Server Indexed Views vs Oracle Materialized View

SQL Server’s indexed views are always kept up to date. In SQL Server, if a view’s base tables are modified, then the view’s indexes are also kept up to date in the same atomic transaction.

Oracle provides something similar called a materialized view. If Oracle’s materialized views are created without the **REFRESH FAST ON COMMIT** option, then the materialized view is not modified when its base tables are. So that’s one major difference. While SQL Server’s indexed views are always kept current, Oracle’s materialized views can be static.

Difference between streams and materialized views oracle

Streams is the more recent, preferred architecture. Streams is more efficient than materialized views-- Streams reads data from the redo logs asynchonously while materialized views (assuming you want to replicate only changes) have to synchronously write to materialized view logs. It is more flexible. It is the solution that Oracle invests time and effort in improving (though Oracle is in the process of integrating Streams with Golden Gate now and Golden Gate will be the replication technology of choice going forward).

Materialized views are a less efficient approach but they can be easier to set up and to configure (particularly if the admins of the source database aren't interested in helping you). There are undoubted more people walking around that have experience using materialized views simply because they've been around quite some time. If you have relatively simplistic requirements-- say, you want to replicate data from a source database once a day at a fixed time-- materialized views may be sufficient. If you want real-time replication, you're much better served using Streams.

What are materialized views?

Sure.

A normal view is a query that defines a virtual table -- you don't actually have the data sitting in the table, you create it on the fly by executing.

A materialized view is a view where the query gets run and the data gets saved in an actual table.

The data in the materialized view gets refreshed when you tell it to.

A couple use cases:

  • We have multiple Oracle instances where we want to have the master data on one instance, and a reasonably current copy of the data on the other instances. We don't want to assume that the database links between them will always be up and operating. So we set up materialized views on the other instances, with queries like select a,b,c from mytable@master and tell them to refresh daily.

  • Materialized views are also useful in query rewrite. Let's say you have a fact table in a data warehouse with every book ever borrowed from a library, with dates and borrowers. And that staff regularly want to know how many times a book has been borrowed. Then build a materialized view as select book_id, book_name, count(*) as borrowings from book_trans group by book_id, book_name, set it for whatever update frequency you want -- usually the update frequency for the warehouse itself. Now if somebody runs a query like that for a particular book against the book_trans table, the query rewrite capability in Oracle will be smart enough to look at the materialized view rather than walking through the millions of rows in book_trans.

Usually, you're building materialized views for performance and stability reasons -- flaky networks, or doing long queries off hours.



Related Topics



Leave a reply



Submit