difference between materialized views and tables
Hi,
I got a question regarding the difference between materialized views and tables. As I know, materialized views will store the data with the view, so that next time while running sql against the materialized view, it will not do the query in view again. But is there any differences between materialized views and tables. I can also do the same thing using the table by duplicating the data to the table. Will there be any difference? In that case, I can also safe some time for refreshing the materialized view. Would that be better if the data size is very large and there is frequent insert but not much update?
Thanks in advance for your help.
Cassidy
Software/Hardware used:
Software/Hardware used:
ASKED:
December 20, 2005 7:50 PM
UPDATED:
February 17, 2012 4:32 PM
RELATED QUESTIONS
Answer Wiki:
Hi Cassidy:
Materialized view (MV) is for disconnected computing, the difference between table and MV is table you can do DML operations which will be seen by other users whereas the changes you do to MV will not be available to others until you update your database server.
MV has another advantage when you build MV based on multiple tables using complex queries, the users when using MV the performance increases drastically.
Regards,
Ravindhran R
To see all answers submitted to the Answer Wiki: View Answer History.
Hi,
also you do not need any program to refresh the snap-shot tables (mv) There’re built-in pl/sql packages to refresh these types of tables. You can specify a time interval for refresh, even you can define on-commit snap-shots.
regards
Hi,
Thanks for your reply. Can I say that the materialized view got the advantage for saving time to write the script to reflash the data as Oracle has written a pl/sql script to handle the refresh. Other than that there is not much difference? If I am willing to write those scripts to update the changes on my table. There is not much difference? Will there be any advantages on materialized views other than tables beside the refreshing?
Cassidy
The real benefit to MVs is the query rewrite feature… If you have that feature enabled, it will take queries that COULD be using the materialized view instead of what they wrote – and rewrite them BEFORE they get executed!!!
The other advantage as someone else said is the built in mechanisms to keep it updated rather than writing all that yourself.
I hope this helps.
((MrO))
I agree with Mr. Oracle
Query rewrite is a great advantage of MVs.
If you define a materialized view, the optimizer will decide for each query (even those written before the MV) whether using the MV or the underlying tables is the most effective execution plan.
MVs might not be very useful in transactional systems, which get updated all the time, but are handy in business intelligence environments.
As the name itself says it gives you all the added advantages of views over table, like selective data sharing. You can build a MV based on many base tables without changing your DB arichiecture using joins.
MV are more usefull for precomputed data store for datawarehouse purposes.
but we cant do any changes on mv because dml not allowed on mv…………….