| Summary Materialized views [message #404427] |
Thu, 21 May 2009 15:24  |
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 #404510 is a reply to message #404427] |
Fri, 22 May 2009 01:06  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
