Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12015: can not create fast refresh materialized view from complex query (10g)
ORA-12015: can not create fast refresh materialized view from complex query [message #361173] Tue, 25 November 2008 04:56 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I wanted to create a materialized view based on joining 4 source tables and want to display the
records from it based on search criteria. I did created so that the new table will
be refreshed after every day using fast refresh clause.But getting error.


Approach I followed:

Step1: Records with selected columns should be inserted from 4 different tables to a single new tables

I created a complex view for this.And then wanted to use materialized view using FAST REFRESH ( it should be refreshed everyday)
based on the view.But when I tried to create materalized view its showing error that materialized view can't be
created on complex query. Need your suggesion here to resolve the issue.


Step2: Create procedure to fetch results from the table based on the materialized view with different search criteria.




Regards,
Oli
Re: ORA-12015: can not create fast refresh materialized view from complex query [message #361175 is a reply to message #361173] Tue, 25 November 2008 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. You can create the mv but you can't use fast refresh if it too complex.

2. SELECT

Regards
Michel
Re: ORA-12015: can not create fast refresh materialized view from complex query [message #361189 is a reply to message #361175] Tue, 25 November 2008 05:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I have joined 4 tables and created the view and then wants to refresh the result based on the query everyday with fast refresh clause.


Any alternative solution to overcome this issue?


Requirement: Get refreshed data ( with specific column from the table) from 4 tables to a new table (Tried to do it using Materialized view with fast refresh clause because with fast refresh we can assign start date and nextday).










Regards,
Oli
Re: ORA-12015: can not create fast refresh materialized view from complex query [message #361194 is a reply to message #361189] Tue, 25 November 2008 05:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can assign Start and Nextday without using the Fast Refresh.

All Fast Refresh does (it is poorly named) is to perform an incremental refresh on the MV, rather than recreating it from scratch. It is quite possible for a Fast Refresh to be slower than a Complete refresh.

Regardless of this, a quick look at the documentation will show that you can specify START WITH and NEXT independently of the Refresh mode.
Previous Topic: Exception messages
Next Topic: Why Deadlock occured
Goto Forum:
  


Current Time: Sat Dec 10 20:40:51 CST 2016

Total time taken to generate the page: 0.07933 seconds