2

How can I update a materialized view? Is there any downside to updating materialized views? I'm in a situation where I can either

  1. Update the materialized view (OR)
  2. Copy the records to another table, update them, truncate or drop the materialized view table, insert the updated records back into the materialized view.

These two options revolve around the long amount of time required to rebuild the materialized view (literally 5+ days).

Version : Oracle 10g

Vikdor
  • 23,934
  • 10
  • 61
  • 84
contactmatt
  • 18,116
  • 40
  • 128
  • 186
  • What exactly do you mean with "UPDATE"? You cannot run an SQL `UPDATE` against a materialized view as far as I know –  May 23 '11 at 13:59
  • What went wrong with the suggestion I made in your other question on this topic: http://stackoverflow.com/questions/6046502/oracle-materialized-view-alter-structure-so-slow/6047579#6047579 – DCookie May 23 '11 at 14:54
  • @DCookie - My ignorance lol. I thought I'd have issues updating the MV table even after I dropped the MV "container", but I just tested it and I have no issues updating the table after I drop and preserve the associated table. – contactmatt May 23 '11 at 15:12
  • Correct... the MV "reverts" to normal table status when preserved. You really ought to investigate why that query is so slow, however. It seems excessive to run for days. – DCookie May 23 '11 at 17:53

1 Answers1

2

The intention of a materialized view is to store the results of some complex long running query that the query rewrite mechanism can use to save lots of time. It looks like the sql that is used to build the MV needs some tweeking.

You cannot update an MV, unless you meant doing a full/fast refresh/rebuild.

What is eating the time in during the MV refresh? Did you check the addm reports? Did you configure full or fast refreshes?

  • It's set to REFRESH FORCE ON DEMAND. I'm not 100% certain, but I believe that our client refreshes it periodically via a Oracle job – contactmatt May 23 '11 at 14:29