HomeНаука и техникаRelated VideosMore From: Kishan Mashru

Materialized Views in Oracle (Part - 2 Practical Implementation with Examples)

105 ratings | 10617 views
This video explains the syntax of creating a materialized view in oracle database, and also a brief demo of creating materialized view, refreshing the MV and dropping the MV is shown in this free video tutorial.
Html code for embedding videos on your blog
Text Comments (25)
Sameer Ahmad (3 months ago)
Hi Kishan, which Oracle Edition you are using......? plz let me know...
Vijaya Diwakar (1 year ago)
Can u tell me how to schedule a job using materialised view
Kishan Mashru (1 year ago)
Scheduling a job using materialized view, humm Why?? We can schedule jobs with dbms_scheduler package. What we can do is, we can refresh the MV using DBMS_MVIEW.REFRESH() option, from a DBMS_SCHEDULERS job. Hope this helps.
Vijaya Diwakar (1 year ago)
Hai kishan can u do videos mv fast,complte and force refresh also plz
Kishan Mashru (1 year ago)
There is not much change in the syntax, for FAST / FORCE / COMPLETE refresh, the actual difference lies in the concept, which I have discussed in https://www.youtube.com/watch?v=7PklhTCNxxw Hope you liked the video :)
bibek sahoo (1 year ago)
very helpfull ,,thanks
Kishan Mashru (1 year ago)
Welcome buddy, hope you have liked the video and subscribed to the channel!!!
naman singh (1 year ago)
Nice explanations but background sound is disturbing. Dear work on clarity
Kishan Mashru (1 year ago)
Thanks man, ya, my new uploads don't have the disturbances. Hope you have liked the video :)
Shashank Vishnoi (1 year ago)
nice explanation... but m confused about what's d use of 'enable query rewrite'?? pls explain in brief
vayunandu (1 year ago)
Very clean explanation. Thank you!
Kishan Mashru (1 year ago)
am gald you enjoyed it, hope you have liked the video and subscribed to our channel to get all the updates!
Jsinghchandel (2 years ago)
Sir, I have a huge SQL query of over 2.5k lines(lots of had case than condition) and i want to create materialized view for that. Requirement is that i need to use the whole output record set to the reporting which is a different tool. Due to large data set my SQL query runs around 1 hr to generate result. Would fast refresh be recommended for this. Can you pls guide.
Jsinghchandel (2 years ago)
thank i am using this to create my view BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE...and it works now. per my understanding whenever there is change in data at the source table end which is used in the Materialised view SQL then it should get updated on its own in the MV table as well..correct me if I am wrong
Kishan Mashru (2 years ago)
Why refresh on commit?? you can use refresh ON DEMAND that would help!!!
Jsinghchandel (2 years ago)
Hi, thanks for the input i did try using the REFRESH FAST ON COMMIT but it is giving me error RA-12054: cannot set the ON COMMIT refresh attribute for the materialized view 12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view" *Cause: The materialized view did not satisfy conditions for refresh at commit time. As I have multiple tables and views used in the query i can not select one column as primary key also...can you help me how to remove this error
Kishan Mashru (2 years ago)
Ya, fast will help you with performance when you refresh the materialized view to fetch the updated record. Wherever possible you should opt for a fast refresh. Hope this helps!!! :)
MANOJ N (2 years ago)
good video... can we schedule a mv to run every 2 hours without creating a job ? if so , how to do it ?
Kishan Mashru (2 years ago)
If you want a fast refresh then you would need a MV log, else MV logs are not required!!!
MANOJ N (2 years ago)
Also, i am able to create mv(Materialized view) without mv logs... is it fine ? or its compulsary to have mv log? apologize for asking too many questions, I am new to this concept...
Kishan Mashru (2 years ago)
Thanks, Manoj!!! I don't think we can schedule a MV refresh without creating a job.
khaled alkhudari (2 years ago)
very nice thank you
Mahmoud Aboueitta (9 months ago)
ي اخى نفسى تشرحه انته بالتفصيل وي ترى موجود ف القناه ولا لا؟؟
khaled alkhudari (2 years ago)
sure thanks
Kishan Mashru (2 years ago)
Thank you bro!!! I am glad that you enjoyed watching it, please do give a "thumbs up" to the video and subscribe our channel. :) :)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.