Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Materialized View Refresh Question

Re: Materialized View Refresh Question

From: Arthur <amerar_at_iwc.net>
Date: 30 Jul 2002 11:02:32 -0700
Message-ID: <8b622eae.0207301002.1b6b9b8f@posting.google.com>


Howdy,

Thanks for the reply. I'm thinking about your suggestions. Here is the concept, in simplest terms.

The tables have a one-to-many relationship using the PH_KEY field. So, in order to get the proper totals, it is necessary to SUM the fields in the AUDIT_TRAIL table so I can have a one-to-one relationship. I think that is where my biggest problem is......

I'll have to think about your suggestions, as I do not know a lot about IOT's and such.....

Arthur

roobaron <member_at_dbforums.com> wrote in message news:<3d4631d7$1_at_news.usenetgateway.com>...
> Hi Arthur,
>
> This is from the doco
>
> "If a materialized view contains joins and aggregates, then it cannot be
> fast refreshed using a materialized view log. Therefore, for a fast
> refresh to be possible, only new data can be added to the detail tables
> and it must be loaded using the direct path method."
>
> Have a look at the doco on materialised views it lists all things
> required to use fast refresh.
>
> Have you looked at creating a composite index on PH_KEY and FIELDA on
> both tables? this might help get rid of the need to use a view. Put the
> column with the lowest distinct values (low cardinality) first as this
> will almost ensure uniqueness
> i.e create index .. on table ... (fielda,ph_key)
>
> Getting rid of the group by and making the materialized view a simple
> join should enable you to get fash refresh happening.
>
> If you have time, check whether creating AUDIT_TRAIL as either a IOT or
> creating an index on FieldB,FieldA,PH_KEY (all columns required for
> join) enables a FAST FULL INDEX SCAN. just interested...
>
> Have Fun
Received on Tue Jul 30 2002 - 13:02:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US