Home » RDBMS Server » Performance Tuning » ORA-12013: updatable materialized views must be simple enough to do fast refresh (Oracle 10g)
ORA-12013: updatable materialized views must be simple enough to do fast refresh [message #359642] Mon, 17 November 2008 16:01 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Hi,


I am getting below error while creating Updatable Materialized view.

ORA-12013: updatable materialized views must be simple enough to do fast refresh


The query to create materialized view is:

CREATE MATERIALIZED VIEW dept FOR UPDATE AS
   SELECT firm, type,
 COUNT(*) ,
date 
 FROM detail 
GROUP BY firm, type, date;


What can I do to make it simple enough?

Please let me know how to proceed further.

Regards,
Re: ORA-12013: updatable materialized views must be simple enough to do fast refresh [message #359650 is a reply to message #359642] Mon, 17 November 2008 19:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Remove the group by and count.

Why do you want it to be updateable?

Ross Leishman
Re: ORA-12013: updatable materialized views must be simple enough to do fast refresh [message #360419 is a reply to message #359650] Thu, 20 November 2008 22:31 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am no expert with Materialized Views. However, I seem to recall there were very specific rules on how to construct fast refreshable aggregate materialized views. You need to read the docs about materialized views and see where your view fails to meet the requirements of fast refresh.

For example, I think you need count(firm),count(type) as columns. Maybe also sum(firm),sum(type).

Or it may be these rules related to query rewrite and/or restrictions on what additional columns were needed in order to use specific functions and operators if query rewrite was expected. Or maybe a combination of the two ideas fast refreshable and query rewrite.

Point is, there is some docs that will give you more insight. Google up the oracle documentation for fast refreshable Mviews and read about it.

Good luck, Kevin
Previous Topic: Query optimization
Next Topic: Synonyms and dblinks
Goto Forum:
  


Current Time: Sun Dec 11 00:36:11 CST 2016

Total time taken to generate the page: 0.09649 seconds