Re: Help rewriting query

From: News <Contact_404_at_hotmail.com>
Date: 16 Nov 2005 08:16:43 -0800
Message-ID: <1132157803.706371.39460@g14g2000cwa.googlegroups.com>

Andy Hassall a écrit :

> Can't say I've done more than the bare basics with materialised views, so odds
> are there's something fundamentally wrong with this, but how about the sequence
> below?

Hi !

I run Oracle9i Enterprise Edition Release 9.2.0.1.0 under AIX 5.2

CREATE MATERIALIZED VIEW mv ... REFRESH FAST .. produces :

ORA-12015: cannot create a fast refresh materialized view from a complex query

But :
CREATE MATERIALIZED VIEW ... REFRESH COMPLETE ... Then:

select CAPABILITY_NAME,possible,MSGTXT from mv_capabilities_table where mvname = 'MV';

CAPABILITY_NAME                               P MSGTXT
----------------------------------------------      -
-------------------------------------------------------
...
REFRESH_FAST_AFTER_INSERT      N multiple instances of the same table

or view

I guessed you run 10g so I installed it and it worked!! Thanks for the tip!

So materialized views with multiple instances of the same table are fast refreshable in 10g.

I have now another alternative: upgrade unless anyone has other solution. For me upgrade will be logistical nightmare (8 huge data warehouses distributed over the country).

Another problem:

Now try please in the same sample inserting row and fast refreshing! The MV is updated only when I issue a complete refresh. It works fine in 9i. I tried it with refresh on commit, refresh on demand and when t1, t2 and t3 are different.. Is there in 10g any parameter I must set for the MV to be updated?

In 10g docs it's said
" If you think the materialized view did not refresh, check the alert log or trace file. "
but I see nothing there. Received on Wed Nov 16 2005 - 10:16:43 CST