Materialized View In Oracle - Part 3

This video explains the different modes using which you can refresh your materialize views and the different scenarios in which you should opt for these strategies.
#MaterializedView #ViewvsMaterializedview #Techcoach

[@akhiln1370]

I have watched many videos on mv your explaination makes me to remember the basis for a longer time. Thanks for sharing knowledge

[@TechCoach]

Thanks Akhil for the kind words I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@sureshnamala6776]

Great explanation on this series. Thanks a lot!!

[@fityko]

I feel back in college, Great explanation!!! I'm blown away by these materialized views Thanks for sharing your knowledge.

[@TechCoach]

Thanks oldroof for the kind words, I have a small request. I am working on this new KZbin channel.I would really appreciate if you watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/ZqSvhmuPlr6latk

[@girishpandey9758]

Complete refresh is happening and somebody tries to access mview what will happen - You havent discussed this point -> Data will be fetched from redo logs?

[@ruksraman50]

Vivek, Thanks a lot for detailed explanation. It is very useful during my interview preparation. I had a interview question on MV. 5 MV's are created on 5 different servers (all the MV query are same) and base tables on different server. All the 5 MV's are scheduled for FAST refresh by midnight. If one of the MV's is not available, then how the 'missed' MV will get refreshed? In FAST refresh, once the data is refreshed the MV log file will be cleared. In this case, since one of the MV is not updated yet, how the oracle will handle it? Another question is: does the refresh method uses pull or push mechanism? Can you help me on these questions?

[@ranajparida5412]

Hi Vivek, If you want to really educate the people like me who are really interested then please 1 humble request. Please create all videos for the specific topic and upload it finally by which we will get the complete information. I saw may cases that you posts are very useful and nice but it is incomplete. For example the post on oracle analytic function, materialize view which you want to describe DML operation on materialize view. Please upload the post at a time..it might take time but finally when it come to us it would work like a package of complete information. Regards, Ranaj

[@TechCoach]

Thanks Ranaj.I will keep it in mind.

[@09sitaram]

Thank u for such valuable videos. Could u please create a video specially on Query Rewrite. On what cases the query rewrite works and in which cases it does not work? Please explain with example. Request you to pause while explaining.

[@TechCoach]

Thanks for such valuable feedback I will try to improve my video delivery style. Sure I will try to explain query rewrite a little more in my next video. I have a small request I am working on this new youtube channel, Please help me build by watching and subscribing to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@rajnichahar6951]

While complete Refresh the materialized view create exclusive locks on the Mview which is being truncated and inserted ? Creating lot many Mviews does not affect the memory space usage since it is a physically stored DB object?

[@SalluBhai85]

Can we create index on materialize view?? And please share your video of dml operation on materialize view as well

[@TechCoach]

SalluBhai85 Yes you can create Index on materialize view. I shall post it soon.

[@funwithus8160]

Very nice explanation every time in your video.

[@TechCoach]

Thanks a lot Shashi for the kind words :)

[@grvmavi]

Thank you .. Well Explained 👍

[@shaiknazeerbasha3901]

Can we alter the MV from on Demand to on commit refresh?

[@pradiptidubey1656]

NIce explainations for all the videos, proved to be quiet helpful (Y)

[@TechCoach]

Thanks a lot Pradipti for the kind words, I am glad I could help.

[@skrashidali]

Is query rewrite supported in FAST refresh?

[@priyankapriyadarsini7840]

Can we do update or any dml operations in MV?

[@TechCoach]

Hi Priyanka, The short answer is No. You can use "for update " clause to update the materialized view but the changes won't be done in the base table. Which means as soon as the base table and materialized view are refreshed again the changes done via updates will be lost. I hope this helps. Priyanka, I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. kzbin.info/www/bejne/fZ-5n2ajhaeFsJY

[@mayuriwankhade574]

Really helpful . I have view with nested view in it which are non materialized so would it be advisable to make all view materialized or only which are giving poor performance should be replaced ? Can we used non materialized view inside materialized view please reply .

[@TechCoach]

If the performance is poor and the view/data is used/queried frequently you can consider using MV. View inside MV should work but I have never tried it personally.

[@mayuriwankhade574]

@@TechCoach thanks for quick reply , one more query in posgrey I don't see any option to refresh on commit automatically do u have any idea on that . What I got is we need to call scheduler to refresh materialized view . Please advice.

[@TechCoach]

@@mayuriwankhade574 I am not sure about Postgre Mayuri, may be they don't have this functinality and we will have to schedule it

[@mayuriwankhade574]

@@TechCoach thanks keep up the good work

[@TechCoach]

@@mayuriwankhade574 thanks :) Please consider subscribing to my other channel as well it will really help me kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@celinehm9630]

Thanks Can you tell us how to refrech the data warehouse then with these materialised views please?

[@TechCoach]

You should use a mature ETL process instead of using MV entirely for your datawarehouse.

[@celinehm9630]

@@TechCoach thank un fort your response😍 i have a little homework fort tommorow and I need you to tell me just how In our case our teacher dont use ETL but ans SQL batch Can i send you à mail please ?

[@codewithovi997]

Outstanding explanation

[@TechCoach]

Thanks a lot buddy for the kind words :)

[@suchshas7594]

Great video, as mentioned at the end of the video, Is there a part 4 video for DML operations on Materialized views? I don't see it in the channel.

[@udayabanunagarajan5401]

Great explanation!

[@rahulgiri5645]

Please add next video for materialized view

[@SudhirBhajnawale]

Nicely explained ... Thanks a lot

[@TechCoach]

Thanks Sudhir for the kind words :) , You made my day :)

[@Lime_A]

thanks that was very helpful and easy to understand

[@TechCoach]

Thanks a lot for the kind words. I need a small help I am working on this new youtube channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@KrishnaPaneri]

Explained very well! 👌🏿

[@TechCoach]

Thanks a lot Krishna for the kind words :)

[@chasemarler5568]

Great video - thanks!

[@TechCoach]

Thanks Chase Marler for the kind words, I have a small request. I am working on this new KZbin channel.I would really appreciate if you watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/ZqSvhmuPlr6latk

[@psaikishore3210]

waiting for next video bayya .......

[@TechCoach]

Thanks for the kind words Sai.I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/h6PTgZZjjLOdpbc

[@kasieswarimedicharla2656]

Do we need to commit in case of fast refresh?

[@syedamunawarfatima6989]

IF U PROVIDE THE OPTION OF COMMIT IT WILL GET INCREMENTALLY REFRESHED WHEN THERE IS A COMMIT ON OTHER SIDE ..OR U CAN PROVIDE THE OPTION OF ON DEMAND INCREMENTAL REFRESH..

[@uditech7736]

Clearly explained.

[@TechCoach]

Thanks a lot for the kind words :)

[@mallikarjunam3239]

Thank you so much for your information

[@TechCoach]

Thanks Mallikarjuna for the kind words, I have a small request. I am working on this new KZbin channel.I would really appreciate if you watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/ZqSvhmuPlr6latk

[@shourabhpayal1198]

Great video

[@TechCoach]

Thanks buddy for the kind words :)

[@shourabhpayal1198]

@@TechCoach I am using 3-4 tables to make the materialized view and setting refresh strategy as COMPLETE on commit but it shows an error as ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view Note : I have not included any primary key column in my view definition like id from parent table. Can you please advice what can be the probable cause ? Thanks

[@anuswadh]

Thanks for this video. Please give your patreon or create a community.

[@TechCoach]

Thanks buddy for the kind words, i din't understand the 2nd part.

[@anuswadh]

www.patreon.com or buymeacoffee.com. I was suggesting adding something like these in your about info so that you can generate some revenue :).

[@TechCoach]

@@anuswadh thanks a lot for the valuable suggestion, I will consider adding it :)

[@aruljoannes6490]

not clear

[@TechCoach]

I am sorry I would try to do better next time.

[@aruljoannes6490]

Thank you!..All the very Best@@TechCoach

[@syedamunawarfatima6989]

LOVE TO LISTEN U WITHOUT ANY BREAK....

[@TechCoach]

Thanks Syeda for the kind words,I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. Please share with ur friends also. kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@SatyendraSingh-ul5iq]

Don’t say -please subscribe my channel, it sounds very cheap. You r a talented guy then y begging 🥺 for subscription.

[@TechCoach]

Thanks for the advice Satyedra :)