Difference between View and Materialized View in Database or SQL?

The difference between View and Materialized view is one of the popular SQL interview questions, much like truncate vs delete, correlated vs noncorrelated subquery, or primary key vs unique key. This is one of the classic questions which keeps appearing in SQL interview now and then and you simply can’t afford to learn about them. Doesn’t matter if you are a programmer, developer, or DBA, these SQL questions are common to all. Views are a concept which not every programmer familiar with, it simply not in the category of CRUD operation or database transactions or SELECT query, its little-advanced concept for the average programmer.

Views allow a level of separation than an original table in terms of access rights but it always fetches updated data. Let’s see what is View in the database, what is materialized View, and the difference between view and materialized view in the Oracle database.





What is View in a Database?

Views are a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables.

The performance of the view depends on our select query. If we want to improve the performance of view we should avoid using join statements in our query or if we need multiple joins between tables always try to use the index-based column for joining as we know index-based columns are faster than a non-index-based column.

View also allows storing the definition of the query in the database itself.

Btw, If you are new to SQL and don't understand fundamentals like triggers, views, or co-related sub-queries then I highly recommend you go through a comprehensive SQL course like The Complete SQL Bootcamp by Jose Portilla on Udemy. It's one of the best and also most affordable courses to learn SQL online

difference between view and materialized view in SQL and database




What is Materialized View in a Database?

Materialized views are also the logical view of our data-driven by the select query but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.

When we see the performance of Materialized view it is better than normal View because the data of materialized view will be stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

See Oracle Database 12c Fundamentals, an online course from Pluralsight to learn more about how Oracle database stores Views.

What is difference between View and Materialized View in Database or SQL?




Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) The first difference between View and materialized view is that In Views query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table.


2) Another difference between View vs materialized view is that, when we create a view using any table, rowid of view is the same as the original table but in the case of Materialized view rowid is different. 

3) One more difference between the View and materialized view in the database is that In the case of View we always get the latest data but in the case of the Materialized view we need to refresh the view for getting the latest data.

4) Performance of View is less than Materialized view.

5) This is the continuation of the first difference between View and Materialized View, In the case of view it's only the logical view of the table no separate copy of the table but in the case of Materialized view we get a physically separate copy of the table

6) Last difference between View vs Materialized View is that In the case of Materialized view we need an extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in the database. You can further see Oracle SQL Performance Tuning Masterclass for more details on the materialized view in Oracle.



When to Use View vs Materialized View in SQL

Mostly in an application, we use views because they are a more feasible, only logical representation of table data no extra space needed.

We easily get a replica of data and we can perform our operation on that data without affecting actual table data but when we see a performance that is crucial for a large application they use a materialized view where Query Response time matters.

So, Materialized views are used mostly with data warehousing or business intelligence application.

view vs materialized view in oracle



That’s all about the difference between View and Materialized View in database or SQL. I suggest always prepare this question in good detail and if you can get some hands-on practice like creating Views, getting data from Views then try that as well.


Further Learning
Introduction to SQL
The Complete SQL Bootcamp
200+ SQL Interview Questions and Answers


Other related SQL queries, Interview questions, and articles:
  • 5 Courses to learn Database and SQL Better (courses)
  • Difference between Self and Equi Join in SQL? (answer)
  • 5 Free Courses to learn Oracle and SQL Server? (courses)
  • Top 5 Courses to learn MySQL Database for Beginners (Courses)
  • 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
  • How to join three tables in one single SQL query (solution)
  • Top 5 Websites to learn SQL online for FREE? (resource)
  • How do you find the duplicate rows in a table on a database? (solution)
  • The real difference between WHERE and HAVING clause in SQL? (answer)
  • 4 ways to find the Nth highest salary in SQL (solution)
  • Difference between clustered and non-clustered indexes in SQL? (answer)
  • 5 Advanced SQL books to level up your SQL skills (books)
  • Write a SQL query to copy or backup a table in MySQL (solution)
  • Top 5 Courses to learn PostgreSQL in-depth (courses)
  • 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
  • Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
  • Difference between the Unique and Primary key in the table? (answer)
  • 5 Free Courses to learn Database and SQL (free courses)

Thanks for reading this article, if you find these SQL interview questions useful, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S. - If you are a beginner and interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at the Introduction to Databases and SQL Querying free course on Udemy to kick-start your learning

19 comments:

  1. Hi Paul,
    I would like to know if Materialized views occupy more space than the normal views?
    Thanks,
    Sushmita

    ReplyDelete
    Replies
    1. Hi Sushmita, Yes it does. Since materialized views are stored with data in disk obviously they occupy more space. Normal view doesn't store any data just definition, data always remains with corresponding table.

      Delete
  2. hello paul,
    If i update in table it will affect view(materialized) and how can i create materialized view

    ReplyDelete
  3. After refresh materialized view gets updated. syntax for materialized view creation is
    create materialized view MV_V as select * from emp.

    ReplyDelete
    Replies
    1. you can't use SELECT * ,, Instead you need to define column name and for table name need to use schema..
      Like.
      Create view viewname
      with schemabinding
      as
      SELECT name, empid from dbo.employee

      Delete
  4. What are the refresh methods in materialized view ???

    ReplyDelete
    Replies
    1. refresh fast - it will only the changes.(requires the materialized log file to be created on the source table to record the changes.
      refresh complete - it will truncate the full table and reload the complete data in the view.
      refresh force - it will start with refresh fast , if it get fails then do refresh complete

      refresh can be done based on our choices as below:-
      refresh on commit - it will refresh the data as soon as any commit happens in the parent table. ----it is not advisable as well as not recommended.

      refresh on demand - it will refresh the data based on our choices.

      Delete
  5. Where stores materialized view???

    ReplyDelete
  6. Can I update view and materialized view ?

    ReplyDelete
    Replies
    1. View Produces udpated reply every time you execute the view, where as you need to extenally update the materalised view to get the updated data of basetable.
      Please note that materalised view can be auto updated for this we have to define auto update at time of Materalised view declaration.

      Delete
  7. A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.
    http://www.oraappdata.com/2016/04/materialized-view.html

    ReplyDelete
  8. Good one here! Thanks for the post. :)

    ReplyDelete
  9. can we create index and trigger on both normal and materialized views.

    If yes, how?

    ReplyDelete
  10. View is a database object and also views are used to we can store data from table.views created on tables Oracle having the following types of views these are.
    1.simple views
    2.complex views
    1.simple view
    Simple views are used to we can not
    store data into database or horddiskha when ever we are droping a base table then auto matically views are not accessible wheare as when ever we are using materilized views automTically stored data into database or hord disk
    2.we can perfor dml on simple views where as on materilized views we can not perform dmlX
    3.in simplssviews having same roads of base table where as mview having different rowids when ever we are using refresh.
    4.normal views get leatestdta where as mviewedoes not get latest data

    ReplyDelete
  11. Replies
    1. Views and materilized views both are perform the ddl operations are not?

      Delete
  12. Hi,
    Can we insert/update data in the Views?

    ReplyDelete

Feel free to comment, ask questions if you have any doubt.