Re: Materialized View Refresh Question

Hi Arthur,

Can't quite follow the details you've posted, the actual MV definition might help me (although watching the Commonwealth Games could be distracting me too much :)

This will be absolutely no help I know but if you can at all, try moving off 8.1.5 and onto a support version of Oracle. Each release seems to make improvements to MV and increase the types of MVs that can be fast refreshed.

On 9i, a nice feature is the DBMS_MVIEW.EXPLAIN_TABLE procedure which documents for you why a particular MV can not be fast refreshed. Note you *can* fast refresh group bys (if you supply necessary fields like count() and the such), unions, sets, m-m sub queries, etc. it's improving all the time.

Absolutely of no help I know but ...

Regards

Richard
"Arthur" <amerar_at_iwc.net> wrote in message news:8b622eae.0207291130.7642a51a_at_posting.google.com...
> Hello All,
>
> We have a materialized view and we are trying to get it to 'fast
> refresh'.
> Right now we are performing a complete refresh each night, which takes
> almost
> 2 hours, then there are the indexes, which take another 2 hours.
> Below is
> a quick picture of the two tables involved.
>
>
> PAYMENT HISTORY joined with AUDIT TRAIL
> --------------- -----------
> PH_KEY (sequence#) PH_KEY (foreign key)
> FieldA FieldA
> FieldB FieldB
> FieldC FieldC
> FieldD (indexed) FieldD
> FieldE FieldE
>
> (PH_KEY = Primary Key) (PH_KEY, FieldA, FieldB = Primary
> Key)
>
>
> There is a one-to-many relationship regarding the PH_KEY from the
> Payment History table and the Audit Trail table. Before these
> tables can be joined, the records in the Audit Trail must be grouped
> by PH_KEY and FIELDA. So, my current materialized view actually looks
> like this:
>
> PAYMENT HISTORY joined with AUDIT TRAIL_VIEW
> --------------- -----------------
> PH_KEY (sequence#) PH_KEY (foreign key)
> FieldA FieldA
> FieldB FieldB
> FieldC FieldC
> FieldD (indexed) FieldD
> FieldE FieldE
>
> (PH_KEY = Primary Key) (PH_KEY, FieldA, FieldB = Primary
> Key)
> (GROUPED BY: PH_KEY, FieldA)
>
> We are running Oracle 8.1.5. And, I believe that you cannot do a
> fast refresh
> using a view as a base table? Also, I'm sure there are other weird
> restrictions in regards to keeping the primary keys in the log table
> and such. I've worked for weeks with no luck, and I thought someone
> might be able to give me an idea.
>
> Thanks for your time.
>
> Arthur
Received on Tue Jul 30 2002 - 02:32:17 CDT