Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Newbie question re refreshing Materialized Views

From: Adam Cameron <>
Date: Wed, 15 Aug 2007 15:51:45 +0100
Message-ID: <>

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.

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.

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. 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.

I'm fairly certain what I'm seeing is just a vagary of my set up... 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? ;-)

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

Received on Wed Aug 15 2007 - 09:51:45 CDT

Original text of this message