ROWID-based aggregate MV

I need to build a fast-refreshable aggregate MV on a table that does not have primary key, so it has to be ROWID-based. Oracle says that such MV is not allowed - From Oracle816 SQL Reference, page 7-326: "Rowid-based materialized views must be based on a single remote table and cannot contain any of the following:
* distinct or aggregate function

• GROUP BY or CONNECT BY clauses ..." That's a very severe restriction - I think that the following scenario is very common: 1. Large "historical facts" table 2. No primary key: because this will require multi-column index, this index will slow down inserts. Moreover, if direct loads are used to populate the table and there is a need to run queries in parallel with running direct loads, this index will be useless as direct loads put indexes in unusable state. 3. A summary table is required to present aggregate data (mostly counts and sums).

If fast-refresheable MV is not possible, then the only other aproach is to load each file twice - in the "historic facts" table and also in the staging table, run a query against the staging table to update the summary table, and then truncate the staging table - clearly, a MV would be much better.

Regards,
Sev Received on Mon Jan 28 2002 - 18:32:05 CST