Fast refreshable MV with union-all

If you need to have a fast-refreshable join MV with union-all structure(don’t use union, add a second aggregate MV with group by on top if you need union), you need to do the following:

  • ROWIDs in the SELECT list, as for any join MVs.
  • Materialized view logs on detail tables, as for any join MVs.
  • A marker column for union all
  • Make sure each branch of union all is fast-refreshable, check with dbms_mview.explain_mview first.

If you miss the count(*) in the second MV, it will only be fast-refreshable after insert, not for all DML.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: