difference between materialized views and tables

pts.
Tags:
Oracle
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Enkidu
    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
    0 pointsBadges:
    report
  • Cassidy6o6
    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
    0 pointsBadges:
    report
  • MrOracle
    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))
    0 pointsBadges:
    report
  • Pelaez
    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.
    0 pointsBadges:
    report
  • MohanKumar
    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.
    0 pointsBadges:
    report
  • Ammalu
    but we cant do any changes on mv because dml not allowed on mv................
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following