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: DA Morgan <>
Date: Wed, 15 Aug 2007 08:30:22 -0700
Message-ID: <>

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

It would help to have the following:

  1. Oracle version number to 4 decimal places
  2. The DDL used to create the MV
  3. An explain plan taken both before and after the change
    • you do run regular StatsPacks don't you?
  4. Any indication of what has changed: Something has. -- Daniel A. Morgan University of Washington (replace x with u to respond) Puget Sound Oracle Users Group
Received on Wed Aug 15 2007 - 10:30:22 CDT

Original text of this message