Re: ROWID-based aggregate MV

Why not add a column and populate it with a sequence so you have a primary key?

Or ... even more radical ... normalize.

Daniel Morgan

Vsevolod Afanassiev wrote:

> 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 - 10:53:02 CST