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.
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.
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!
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.
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 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
awesome Tafaq .. thanks alot .. please continue sessions on Materialized views .. with all refresh methods .. it is very nice of you. Thanks once again
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.
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 😅
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;
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🙏
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
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 !!!
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
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.
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,
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?
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?
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.
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.
Microsoft documentations says a materialized view persists the data returned from the view definition query and automatically gets updated as data changes in the underlying tables, not sure the documentation is wrong or not.
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 :)
Hi there! in this query has not worked in Microsoft sql server Database insert into random_tab select 1,rand() from GENERATE_SERIES(START = 1, STOP = 10) please try to explain on Ms SQL server
Great Explanation, also Pronunciation for View is "vu" 😊
Thanks & noted
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.
you can also refresh the MV using a function and run that function from a trigger based on some conditions.
@@DaveThomson Thanks for this hint. Do you mind sharing a practical scenario about this?
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.
Extremely helpful video, I didn't know nothing about views, and now I feel like an expert. THANK YOU SO MUCH
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!
The BEST Training for SQL learners, the teaching concepts are "JUST AMAZING"
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.
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
Really the best tutor i see in database
Short, simple & easy to understand. Thanks Taufiq!
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.
Hi Achal, I am fine and thank you 🙏🏼
Noted on the request, I’ll plan it
@@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
Thank you Nirmala 🙏🏼
I will plan it
Hi tfq, worth watching . It was great and clear. Thanks for the video.
Glad this helped
The way you have explained it is amazing and thanks for this incredible content.
Best Explanation........
👌🏻👌🏻👌🏻👌🏻👌🏻on point
Thank you 🙏🏼
Nicely explained. Thanks
You made this very simple to understand. Thanks!
awesome Tafaq .. thanks alot .. please continue sessions on Materialized views .. with all refresh methods .. it is very nice of you. Thanks once again
Thank you and noted bro
Bro, you're awesome. Very clearly explained
Excellent Sir.I really appreciate your effort
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.
Hi Pham,
I understand your confusion but difficult to explain it in comment here.. perhaps will do in a video
@@techTFQ Happy to wait and thank you so much ^^
You are really champ sir
I took your course broah ...I hope I get a lot of insights !!!
Great, see you in the class
yes need materialized view in oracle
Very Helpful, Kindly make separate video for MS SQL materialized View sir. Thanks ahead & more power
Awesome👌 Very well explained🙌🏻
Thank you bro
Thanks for sharing your knowledge, It's great that you provide training, i would like to get started
beautiful explained, fundamentals of MV & how it can be really powerful in analytics use cases :)
Explained Very well,Thanks
Just Wow.. Thank you.
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 😅
Good explanation 😊
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;
Hi Tfq, your explanation about the concept is very good. Could you please upload the separate video for Oracle materialized view and view? Thanks
Thank you and noted bro
Trigger, Cursor,Function,Index,Record,Exception Handling,Package Please do these videos as soon as possible
REQUEST: Please make advanced video odf postgres Regular Expressions.
Your videos & blog are among the best on DBMS topics. 👍
great explaination ....
Thanks Omkar 🙏🏼
Super explanation
Very well explained 👍
Vera level bro
bro did he cover all the concepts in this course??
In my work area we refresh the materialized view twice a day. One in the morning and one in the evening.
Very clear video ... Please show us materialized view in Oracle
Thank you so much Toufig
Your welcome buddy and sure will do
Really awesome 😎😎
Thanks for the explanation...please make video on oracle as well.
your welcome and sure will do
Yes i appreciate it😊
Your videos are really helpful and clear the concepts. Please make a series on pl/sql
Noted Bindiya
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🙏
great
Thank you 🙏🏼
Great video.. Pls consider making video for materialised view in oracle and it's different functionality.. Cheers!!
Thank you and noted bro
@@techTFQ Thanks Sir. Please make video for materialized view in Oracle.
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
thank you and noted on the request bro
Thanks for explaining concepts so clearly and simplifying complexed ones!
Also can you make videos on Group by extensions (rollup, grouping sets.cube)
Thanks, greate video!! So materialized view is a form of denormalization, when we store precalculated data.
viv ))
Thanks for this wonderful video. Please do create video on Oracle materialized view and it's refresh types.
Thanks in advance.
Noted and will do
This video is nice
This was a great video to watch, thank you! Please do make a video on how Oracle materialized views have more functionalities than Postgres :)
Your welcome and sure wil do
Any video for indexes?
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
Hi Toufiq,
Firstly thanks for the video.
Kindly explain the concept of materialized view in Oracle Database as well. Thank you once again..
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 !!!
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
Thank you Nalini and will do that soon
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.
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.
Thanks for making video.....
Please make video for Oracle on both the views in detail
Noted bro
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,
Hello , your videos are really helpful, please do a video on MS SQL materialized views
Noted bro
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?
Hello TFQ - what's the difference between Materialized Views and TEMP tables?
Yes, Please cover materialized view in Oracle.
Noted Kavi
Nice Bro
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?
Great ! It would be good if you could explain the index on view.
Pls post a video about execution plan cardinality optimization
It was really nice explanation. Yaa could you please create video for oracle
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.
thanks
Great explanation. When are you going to conduct live sessions again?
In Qlik we use qvd the same way, and incremental load if need.
Sir, Kindly make a video on Materialized views in Oracle also.! Thanks for explanation!
Sure will do it, thank you
Hi TFQ...what about materialised views in MSSQL...
how the temp table is different from them, I mean it also stores the data for a particular session, and materialized view does the same thing..
Hi pls create videos on indexes and constraints in SQL
Hello sir,
Thank you so much for this .
Can you please make a separate video on the materialized view in Oracle.
🙏🙏
Yes, please make a video on Oracle materialized views..
Could you please explain Oracle MV in detail ?
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.
Noted bro and will do
Hi Thoufiq. May I know if the materialized view applicable to MS SQL and My SQL?
Yes Tony, it is present in both these DB’s
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?
Please make a video on temp table on Oracle
Can you make a video on registered servers and stored procedures creation and execution
I was looking for something like this. I am working on a project with a very big volume of data. I think this would just work like a caching.
please the text editor / IDU u are using sir... or someone else, please tell..!!
Its PostgreSQL database and PgAdmin IDEA
If possible can you please make separate video of oracle views.
Hi I would be interested in Materialized Views in Oracle and which options are at hand there.
Hello, Please make video on oracle database also...
please make a video of materialized view for Oracle. thank you.
Please share a video on materialized view in Oracle.
Microsoft documentations says a materialized view persists the data returned from the view definition query and automatically gets updated as data changes in the underlying tables, not sure the documentation is wrong or not.
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 :)
Hi pls create a materialized view video on Oracle database as well
But why would I create a materialized view rather than create a table? What's the difference? It seems like they do quite similar things.
Thank you, can you kindly make one for Oracle? had problem refreshing mv in Oracle
Hi there!
in this query has not worked in Microsoft sql server Database
insert into random_tab
select 1,rand() from GENERATE_SERIES(START = 1, STOP = 10)
please try to explain on Ms SQL server