OraFAQ Forum: Performance Tuning » ORA-12013: updatable materialized views must be simple enough to do fast refresh

Home » RDBMS Server » Performance Tuning » ORA-12013: updatable materialized views must be simple enough to do fast refresh (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12013: updatable materialized views must be simple enough to do fast refresh [message #359642]

Mon, 17 November 2008 16:01

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,

Report message to a moderator

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

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Remove the group by and count.

Why do you want it to be updateable?

Ross Leishman

Report message to a moderator

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

Kevin Meade
Messages: 2103
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

Report message to a moderator

Previous Topic:	Query optimization
Next Topic:	Synonyms and dblinks

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 08:21:32 CDT 2024