Oracle Interview Question - oracle difference between view and materialized view

Oracle Interview Question - Difference between view and materialized view?
List of all the SQL and PLSQL interview questions @ sivakacademy.blogspot.com/p/sql-plsql-interview-questions.html

[@user-up3wh3nl6q]

very helpful, explained in a very simple way, thanks for the amazing content.

[@AbhishekTiwari-ol6gv]

your videos are so much helping brother

[@vishalmoradia4888]

Best explanation I found on internet. Thank You.

[@SivaAcademy]

Welcome 💐💐🙏👍

[@gsraj511]

Very Informative and Clear Explanation... Thank you so much Siva.. Keep up the Good work !!!

[@SivaAcademy]

Thank you very much bro

[@tusharshreshth]

Thank you very much for sharing your knowledge...If am not wrong need below correction. For point no 2. in comparison of View and MV @9.55. For view, online data from underlying base tables and for MV it should be Offline/delayed data from MV instead of underlying base tables as last refresh data is returned...

[@anilk3233]

Thank you for your "Crystal Clear Explanation" Siva. Appreciate it. Thanks again and keep up the good work.

[@SivaAcademy]

My pleasure!

[@AtulKumar-tw3gm]

Hi sir, I watch your all sql videos and it is very helpful for me. Thanks a lot for these videos. Can you please make a video on how we can refresh materialized view or a detailed video on materialized view.

[@rahulsingh-sw3yt]

Well explained.... All topics....really appreciated for such type of help....

[@SivaAcademy]

@rahul, welcome bro 🙏

[@abhigoswami6668]

Excellent Explanation...very very good work siva..keep it up

[@SivaAcademy]

My pleasure, thank you

[@jksharma7]

Siva Sireeee....... You explained it very well. Tahnks.

[@SivaAcademy]

Welcome

[@devilsgaming5915]

Thanks for the video ,i have got clarity on these concepts very well

[@SivaAcademy]

Welcome 🙏, its my pleasure 🙂

[@vickypatekar9532]

Awesome Explaination Sir Thanks alot 👌

[@SivaAcademy]

@vicky, thanks for your comments bro 🙏

[@dnyaneshchavan7227]

You are rock man...good explain in very simple language and example

[@SivaAcademy]

My pleasure bro

[@kumargaurav4961]

Hi Shiva , your videos are great and very informative ... just wanted to know if we can create view with unique column or like unique sequence id ?

[@newpatron7054]

Best explaination till date.

[@SivaAcademy]

Thank you 💐

[@Ram.k93]

Nice explanation. Few points to add- 1. While creating Mviews, internal tables and index are created in same schema along with mview. 2. We can have partitions and indexes for mviews.

[@bhushanpalse516]

Excellent teaching of Concept sir,. Keep it on, best wishes

[@SivaAcademy]

Welcome bro

[@tusharsonwane8468]

Superb superb explanation sir thank you for such great information 🙏

[@SivaAcademy]

Welcome bro

[@aniketjagdale7765]

Hello sir it was a very good explanation but please make a detailed video on materialized view as you have make on index and view.

[@anshumanmishra571]

Awesome explanation sir and its very informative

[@SivaAcademy]

Welcome 🙏👍

[@maboodahmad7289]

Thank so much sir, cristal clear explanation.. ❤️

[@SivaAcademy]

Thank you

[@ramanhasan9398]

Thank you very much for the great content! Could tell me where could I find the Video related to last part of the video? (about the question: in which scenarios should we use wich concept)

[@mateen161]

Nice explanation. Thank you!

[@SivaAcademy]

Welcome

[@shaikrasheed2861]

Excellent sir,we need more updates

[@SivaAcademy]

🙏 thanks, please stay tuned for more videos

[@hridayborah9750]

It's in external level , modification done on base table will not affect the views of the end user

[@sidhantpasricha5356]

Very well explained..Thanks

[@SivaAcademy]

Welcome

[@sachinjadhav3566]

On performance basic is mv is used if there is frequent dml operation?

[@purnachandrajena7948]

Excellent... Kindly Upload the the concept of performance and tunning

[@SivaAcademy]

Thanks for your comment. Working on performance tuning series of videos, will upload once done.

[@pradipsaudar4077]

Thank for clarification ... Bless you

[@SivaAcademy]

Welcome bro

[@funmastisagar]

Can we create index on view or Mview and can we perform DML on Mview ??

[@deepshikhakhatri3338]

my doubts are all clear, thanks

[@SivaAcademy]

You are welcome

[@lehuuphuc0909]

Thanks for your explain...

[@SivaAcademy]

Welcome 💐🙏

[@arijitroy7253]

If materialised views have their separate physical storage then why shld v not create a subset of the main table, why a materialised view only ! If u may answer pls .

[@Rathod-P_Raj]

ora - 12015 cannot create a fast refresh materialized view from a complex query Cause: Neither ROWIDs and nor Primary key constraints are supported for complex queries..Plz make one video on this topic

[@asishkumar4376]

Clear Explanation. sir

[@SivaAcademy]

Thank you

[@shankark7584]

thank u bro clean explanation

[@SivaAcademy]

Welcome bro

[@tej1316]

Appreciate your hard work! Well as per my experience : If you practice this playlist you are good to clear any service based firm for PLSQL role ! can't we use CREATE OR REPLACE MATERIALIZED VIEW mv_name ; syntax ?😃

[@pravingaddam8541]

Hi Sir , nice explanation. I have one query, currently I'm creating one Mview (base on 2-3 table) monthly on first date but while using that Mview, in next day some of the packages (base on Mview) going in invalid objects status . So kindly clarify why this packages going in invalid objects status and how to avoid it ? Thanks in advance

[@user-wu7dq6fc1i]

Hi shiva, which sceneries view and Mview will use, please make the video

[@suchismitamishra2248]

Quite informative

[@SivaAcademy]

Thank you

[@KarthikRampur-ql2td]

where exactly M-View gets stored ? Does it store as table data/file ??? Also can we update materialized view ?

[@SanjayKumar-ng7zp]

nice presentation

[@SivaAcademy]

Thank you

[@namratanikam7566]

Great👍🤟

[@SivaAcademy]

Thank you

[@bharatbagul9292]

Good One...please make videos on performance tunning... if particular doesn't give the performance what we have to check...or what is basic methods of tunning the query

[@SivaAcademy]

sure, please stay tuned

[@rahulbadhe5930]

nice sir .. 1) what is dynamic sql?explain in details. 2) what is ref-cursor?expain in details. this above questions plz make a videos sir ...interview point of plz...

[@SivaAcademy]

@Rahul, Thanks for your comment, Sure, I will post a video on dynamic SQL, and ref cursor soon, Pls stay tuned... Thanks

[@sudarsanrout5787]

👍👍👍👍

[@ManojKumar-il8vt]

Superb

[@SivaAcademy]

Thanks Manoj

[@BewithuNnk]

Please upload this video with an example? Views are created on top of materialized views ? And How?

[@aashokprusty]

When will we go for a view and when will go for Materialized view

[@Pras2winma]

Thank u

[@SivaAcademy]

Welcome

[@kameshrajdevaraj]

Hi Shiva, your way of teaching is quite interesting .have an question on this part whether we able to do any modification in created M-view without dropping.

[@SivaAcademy]

Thank you bro, I guess no, however I will check and post back with detailed information

[@slyroy7562]

When we perform an DML Statements in view table will it be effect on base table or not??

[@SivaAcademy]

Yes, please check this video kzbin.info/www/bejne/i4CQlX2neNadpZo

[@geetika.shrivastava]

Can u give some scenarios- when views should be used and when materialized view should be used ?

[@kuntalbanerjee6903]

Views generally used in database application where number of data are less like daily transaction and number of table that need to join to retrieve data are less then we use view.But we use materialized views where the query is complex and in the mat view many tables need to join and time consuming.In this scenario we use materialized view.In normal OLTP application we use views but for data warehouse application and business intelligence application where we generate reports based on some complex query and join we use materialized views.

[@krishnaadabala169]

​@@kuntalbanerjee6903thnkyou ❤

[@hiralalpawar1506]

very nice sir

[@SivaAcademy]

Thank you

[@SanjayKumar-ng7zp]

Please create video on types of refresh in materialized view and more advance features in mv

[@SivaAcademy]

Sure please stay tuned

[@suryamadank3626]

Can we do DML om MV?

[@ManojKumar-il8vt]

Sir pls share some more interview questions asked vedios like triggers concept & indexing part

[@SivaAcademy]

Sure Manoj, Already posted on trigger concept & types., Next video i will cover DML trigger types and its concepts. send if you have any specific question on DML trigger, i will answer as part of that video.

[@vskroyal4565]

Can u make video on below question

[@gauravdutta5486]

Hello Sir , just a question Suppose we have a materialized view which we refresh periodically ! and then we just drop the base table of that M_VIEW ! What will happen when M_VIEW will e refreshed the next time ? I believe we will get some kind of error like source table do not exist

[@SivaAcademy]

Yes we will get error

[@mahammadshoyab9717]

Good video bro,

[@SivaAcademy]

👍👍

[@mahammadshoyab9717]

@@SivaAcademy hi siva, how to find no of weeks for a month in sql

[@mahammadshoyab9717]

Also,how to change snowflake schema to star schema.

[@sushmasakore2562]

How to add columns in view

[@priyankadesai9848]

Hi please post separate video on materlized view.... Refresh methods.

[@SivaAcademy]

Sure, please stay tuned

[@surendrabisht2523]

👌

[@SivaAcademy]

🙏💐

[@ManishaKumari-jb9xg]

Sir, Could you please answer this question- why we are going for a Materialized? I mean as view doesn't store any data so there will not be any space issue but Materialized view stores data in a physical memory location so it will take space then also use Materialized view why?

[@kuntalbanerjee6903]

There are many application where space is not the primary concern but the query time is more important like data warehouse and BI application where we use materialized views.

[@nitu.mishra.549]

Hello sir... Can we perform dml statement in base table through view??

[@SivaAcademy]

Yes, for simple view, its possible, for complex its not possible, please look into this video kzbin.info/www/bejne/i4CQlX2neNadpZo

[@anjalishende662]

When to use view and materialized view ?

[@sabariraj-sh7hg]

Ji enable query write ku , seperate video poduga ji,, it will helpful for us

[@SivaAcademy]

Sure Sabari, can you please eloborate little bit more ji

[@learntolive5001]

what is the purpose of creating materialized view??

[@rahulsingh-sw3yt]

Plz provide some more topic on performance tunning

[@SivaAcademy]

@rahul, sure will be posting, please stay tuned

[@pankajdubey8197]

Materialize view can be created on different schemes/database..... can we do the same view also ?

[@SivaAcademy]

Yes.. Using db links

[@ashokkumar-hm5on]

create or replace materialized view vw_emp as select * from employees where department_id=50; not working in sql devoloper

[@Crazy-Trainings]

Don't use Replace ,it will work.

[@reddysrkhar]

Explain performance tuning concept deep explain video upload

[@SivaAcademy]

Sure,I Will do.

[@utubemovies1]

Very informative video. Most of the videos voice is low. Hope it will be taken care

[@SivaAcademy]

Hopefully recent videos are better

[@crazycom9807]

Hi Sir, Please make tutorial on MV... refresh types etc.,

[@SivaAcademy]

Yes, please stay tuned

[@arunrocketz]

what is the default refresh method in Mv?

[@Crazy-Trainings]

Force Refresh

[@ur8946]

Can we fire DML statement on Materialized view ?

[@SivaAcademy]

@ur, DML operation is not possible on read-only materialized views, however, you can do DML on updatable materialized view.

[@Murthy3333]

Hi Siva, Could you please confirm how many Mviews are there.Could you please make a video on How to create types of Materialized view 1. READ ONLY 2. WRITABLE3. UPDATABLE Note: particularly How to create Updatable Mview and give us an example of how to update the base table through Updatable Mview. Thanks in Advance...

[@SivaAcademy]

Sure, definitely, please stay tuned, will cover in upcoming videos

[@bhargavasrirambhatla4002]

Hi sir Can you please explain what is pass by value and pass by reference, thanks in advance

[@SivaAcademy]

Sure bro, please stay tuned, will post soon

[@bhargavasrirambhatla4002]

@@SivaAcademydo we get any message or how do we know that u have updated videos

[@SivaAcademy]

@bhargava, click the bell icon next to subscribe button, to enable the notification, after that for any new video uploaded you will get a notification

[@Mini-vv1gz]

you videos are good but volume is very low , we have to keep 100 then ony can hear you properly .

[@SivaAcademy]

Thank you, I believe recent videos volume is good?

[@vskroyal4565]

Sir can u make difference between group by and partition by????

[@SivaAcademy]

Sure bro, please stay tuned

[@ManojKumar-il8vt]

Mainly index types & use

[@SivaAcademy]

Sure Manoj.

[@SivaAcademy]

Started posting index related questions, Please check and share your views

[@user-wi5ui4me5q]

Pls improve sound quality

[@suraj454]

Please make video on types of Materialised view in detail???

[@SivaAcademy]

Sure Suraj, please stay tuned 👍

[@pallavideb9300]

please make videos on types of materialised view and refresh methods in detail..

[@SivaAcademy]

Sure please stay tuned 👍

[@mhalappatengale8980]

We can do same things using table then why need materialized views ?

[@SivaAcademy]

Mviews provide additional advantages over table, like refresh etc.

[@mhalappatengale8980]

@@SivaAcademy thank u so much

[@bharathipandian45]

What is the difference between view and synonyms?

[@SivaAcademy]

Ideally we should not compare both, view is just a named query, that executes and returns value, whereas synonyms are alternative name for any database objects

[@ajinkyagawade7176]

Video ok but voice very low

[@ssss-uy6jh]

It's very nice teaching sir...can u please explain about joins and subquery

[@SivaAcademy]

@Madhavi, Thanks for your comment, Subquery I have already covered as part of interview question series, Links given below for your easy reference. Joins I will cover in upcoming videos, please stay tuned. kzbin.info/www/bejne/j53QY5ltqbh0q5I kzbin.info/www/bejne/nKfNdKCil9WWj9k kzbin.info/www/bejne/mIG2g36nYt-dibM kzbin.info/www/bejne/haPCm5-NmJKqbqc Regards, Siva

[@ssss-uy6jh]

Thank u sir

[@kaushikkhanra941]

why do we need materialized view ?

[@Moodfood145]

For fat access the data . data will stay in cache memory after execution of query

[@ru2979]

82k view 😂