Skip to Main Content
  • Questions

  • Best approach to do incremental refresh for Materialized Views

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 22, 2011 - 2:51 pm UTC

Last updated: June 23, 2011 - 7:56 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I am dealing with 300 Materialized View which I need to refresh incrementally every day. I am looking for a best and most efficient approach to refresh these MVs. As per my knowledge about MVs, oracle provide two approaches to incrementally refresh MVs i.e. dbms_mview.refresh and dbms_refresh.refresh

I am not sure which approach is better to refresh these many MVs, I think dbms_refresh.refresh refresh all grouped MVs in single transaction. But, I am not sure if that's the best approach to refresh 300 MVs, even though I create multiple groups and execute them in parallel. I have following concerns;

1) Not sure if that would be efficient since all incremental data would be in rollback segment until last MV in a refresh group is successfully refreshed.

2) If somehow the incremental refresh for 50th MV in a refresh group fails then it is going to rollback everything. Not sure if there's any dictionary table which could tell me as to which MV in a refresh group failed, if I am able to find that then I can run another script to do COMPLETE refresh for the erroed MV and then again re-run incremental refresh for erroed refresh group. But, that's again not efficient since refresh group will again do incremental refresh till 50th MV before going ahead and what if it fails again on 80th MV.

All these issues lead me towards dbms_mview.refresh where I can get around all the above mentioned issues i.e. by logically grouping the MVs and executing them in parallel. Also, since every MV refresh will be a separate transaction so it's going to keep only particular MV incremental data in rollback segment and not all.

Please suggest me if you have any better approach, I appreciate you help on this.

and Tom said...

1) that isn't a problem. Just size your undo segment appropriately.

2) that is correct, the goal is to transactionally refresh a set of materialized views - it is all or nothing. The error message would tell you what failed.


If you need to have these objects refresh transactionally (to the same point in time - AT the same time) you need refresh groups.

If you do not need them to be "as of the same point in time", if you do not need them to all be as of the same point at all times - then you do not need refresh groups

That is what will drive this.


again, do not worry about generating undo, just size it correctly.

And you just need to figure out if the materialized views need to be as of the same point in time - or not.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 22, 2011 - 4:29 pm UTC

Thanks for your reply, it helped me a lot in figuring out as to which way to go for incremental refresh. We need point in time data, so I will go with using refresh groups.

But, as mentioned before, is there any way to find out from the dictionary tables as to which MV in the refresh group failed so that I can trigger complete refresh for that particular MV, error message does gives us the erroed MV name but I am looking for some better approach instead of retrieving the MV name from the error text.
Tom Kyte
June 23, 2011 - 7:54 am UTC

you can log it yourself, instead of just calling refresh, call a block of code that catches the exception and logs it. We do not log it.

Incremental refresh on MV with high number fo transactsion.

mdinh, June 22, 2011 - 8:11 pm UTC

I would like your ideas on best method to perform incremental refresh of MV where there are millions of changed records on the base table. I have actually found that it's faster using CTAS versus MV refresh. Is this the best approach? Thank you.
Tom Kyte
June 23, 2011 - 7:56 am UTC

do you want to do an incremental refresh? or not. CTAS is not incremental.


Why don't you refresh more often? Don't wait for millions of records - do it at thousands or hundreds of records.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_MVIEW

More on PL/SQL routine DBMS_MVIEW here