Materialized View on a partitioned table.

From: Adrian Carlson-Hedges <adrian.ch_at_btinternet.com>
Date: Tue, 16 Apr 2002 20:23:32 +0000 (UTC)
Message-ID: <a9i183$4d3$1@knossos.btinternet.com>

Oracle 8.1.7.2 running on Win2K

I have a table partition by range on a date field. After the end of the day, it is highly unlikely (although possible) that the table will have any more updates/deletes applied to it. If it is updated, then it will be very infrequently (perhaps only once in a week). I want to create a(some) materialized view(s) on the table containing summary data, and other computed data form the clob. I have functions that pull out the various sections of the clob that I need

e.g.
table t1 (
code varchar2(30),
xmldata clob,
capture_date date)

partitioned daily on capture date each day.

function FIDNumber(pfid NUMBER, pdata clob) return number;

materialized view agg_bid_per_code_20020416 refresh on commit
select code,

  min(FIDNumber(23, xmldata)),
  max(FIDNumber(23, xmldata)),
  avg(FIDNumber(23, xmldata))

from t1 partition(part_20020416)
group by instrument_code;

My question is:

Given that I will be creating my materilazed views against a single partition, will inserts into other partitions cause my materialized view to be recalculated?If so what are my other options?

I am using partitions to maintain a rolling window of data that will potentially be queried a lot. All of the queries will be performing some kind of summary operation(s).

Thanks in advance

Adrian Received on Tue Apr 16 2002 - 15:23:32 CDT