RE: dba_mview_keys
- From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Wed, 1 Sep 2010 12:22:32 -0500
The primary reason I am re-writing this is because in order for the entire
hierarchy of materialized views to be refreshed accurately they have to be
compiled. This is due to a bug which I don't have time to look up right now
because I'm on my way to a meeting. I've never been able to figure out why
this is necessary other than noticing that mv's show either 'INVALID' or
'NEEDS_COMPILE' and I can't explain it looking through dba_dependencies and
Oracle can't explain it either. To get around this while waiting for a one-off
patch, we were manually compiling the materialized views prior to refresh.
This caused problems because the compilation of the mv's causes a dependent
package to become invalid which bubbles up to a cold fusion app that generates
an invalid package state error because the location of the parsed package
changes when recompiled. Once we got the one-off patch and applied that, we
discovered that the patch was just compiling the materialized views anyways so
we still had a problem, even though we had good data in our materialized views.
Ugh.
The secondary reason is that these views are incapable of fast refresh. I
believe the use of analytics precludes fast refresh but the reason given by
dbms_mview.explain_mview is 'view or subquery in from list'. We end up wasting
a colossal amount of resources with complete refreshes. There are only 4 base
tables involved and writing the fast refresh logic to maintain our own summary
tables won't be hard. I plan to use materialized view logs to track the rows
that change and then just maintain a table of parent-child relationships for
the hierarchy of views to determine which summary tables need to be maintained.
I was thinking about adding columns to the mview_logs that correspond to what
is selected by the materialized views. That would obviously have efficiency
benefits. I was thinking I could make use of the data dictionary but
apparently not. whether or not that is because of my lack of understanding of
the documentation or a bug or some other reason is my original question though.
Well...this turned out way more verbose that I expected. :)
I have to run so apologies for typos and this not necessarily making perfect
sense.
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Wednesday, September 01, 2010 11:27 AM
To: Stephens, Chris
Cc: ORACLE-L
Subject: Re: dba_mview_keys
On Wed, Sep 1, 2010 at 7:28 AM, Stephens, Chris
<Chris.Stephens@xxxxxxx<mailto:Chris.Stephens@xxxxxxx>> wrote:
10.2.0.5 on Linux 64
I'm trying to write my own fast refresh process due to some limitations on fast
refresh by oracle.
That's quite a bit to take on IMO.
What are the limitations you have encountered that are the problem?
There may be a better way to deal with it than re-inventing the wheel. :)
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential and
exempt from disclosure under applicable law. If the reader of this message is
not the intended recipient or the employee or agent responsible for delivering
this message to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, please notify us
immediately by email reply.
Other related posts:
