Fast refresh of aggregate-only materialized views – introduction

The same scripts investigates also disabling it by setting the undocumented parameter “_mav_refresh_opt”=32, but as always, ask Oracle Support first (also because there’s no official note explaining how it works on MOS, and I haven’t used it in production yet – since I actually discovered it about one week ago while preparing this post).

In the next post, we will examine the SUM scenario.

Page 3 of 3 | Previous page

4 comments on this post.
  1. Alberto Dell’Era’s Oracle blog » Fast refresh of aggregate-only materialized views with SUM – algorithm:

    [...] the general introduction to aggregate-only MVs we have seen how the refresh engine first marks the log rows, then inspects TMPDLT (loading its [...]

  2. Alberto Dell’Era’s Oracle blog » Fast refresh of aggregate-only materialized views with MAX – algorithm:

    [...] the general introduction to aggregate-only MVs we have seen how the refresh engine first marks the log rows, then inspects TMPDLT (loading its [...]

  3. Sandeep:

    Hi,

    I was looking for a description of the columns of the Materialized View Log tables and found your site. Thanks for the nice description.

    I have a scenerio and need your thoughts on it. We are currently using Oracle Apps EBS, and doing a migration to a higher version. We have only the MLOG$ table created on a base PO table and have trigger on the MLOG$ table to get approved PO records out of the oracle apps system. Do you see any disadvantage of using triggers on the MLOG$ tables? Is there any performance hit? We have not seen any performance issues by and large till now but wanted to make sure we are doing it right.

    Thanks,
    Sandeep

  4. Alberto Dell'Era:

    Sandeep,

    I don’t think that having a trigger on the mlog table is neither supported nor a good thing to do … those tables are “private” and we are not supposed to do anything on them, besides (sometimes) indexing them or perform (even more rarely) some maintenance operation such as coalescing.

    Especially, the issue is that the algorithm Oracle uses to populate the mlog table might change, and that would break your trigger. By the way, do you know that records inserted by insert-append are not replicated to the log ?

    I would suggest to rewrite the trigger on the base table; probably you would get similar performance, since the mlog is populated, and hence your current trigger fires, for every row modified in the base table (besides insert-append, of course).

Leave a comment