The refresh time is faster 1. You forgot to mention that the fast refresh locks all source tables, and it so complicated In an oltp if a source table is used frequently. Hello Kamel, You right but as mentioned in my blog, my objective was not to describe basic concepts of Oracle Materialized View, I just wanted to show a step step by step procedure used from a real user case to investigate and tune oracle mview.
In my environment, complete refresh was more suited since only my ETL process use this mview so no impact locks on some users. In a OLTP enviroment, indeed, complete must be use with a good knowledge of his architecture. Regards Lazhar. Save my name, email, and website in this browser for the next time I comment. This site uses Akismet to reduce spam. Learn how your comment data is processed.
Infrastructure at your Service. Being given that, here all questions which come to me : My materialized view can be fast-refreshed, so why it takes more than 48 mins to refresh? Conclusion : We have reduced the refresh time from 50mins to 1.
Fast Refresh is not always more faster than Complete Refresh, it depends of the SQL statement loading the view and the number of rows propagated from the base tables to the container tables within the materialized view logs.
To decrease the refresh time, act only on the refresh option Fast, Complete, Index,etc. If you have design problem, never be afraid to modify the SQL statement and even some part of your architecture like here the dependent objects. Of course you have to know very well the impact on your application and on your ETL process.
Before the first usage of the explain procedure, this table must be created with the script utlxmv. With this information, we can recreate the materialized view with the required expressions:. COUNT s. Now there are no more restrictions that prevent a Fast Refresh. Finally, we can repeat our test and see that the materialized view is now updated with a Fast Refresh:. Compared to previous versions of the documentation, the newer versions are easier to understand.
Instead of a list of restrictions, the documentation contains now a good sections with Tips for Refreshing Materialized Views. If you like to read a short and good overview of materialized views with examples of how to use and refresh them, you can find these descriptions in chapter 15 of the book Troubleshooting Oracle Performance, 2nd Edtition of my Trivadis colleague Christian Antognini.
Like Like. You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email. Notify me of new posts via email. Complete Refresh The simplest form to refresh a materialized view is a Complete Refresh. Fast Refresh A more elegant and efficient way to refresh materialized views is a Fast Refresh.
Share this: Twitter LinkedIn Email. Like this: Like Loading Thank you! Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Email required Address never made public. Name required. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email.
Notify me of new posts via email. This site uses Akismet to reduce spam. Learn how your comment data is processed. Share this! Share Twitter Facebook. Like this: Like Loading I think that this problem should be corrected, but it looks to me that it would be best if you or another developer advocate will look after it … In the meantime, anyway, maybe the VARRAY variant could be made to work.
0コメント