Materialized View in SQL | Faster SQL Queries using Materialized Views

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

[@MacPG-rc8ti]

@@DaveThomson Thanks for this hint. Do you mind sharing a practical scenario about this?

[@naguleshwarg2320]

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.

[@falconmack]

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

[@srinivaskattunga7778]

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

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

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

[@asutoshkumar1793]

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

[@techTFQ]

Thanks & noted

[@aap1803]

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]

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

[@nirmala3847]

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

Thank you Nirmala 🙏🏼 I will plan it

[@smrutimohanty8560]

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

[@amartyakumarsaha338]

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

[@engalibadouin5222]

Really the best tutor i see in database

[@sudarshanmhaisdhune1039]

Short, simple & easy to understand. Thanks Taufiq!

[@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;

[@karennguyen7083]

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]

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

[@karennguyen7083]

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

[@suviikshetty2969]

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

[@techTFQ]

Glad this helped

[@sridhargoud8457]

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

[@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 😅

[@kevinlovely2098]

You made this very simple to understand. Thanks!

[@jvkk123]

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

[@basil6909]

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

[@prabhakarraokaranam719]

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

[@techTFQ]

Thank you and noted bro

[@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🙏

[@Refreshment01]

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

[@umidkurbanov5495]

Bro, you're awesome. Very clearly explained

[@bindiyarastogi8793]

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

[@techTFQ]

Noted Bindiya

[@gangajairaman8742]

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]

Thank you and noted bro

[@CodeChaos_]

yes need materialized view in oracle

[@artrixmotion5885]

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

[@harshusaaho7623]

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

[@techTFQ]

Great, see you in the class

[@arunsundar3739]

beautiful explained, fundamentals of MV & how it can be really powerful in analytics use cases :)

[@kancharalaparameshwarreddy3837]

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

[@hammadyasir2174]

Excellent Sir.I really appreciate your effort

[@archanachaube7027]

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

[@ShiviSrivastava-p9n]

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.

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

[@mohammedbadah5537]

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

[@techTFQ]

Your welcome buddy and sure will do

[@fathimafarahna2633]

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

[@techTFQ]

Thank you 🙏🏼

[@shanmukhpriya]

You are really champ sir

[@EverydayKarma]

Nicely explained. Thanks

[@flowerpower6345]

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]

thank you and noted on the request bro

[@illiasukonnik9966]

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

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

[@rameshthanikonda7027]

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

[@akashgoel601]

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

[@techTFQ]

Thank you and noted bro

[@milindjogdand8630]

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

[@MST_Finvest]

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?

[@mohammedshahil4898]

Awesome👌 Very well explained🙌🏻

[@techTFQ]

Thank you bro

[@skdonsingh]

Just Wow.. Thank you.

[@nalinisathyanarayanan8666]

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]

Thank you Nalini and will do that soon

[@jirehla-ab1671]

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.

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

[@govindkeot8668]

Explained Very well,Thanks

[@MDNASIR-d8c]

Best Explanation........

[@sangameshwarhippargi2007]

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

[@techTFQ]

Noted and will do

[@venukumargadiparthy8233]

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

[@techTFQ]

Noted bro

[@kavithara9604]

Yes, Please cover materialized view in Oracle.

[@techTFQ]

Noted Kavi

[@anupamyadav5257]

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

[@techTFQ]

Noted bro

[@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,

[@vinothvinuable]

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

[@rajkumartripathi2855]

Yes i appreciate it😊

[@siddhartharao8490]

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

[@techTFQ]

Sure will do it, thank you

[@SM-km4gs]

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

[@techTFQ]

your welcome and sure will do

[@pauladams7850]

Vera level bro

[@no-one2177]

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

[@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 :)

[@sravanikolluru8129]

Super explanation

[@sayalishiledar8377]

Very well explained 👍

[@pratikhonrao733]

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

[@omkarmore2198]

great explaination ....

[@techTFQ]

Thanks Omkar 🙏🏼

[@sudarshanreddy943]

Really awesome 😎😎

[@Englishwavesodia123-c4i]

Good explanation 😊

[@Alexpudow]

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

[@victor.ruto.7919]

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

[@thamaraikannan7387]

Bro, I don't have complete knowledge in mv views, only a little bit, if possible make a video for oracle mviews refresh method and auto refresh features how it's working. Thanks in advance.

[@techTFQ]

Noted bro and will do

[@hasanmougharbel8030]

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.

[@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?

[@SandeepSingh-qd1qy]

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

[@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?

[@shivinmehta7368]

Please create a full course on SQL on the udemy platform. It will be beneficial for learners

[@techTFQ]

Probably in the future bro

[@pavanmunagasetty6930]

This video is nice

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

[@pawansahu167]

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,

[@joshuabnguyen87]

What is the difference between a materialized view and a temporary table?

[@CaribouDataScience]

A materialized table isn't temporary.

[@techTFQ]

I’ll make separate video about it

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

[@PradeepKumar-gq3jd]

Pls post a video about execution plan cardinality optimization

[@muninderkomire5617]

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

[@SravanthiKovvuri]

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

[@s0m30n3Jr]

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

[@pavanmunagasetty6930]

Please make a video how to execute materialized view in oracle, Microsoft sql server, mysql workbench

[@deepakshrikanttamhane285]

great

[@techTFQ]

Thank you 🙏🏼

[@neerajchouksey3761]

thanks

[@mirageman2]

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

[@channabasava9662]

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

[@SC-mx4wp]

Nice Bro

[@aniketjagdale7765]

Hi pls create videos on indexes and constraints in SQL

[@kanishkark4226]

Any video for indexes?

[@rahuldeshmukh8221]

Can you please make one full video about SQL?

[@ShilpavathiSB]

Can we create a Materialized view over normal view, how does this internally work. How would be the performance.

[@sugandhitasharma6417]

Can you please make a video on interview questions for data analysis please

[@techTFQ]

Let me consider it

[@sohailkan17]

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

[@ashmaywagh]

can you make video from starting SQL i m a beginner in this field. it's humble request to you.

[@geethikasumana7363]

Hi can u please do video on materialized views in Oracle

[@indhumathiarul1077]

Could you please explain Oracle MV in detail ?