Materialized View in SQL | Faster SQL Queries using Materialized Views

Improve the performance of your SQL Query using Materialized View. In this video, we learn everything you need to know about Materialized View in PostgreSQL. We learn about what is Materialized View, How does it improve query performance and how does it work?

In order to showcase the performance improvement due to Materialized View, we first create a table and load millions of records into this table and then write an SQL query on it to see the slow query execution time. We then create a Materialized View over this SQL query and execute the Materialized View to see the boost in performance. I shall explain the reason for this performance improvement during the video.

Join My SQL Course here:

Timestamp:
00:00 Introduction to Materialized View
02:14 What is Materialized View?
02:30 What does Materialized View store?
03:08 Creating a table with 20 million records
06:53 Create a Materialized View
08:34 How does Materialized View execute SQL queries faster?
09:31 Refresh Materialized View
12:12 What is the use of Materialized View?
14:08 Difference between Materialized View and View

Please do Subscribe to the channel and give me a Thumps up, if you found the video useful.

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

THANK YOU,
Thoufiq

[naguleshwarg]

Hello TFQ.. Really loved the way you teach SQL concepts. It would be great if you could make videos on Trigger, Cursor, Function, Index, Record, Exception Handling, Package and Partitions. It would be of immense help for every aspiring data scientists.

[jimzecca]

Good summary. Two important items.
1) The materialized view is essentially a normal table under the hood with query logic for populating it thus you can index it, etc.
2) The data is basically static until you refresh it at which time it's flushed and the data is replaced by the result of the query at the new run time.
They're particularly good when the performance to run the query is poor but the data doesn't have to be exact or up to the last second. For example, if you wanted to run a query that generates a report for the previous day you could create the materialized view to get the data from yesterday and run it on a schedule after midnight. Then the user can query the materialized view with a select * in the morning and get quick results without waiting on the query to execute against the base data. Sometimes it makes sense to have the materialized view to contain most of the result set and then some optimized query to just pull data from the current day, hour, etc. and union the results together.

[kpoddarmeasme]

I like the way you explain each and every concept in detail.If I don't understand any concept in SQL, I immediately switch to your channel and it really helps.

[clumsy_weirdo]

It the the best tutorial for understanding. I'm really glad that I can recognize English speech because in my native language I didn't find any information about. Author, thank you!

[falconmack]

Extremely helpful video, I didn't know nothing about views, and now I feel like an expert. THANK YOU SO MUCH

[Blowjin]

Thank you! So, as I understood, matetrialized view is a kind of cache.
It store query and it's result. Menwhile a just view stores query only.

Nevermind, just my synopsis 😅

[srinivaskattunga]

The BEST Training for SQL learners, the teaching concepts are "JUST AMAZING"

[shahid]

Learnt from you previous video instead of generate_series() function we can use Recursive .

CREATE TABLE random_for_matvw (

id INT PRIMARY KEY,
value INT
);

WITH RECURSIVE rand(id, value) as (
SELECT 1 as id, 1 as value
UNION ALL
SELECT id + 1, value + 1 from rand where id <
)
INSERT INTO random_for_matvw(id, value) SELECT id, value FROM rand;

[asutoshkumar]

Great Explanation, also Pronunciation for View is "vu" 😊

[aap]

Hello Toufiq, I hope you are doing good. I'm learning PL/SQL for almost last 6 months now but i have struggled to hold grasp on PLSQL concepts but after watching your videos and how clearly you explain i was able to understand very good. Like your video on Materialized View i knew how it work but after watching your video i understood the concept fully. I would really thank you for your work. Same goes with Procedure, Joins. I have a request if you could make PLSQL videos on Trigger, Cursor, Function, Index, Record, Exception Handling, Package and other if you could. Thanks for the awesome work you are doing.

[gangajairaman]

Hi Tfq, your explanation about the concept is very good. Could you please upload the separate video for Oracle materialized view and view? Thanks

[basil]

In my work area we refresh the materialized view twice a day. One in the morning and one in the evening.

[karennguyen]

Hi techTFQ, thanks for uploading amazing video, I learned a lot from you. And now I have a question.

I am confused about these syntax:
- create view
- create materialized view
- create temporary table
- with clause
These syntax can store a subset data to reuse then, but when to use it?

I knew we can use With clause when using specific subqueries many times but how are other syntax?

Thank you so much.

[suviikshetty]

Hi tfq, worth watching . It was great and clear. Thanks for the video.

[smrutimohanty]

very nice. Even though I am a PM and do not work technically on SQL, I could understand the concepts. Keep up the good work

[amartyakumarsaha]

The way you have explained it is amazing and thanks for this incredible content.

[pavanmunagasetty]

Pls do the video on materialized view covering oracle

[kevinlovely]

You made this very simple to understand. Thanks!

[sudarshanmhaisdhune]

Short, simple & easy to understand. Thanks Taufiq!

[oscararmandocisnerosruvalc]

Hello TFQ, Thanks a lot for all the knowledge you share.
can you create a list using SQL for data engineering purposes pls, it would be great to see how you address this topic.
Regards from Mexico !!!