Oracle Scratchpad

February 16, 2022

Materialized Views catalogue

Filed under: Oracle — Jonathan Lewis @ 6:36 pm GMT Feb 16,2022

This is a list of articles I’ve written about materialized views. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

You may also find some useful ideas in the list of MOS articles (needs an account) in Doc ID 1353040.1: Primary Note for Materialized View (MVIEW) though the usual “trust” warning applies – it has a very recent “last update” date but some of the notes referenced are very old.

  • MVs and Hacking 3rd parties (Dec 2021) – a very old article (10g) very recently published presenting an idea of working around 3rd party performance issues
  • Oracle’ aid to query rewrite (Aug 2020) – an introduction to the explain_mview() and explain_rewrite() procedures that help you discover why a matieralized view isn’t doing what you hoped for.
  • Drop unused columns bug (Jan 2020) – fixed in 21.1, patches needed prior to 19.10. This bug may affect you if you have materialized view logs on tables where you’ve marked columns unused then drop them.
  • Nested MVs (Jan 2018) – when written this demonstrated a strange self-deadlock error with nested MVs in 12.2. The deadlock no longer appears on my 12.2 system, but the note is a nice story about how to set up nested MVs anyway.
  • A Distributed Query Trap (Nov 2016/Sept 2021) – Materialized Views came into existence as “snapshots” pulling data from remote databases; when you do this you need to remember that some things can turn a remote query into a distributed query.
  • Union All MV (July 2016) – an example of how to create a materialized view of a UNION ALL view.
  • 12c Out of place refresh (March 2015) – notes on the mechanism introduced in 12.1
  • A bigfile bug for MVs (April 2014) – up to 11.2.0.4 a materialized view log “with rowid” of a table in a bigfile tablespace would cause a crash on refresh. Fixed in 12.2 with a backport to 12.1
  • rman duplicates and MVs (March 2014) – a surprising requirement for “skip tablespaces” (that may have been removed by now).
  • Out of place MV refresh – 11g style (Sept 2013) – Meeting the client’s requirements for refreshing an MV before 12c “out of place” refresh existed.
  • MVs that crash dbms_stats (Aug 2013) – and are still doing it even in 19.11.0.0 and 21.3.0.0

1 Comment »

  1. […] Materialized View catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — February 16, 2022 @ 6:36 pm GMT Feb 16,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: