Home » SQL & PL/SQL » SQL & PL/SQL » Summary Materialized views (Oracle 9.2)
Summary Materialized views [message #404427] Thu, 21 May 2009 15:24 Go to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
During the course of a day I have many questions and I usually research without asking anyone, but alas I am tired.

I have a daily partitioned fact table with .6 million records a day. 500 million total.

I want to create a summary materialized view on an Oracle 9.2 database.

It may not be possible: to create a fast refresh MV with a column of count(distinct column1) and a group by expression.

If a fast refresh is not possible, the whole table would have to be re-summarized, I assume? All 500 million records.

Since only new records are inserted at most a few days back is there anyway to only refresh the materialized view for certain partitions, or a sneaky way to refresh.

Perhaps this is way too sneaky but I am thinking about looking for unusable indexes on source table(direct insert disables index on that partition), and re-aggregating into a normal table the partitions that have the unusable index.

Any ideas?

Getting rid of the count(distinct column1) would increase the number of records in the aggregate from thousands to millions.

Thanks.
Re: Summary Materialized views [message #404428 is a reply to message #404427] Thu, 21 May 2009 15:26 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member

There is no primary key on fact table.
I assume a MV log would have .6 million records a day which seems like meaningless overhead a day.
Re: Summary Materialized views [message #404510 is a reply to message #404427] Fri, 22 May 2009 01:06 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you cannot upgrade to 10g then if your aggregate functions match your partitioning and you don't update every or most of the partitions each day, you can create a view based materialized views created on each partition (or set of partitions: static pnes and "dynamic" (updated) ones). Then only the "dynamic" materialized views have to be refreshed.

Regards
Michel
Previous Topic: Any SQL function to reduce the size
Next Topic: control cannot go to exception
Goto Forum:
  


Current Time: Sat Dec 10 20:14:33 CST 2016

Total time taken to generate the page: 0.12795 seconds