Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie question re refreshing Materialized Views

Re: Newbie question re refreshing Materialized Views

From: <>
Date: Wed, 15 Aug 2007 08:28:25 -0700
Message-ID: <>

Comments embedded.
On Aug 15, 9:51 am, Adam Cameron <> wrote:
> Hi
> Sorry if this is a dumb question. I HAVE spent a fair while googling about
> the place trying to work this out for myself, but have drawn a blank. I
> will continue with Google after submitting this.

Google might not help you as much as could.

> We have some sort of glitch on our Oracle box at present which is making it
> run somewhat slowly, which has revealed some unexepcted behaviour to me.
> We have a materlialized view which we need to do a full refresh of, every
> now and then. When the box is running fine, this process takes a few
> milliseconds: no problem there.
> Currently it is taking about a minute. This in itself is not a problem
> (well: it is, but it's someone ELSE'S problem, and they're on the case).
> The problem is that if a SELECT query is performed on that MV whilst it's
> being refreshed, a zero-row record set is returned. This, I guess, is
> because DURING the refresh process, the current data gets blitzed first,
> then the new data is recreated. And the SELECT query is hitting the view
> between those two steps, whilst there is no data. And I'm only seeing this
> now because the refresh is taking so long.
> This doesn't seem an appropriate behaviour to me.

Why doesn't it seem 'appropriate'? According to the Oracle documentation:

"To perform a complete refresh of a materialized view, the server that manages the materialized view executes the materialized view's defining query, which essentially recreates the materialized view."

It appears you're correct in your assessment that the original data gets 'blitzed'. And, according to the docs that *is* appropriate behaviour for a complete refresh.

> I would have thought there'd be some sort of view-level lock on the view
> whilst it's refreshing, so that anything trying to query it will WAIT until
> the refresh is finished, and then return sensible results.

'Sensible' to whom? The materialized view is being completely rebuilt so a 'no data found' result seems sensible to me.

> OR (and maybe
> preferably) the new data would be generated in the background, and once
> that's done, it would replace the "real" view... my theory being the data
> generation takes the time, but the swap out would be very quick. During
> the data creation, any SELECTs would query the "stale" data.

See above.

> I'm fairly certain what I'm seeing is just a vagary of my set up...

No, it's the way a complete materialized view refresh is designed.

> all my
> Oracle knowledge comes out of a book that I bought (well: three books), and
> google searches. Also it's only a small part of my "day job" (which is a
> software developer), so I make no claims of any expertise or that I know
> what I'm doing. An interesting but dangerous position to be in!
> Does anyone know what I'm doing wrong, or what I need to tweak, or what I
> need to read to work out where I'm mucking something up? Indeed does
> anyone know what I'm even banging on about? ;-)

Read the above again, and visit

and read up on the refresh process.

> I appreciate the efforts of anyone who takes time to give this some thought
> and nudge me in the right direction!
> --
> Adam

David Fitzjarrell Received on Wed Aug 15 2007 - 10:28:25 CDT

Original text of this message