Materialized View in SQL | Faster SQL Queries using Materialized Views

Рет қаралды 102,696

Күн бұрын

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:
techtfq.com/my-sql-course
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 - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
THANK YOU,
Thoufiq

Пікірлер: 206

@naguleshwarg2320 2 жыл бұрын

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.

@jimzecca3961 Жыл бұрын

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.

@DaveThomson Жыл бұрын

you can also refresh the MV using a function and run that function from a trigger based on some conditions.

@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.

@falconmack 5 ай бұрын

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

@amartyakumarsaha338 2 жыл бұрын

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

@srinivaskattunga7778 2 жыл бұрын

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

@sudarshanmhaisdhune1039 Жыл бұрын

Short, simple & easy to understand. Thanks Taufiq!

@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!

@kevinlovely2098 3 ай бұрын

You made this very simple to understand. Thanks!

@archanachaube7027 2 жыл бұрын

Thanks for explaining concepts so clearly and simplifying complexed ones! Also can you make videos on Group by extensions (rollup, grouping sets.cube)

@umidkurbanov5495 6 ай бұрын

Bro, you're awesome. Very clearly explained

@asutoshkumar1793 7 ай бұрын

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

@techTFQ 7 ай бұрын

Thanks & noted

@jvkk123 Жыл бұрын

Thanks for sharing your knowledge, It's great that you provide training, i would like to get started

@skdonsingh Жыл бұрын

Just Wow.. Thank you.

@shahid9813 Жыл бұрын

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 < 100000 ) INSERT INTO random_for_matvw(id,value) SELECT id, value FROM rand;

@govindkeot8668 Жыл бұрын

Explained Very well,Thanks

@suviikshetty2969 Жыл бұрын

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

@techTFQ Жыл бұрын

Glad this helped

@smrutimohanty8560 3 ай бұрын

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

@oscararmandocisnerosruvalc8503 Жыл бұрын

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 !!!

@STTPwithRajani Жыл бұрын

Thanks a lot🙏 u are doing unique work... please add more information integration and application videos in ur playlist...it will be great help...I am phd cse in IIIT Delhi...ur videos are helping 🙂add data warehousing view, virtual view etc if u will add more IIA videos I will suggest this channel to no of my classmates🙏

@hammadyasir2174 Ай бұрын

Excellent Sir.I really appreciate your effort

@user-wx9qw2kr3q Жыл бұрын

This was very well explained and it was easy to understand. Please make a seperate video for Oracle as well. It would be great help.

@mulaparthisuniltej4022 2 жыл бұрын

Hello Brother, Awesome explanation with clarity.Please do video for mviews on oracle DB with many types of refreshes as you said and also can we have a view or mview on DML's instead of sql queries?

@prabhakarraokaranam719 2 жыл бұрын

awesome Tafaq .. thanks alot .. please continue sessions on Materialized views .. with all refresh methods .. it is very nice of you. Thanks once again

@techTFQ 2 жыл бұрын

Thank you and noted bro

@basil6909 Жыл бұрын

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

@AD-qk6zh Жыл бұрын

Hey Thoufiq, the content on materialized view was really useful, you make the concept very clear and easy to understand. Would be really glad if you could make the materialized view content using oracle too. Also , in your views tutorial :: was used . Could you please teach as how this is done in oracle . Thanks a lot :)

@kancharalaparameshwarreddy3837 2 жыл бұрын

sir your class videos are awesome ,i used to watch often sir if possible plz provide one or two classes on TRIGGERS AND JOB creation in postgresql

@sudarshanreddy943 Жыл бұрын

Really awesome 😎😎

@rajkumartripathi2855 5 ай бұрын

Yes i appreciate it😊

@sravanikolluru8129 Жыл бұрын

Super explanation

@sayalishiledar8377 Жыл бұрын

Very well explained 👍

@artrixmotion5885 Жыл бұрын

Very Helpful, Kindly make separate video for MS SQL materialized View sir. Thanks ahead & more power

@Refreshment01 2 жыл бұрын

REQUEST: Please make advanced video odf postgres Regular Expressions. Your videos & blog are among the best on DBMS topics. 👍

@fathimafarahna2633 2 жыл бұрын

👌🏻👌🏻👌🏻👌🏻👌🏻on point

@techTFQ 2 жыл бұрын

Thank you 🙏🏼

@pauladams7850 Ай бұрын

Vera level bro

@no-one2177 22 күн бұрын

bro did he cover all the concepts in this course??

@neerajchouksey3761 3 ай бұрын

thanks

@mohammedshahil4898 2 жыл бұрын

Awesome👌 Very well explained🙌🏻

@techTFQ 2 жыл бұрын

Thank you bro

@bindiyarastogi8793 2 жыл бұрын

Your videos are really helpful and clear the concepts. Please make a series on pl/sql

@techTFQ 2 жыл бұрын

Noted Bindiya

@gangajairaman8742 2 жыл бұрын

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

@techTFQ 2 жыл бұрын

Thank you and noted bro

@aap1803 2 жыл бұрын

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.

@techTFQ 2 жыл бұрын

Hi Achal, I am fine and thank you 🙏🏼 Noted on the request, I’ll plan it

@nirmala3847 2 жыл бұрын

@@techTFQ my request also same sir please make videos for all the important components your explanation is very good iam from telugu I don't know English very well but i understand your English it is very simple and understandable

@techTFQ 2 жыл бұрын

Thank you Nirmala 🙏🏼 I will plan it

@pavanmunagasetty6930 Жыл бұрын

This video is nice

@rameshthanikonda7027 Жыл бұрын

Hi Toufiq, Firstly thanks for the video. Kindly explain the concept of materialized view in Oracle Database as well. Thank you once again..

@vinothvinuable 10 ай бұрын

Great ! It would be good if you could explain the index on view.

@SC-mx4wp Жыл бұрын

Nice Bro

@omkarmore2198 2 жыл бұрын

great explaination ....

@techTFQ 2 жыл бұрын

Thanks Omkar 🙏🏼

@thefireclipse Жыл бұрын

This was a great video to watch, thank you! Please do make a video on how Oracle materialized views have more functionalities than Postgres :)

@techTFQ Жыл бұрын

Your welcome and sure wil do

@deepakshrikanttamhane285 Жыл бұрын

great

@techTFQ Жыл бұрын

Thank you 🙏🏼

@illiasukonnik9966 5 ай бұрын

Thanks, greate video!! So materialized view is a form of denormalization, when we store precalculated data. viv ))

@Blowjin 9 ай бұрын

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 😅

@endalekinfe3391 Ай бұрын

Some database systems, like SQL Server with indexed views, maintain the materialized view in real-time. Any changes to the base tables are immediately reflected in the materialized view. This means the view is always up-to-date,

@flowerpower6345 2 жыл бұрын

all your videos are very simple and helpful. Thank you. Can you please create a video on oracle performance tuning tips and also on how to partition oracle tables to improve sql performance

@techTFQ 2 жыл бұрын

thank you and noted on the request bro

@madhusmyakala1687 2 жыл бұрын

Yes, Please cover materialized view in Oracle.

@karennguyen7083 2 жыл бұрын

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.

@techTFQ 2 жыл бұрын

Hi Pham, I understand your confusion but difficult to explain it in comment here.. perhaps will do in a video

@karennguyen7083 2 жыл бұрын

@@techTFQ Happy to wait and thank you so much ^^

@SandeepSingh-qd1qy Жыл бұрын

It was really nice explanation. Yaa could you please create video for oracle

@mohit3300 Жыл бұрын

thank you very much for this great tutorial sir. I want to know if, in the MS SQL server all process is the same or not?

@harshusaaho7623 2 жыл бұрын

I took your course broah ...I hope I get a lot of insights !!!

@techTFQ 2 жыл бұрын

Great, see you in the class

@nalinisathyanarayanan8666 2 жыл бұрын

Hi sir, Great work 👍 Really it's very helpful for learners like me... Expecting video by explaining Oracle materialized view concepts and refreshing methods too

@techTFQ 2 жыл бұрын

Thank you Nalini and will do that soon

@jirehla-ab1671 9 ай бұрын

Hi , i remember oraclr offering free entperse edition of oracle 21c (non-commercial use only), but i cant find the same equivalent for 23c since the 23c develop edition haa 12gb ram limit.

@muninderkomire5617 Жыл бұрын

Great explanation. When are you going to conduct live sessions again?

@sangameshwarhippargi2007 2 жыл бұрын

Thanks for this wonderful video. Please do create video on Oracle materialized view and it's refresh types. Thanks in advance.

@techTFQ 2 жыл бұрын

Noted and will do

@hasanmougharbel8030 2 жыл бұрын

Hey dear, god bless your efforts in this channel. I have a general enquiry as a new sql learner. How could i create a pipeline to extract and load data from existing accounting program into our SQL server instances. How can i know if the export mechanism in the software permits me to undertake this extraction process, and how can i know if an application have an api? Thanks for taking care of my enquires. Looking forward to gain more knowledge from you.

@rockymarquiss8327 Жыл бұрын

Took a while to figure out what you were saying when you're saying view - sounds like veev... LOL I can see a number of usual uses of materialized views.

@SM-km4gs Жыл бұрын

Thanks for the explanation...please make video on oracle as well.

@techTFQ Жыл бұрын

your welcome and sure will do

@sridhargoud8457 2 жыл бұрын

Trigger, Cursor,Function,Index,Record,Exception Handling,Package Please do these videos as soon as possible

@pavanmunagasetty6930 Жыл бұрын

Pls do the video on materialized view covering oracle

@PradeepKumar-gq3jd Жыл бұрын

Pls post a video about execution plan cardinality optimization

@victor.ruto.7919 Жыл бұрын

Thank you, can you kindly make one for Oracle? had problem refreshing mv in Oracle

@Alexpudow 5 ай бұрын

In Qlik we use qvd the same way, and incremental load if need.

@mohammedbadah5537 2 жыл бұрын

Very clear video ... Please show us materialized view in Oracle Thank you so much Toufig

@techTFQ 2 жыл бұрын

Your welcome buddy and sure will do

@akashgoel601 2 жыл бұрын

Great video.. Pls consider making video for materialised view in oracle and it's different functionality.. Cheers!!

@techTFQ 2 жыл бұрын

Thank you and noted bro

@milindjogdand8630 Жыл бұрын

@@techTFQ Thanks Sir. Please make video for materialized view in Oracle.

@venukumargadiparthy8233 2 жыл бұрын

Hello , your videos are really helpful, please do a video on MS SQL materialized views

@techTFQ 2 жыл бұрын

Noted bro

@sivap5241 Жыл бұрын

Hi bro, video explanation is very clear about mview. I tryed to worked that in SQL developer. I can't create the random_tab like you create. What's the syntax for SQL developer bro

@s0m30n3Jr Жыл бұрын

please make a video of materialized view for Oracle. thank you.

@aniketjagdale7765 6 ай бұрын

Hi pls create videos on indexes and constraints in SQL

@kanishkark4226 3 ай бұрын

Any video for indexes?

@mahadevabamanalli5769 Жыл бұрын

Plz make a video on Oracle materialized view

@anupamyadav5257 2 жыл бұрын

Thanks for making video..... Please make video for Oracle on both the views in detail

@techTFQ 2 жыл бұрын

Noted bro

@nareshsmily226 2 жыл бұрын

Please make a video on temp table on Oracle

@pratikhonrao733 4 ай бұрын

Hello TFQ - what's the difference between Materialized Views and TEMP tables?

@aniketjagdale7765 6 ай бұрын

Hi pls create a materialized view video on Oracle database as well

@shilpagupta6376 Жыл бұрын

Please share a video on materialized view in Oracle.

@user-fb8wy3ij2x 5 ай бұрын

If possible can you please make separate video of oracle views.

@jeevaj569 Жыл бұрын

pls post a video on materialized view in oracle

@pawansahu167 2 жыл бұрын

Thoufiq Bro, Can you make a video on Joins, tables with only one column each, How to tell number of rows just by observing tables, columns also contain null values,

@Avatar911 Жыл бұрын

When user updates base table we can create trigger to refresh the materialised view right ? In this way we can retrieve updated data is it good practice?

@vijayjoshi-mw8cr 6 ай бұрын

Hello, Please make video on oracle database also...

@kancharalaparameshwarreddy3837 2 жыл бұрын

sir plz provide videos on cursors,Triggers and job creation

@saireddy4752 2 жыл бұрын

Please Make video on Materialized view on oracle

@geethikasumana7363 4 ай бұрын

Hi can u please do video on materialized views in Oracle

@CodeChaos_ 4 ай бұрын

yes need materialized view in oracle

@sohailkan17 Жыл бұрын

Can you make a video on registered servers and stored procedures creation and execution

@mirageman2 Жыл бұрын

Hi I would be interested in Materialized Views in Oracle and which options are at hand there.

@hanumanthchinna7676 Жыл бұрын

bro can you explain about array and struct plz i waiting for the videos

@rahuldeshmukh8221 Жыл бұрын

Can you please make one full video about SQL?

@indhumathiarul1077 Ай бұрын

Could you please explain Oracle MV in detail ?

@AtulSingh-be1jk Жыл бұрын

Hello sir, Thank you so much for this . Can you please make a separate video on the materialized view in Oracle. 🙏🙏

@gtsanthosh9568 Жыл бұрын

Yes, please make a video on Oracle materialized views..

@karthikeyanarumugam03 Жыл бұрын

please do it in oracle also

@adityakamble1558 Жыл бұрын

Please create a views from the oracle

@siddhartharao8490 2 жыл бұрын

Sir, Kindly make a video on Materialized views in Oracle also.! Thanks for explanation!

@techTFQ 2 жыл бұрын

Sure will do it, thank you

@channabasava9662 2 жыл бұрын

Hi sir ur explanation good I have a 1 doubt in joints Without common column or name how to join the multiple tables

@jatinjangir2293 Жыл бұрын

Hello sir, I created a Mv on the base table which had 10 records then i manually inserted 5 records then i refreshed the MV concurrently now when i am selecting * from mv i am getting all 15 reocrds but i want only 5 records which i manually inserted so how i can get those?