Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View (Oracle 9.2)
Materialized View [message #326237] Tue, 10 June 2008 15:13 Go to next message
Messages: 91
Registered: February 2007
Location: NJ, US
I am creating Materialized View to speed up a query for an OLTP application and need data to refreshed immediately
but fast refresh is not an option since it is complex query with aggregate function(SUM)and group by clause etc.

What options do I have achieve this apart from using dbms_mview package to achieve this.

Re: Materialized View [message #326238 is a reply to message #326237] Tue, 10 June 2008 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a remark: if you need a MV with aggregate functions then it is not an OLTP application.

Re: Materialized View [message #326241 is a reply to message #326237] Tue, 10 June 2008 16:28 Go to previous message
Messages: 213
Registered: February 2008
Senior Member
Here are a couple of options, although none are ideal.

1. Change the OLTP applications that write the data you are trying to aggregate, so they also manage an aggregated view that is performant at the same time. (unlikely)

2. Go half way with your mview. Write as much as you can as fast refreshable, and see if you can do the rest in your query of that mview.

3. Create triggers on the underlying tables that capture the changes you need, and massage the data into a new table, from which you can make a fast refreshable mview.


app_table_1 --> capture changes, transform and send to mview_table1
app_table_2 --> capture changes, transfor and send to mview_table1

create a fast refreshable mview on mview_table_1
Previous Topic: Update statment
Next Topic: indexing materialized view - resource busy error
Goto Forum:

Current Time: Thu Aug 17 05:01:33 CDT 2017

Total time taken to generate the page: 0.07957 seconds