Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Error on Materialized view : subquery or named view in FROM list even after view merging

Re: Error on Materialized view : subquery or named view in FROM list even after view merging

From: Victor K. <nicolas.karasek_at_gmail.com>
Date: 31 Jul 2006 04:40:49 -0700
Message-ID: <1154346049.585807.22880@h48g2000cwc.googlegroups.com>


DA Morgan a écrit :

> Victor K. wrote:
> > Hello,
> > i'm trying to create a mview with FAST refresh, ON COMMIT and a query
> > with UNION ALL.
> > I succeed in creating some mview of this type, so i assume mview logs
> > creation is ok.
> > So i tried another another query, it didn't work and
> > DBMS_VIEW_EXPLAIN_MVIEW give me the following tips :
> >
> > REFRESH_FAST_AFTER_INSERT F subquery or named view in FROM list even
> > after view merging
> >
> > The concerned query is :
> >
> > SELECT
> > d.ID_PROP as ID_LINE,
> > 'RT' as TYPE,
> > a.TYPE_SS_CAT as CAT,
> > COUNT(*) as TOTAL,
> > COUNT(a.TYPE_SS_CAT) as VALEUR,
> > 'attn' as TYPE_COUNT,
> > d.MARCHE_ID,
> > 1 as umarker
> > FROM
> > ATTN a inner join DBMV d on a.REF_INT_DBMV = d.ID
> > WHERE
> > d.ID_PROP IS NOT NULL AND
> > a.INDICATOR = 1 AND
> > a.TYPE_PROP = 'RT' AND
> > d.MARCHE_ID = 1
> > group by a.TYPE_SS_CAT, d.MARCHE_ID, d.ID_PROP
> > union all
> > SELECT
> > d.ID_FG_LINE as ID_LINE,
> > 'FG' as TYPE,
> > a.TYPE_SS_CAT as CAT,
> > COUNT(*) as TOTAL,
> > COUNT(a.TYPE_SS_CAT) as VALEUR,
> > 'attn' as TYPE_COUNT,
> > d.MARCHE_ID,
> > 2 as umarker
> > FROM
> > ATTN a inner join DBMV d on a.REF_INT_DBMV = d.ID
> > WHERE
> > d.ID_FG_LINE IS NOT NULL AND
> > a.INDICATOR= 1 AND
> > a.TYPE_PROP = 'FG' AND
> > d.MARCHE_ID = 1
> > group by a.TYPE_SS_CAT, d.MARCHE_ID, d.ID_FG_LINE
> >
> > As english isn't my mothertongue, i don't fully understand the error
> > message and what it is the problem.
> > Oracle version is 9.2.
> >
> > does anyone see the problem ?
> >
> > regards,
>
> What is the value for query_rewrite_integrity in gv$parameter?

Hello,

Thanks for your response, i don't know the value of the parameter, but i succeed in resolving the problem. It came from the inner join. I transformed 'ATTN a inner join DBMV d on a.REF_INT_DBMV = d.ID' in 'ATTN a, DBMV d where a.REF_INT_DBMV = d.ID' and it worked. Why does it work like that and not in inner join form ? mystery...

regards,

-- 
Victor
Received on Mon Jul 31 2006 - 06:40:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US