Materialized View Refresh Question

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

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 Mon Jul 29 2002 - 14:30:14 CDT