Re: Newbie question re refreshing Materialized Views

From: Adam Cameron <adam_junk_at_hotmail.com>
Date: Thu, 16 Aug 2007 10:29:07 +0100
Message-ID: <ji59gvtfbbad$.106eovrpp50on$.dlg@40tude.net>

> Google might not help you as much as http://tahiti.oracle.com could.

Cheers for that. This got be on the right track for finding the solution (with the help of Gints posting too).

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

Just because the docs say it doesn't mean it's appropriate (or, perhaps "sensible"). It just means "I should have expected it". Although the docs don't actually seem to bear out what you're saying anyhow (see below).

It makes no sense - well: to me - for the view-refresh process to ever be ANYTHING other than a single atomic operation. Where's the sense in allowing the view to be queried whilst it's in the middle of a refresh?

Breaking the refresh process down into discrete operations means that when one is USING the view (ie: querying it), one must first check to see if a refresh is in process, and THEN query it. Otherwise there's a strong chance of getting false negatives. That's silly.

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

OK. Well let's agree to disagree then.

> Read the above again, and visit
>
> http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#27309

Thanks for the link. I can't find anything there that suggests a complete refresh should not be considered as an atomic operation, though (this para: http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#31691).

However reading the whole thing (well: the bits relating to refreshing and data integrity) kind of suggest that I should NOT be getting these "false negatives" just because a refresh is underway. Well that's my reading, anyhow.

> and read up on the refresh process.

Done. Well I skimmed over it for the time being, but will get back to it all later on when I have more than a few seconds to spare.

> David Fitzjarrell

Cheers David.

-- 
Adam

Received on Thu Aug 16 2007 - 04:29:07 CDT