Re: Materialized views with joins

Working from memory here, and assuming 8.1.5-ish.

Have you declared the PK/FK relationship between product and company; and don't you need to include some column names (in brackets) in the WITH clause - possibly the join column.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

ezarecor_at_my-deja.com wrote in message <8jdrvj$m2$1_at_nnrp1.deja.com>...


>Has anyone had any luck with fast refresh on a materialized view with a

>join.  According to the Oracle documentation this is possible if the

>both tables have materialized view logs that include the rowid.  After

>rebuiding my view logs and the view, I'm still, alas, unable to get it

>to fast refresh.

>

>ORA-12004: REFRESH FAST cannot be used for snapshot "SCOTT"."TEST_MV"

>

>simplified example tables:

>

>TABLE PRODUCT

>

>PRODUCT_ID    PRODUCT_NAME     COMPANY_ID

>

>TABLE COMPANY

>

>COMPANY_ID    COMPANY_NAME

>

>related queries:

>

>create materialized view log on company

>  with primary key, rowid

>  including new values;

>

>create materialized view log on product

>  with primary key, rowid

>  including new values;

>

>create materialized view test_mv

>  refresh fast

>  as select product_id, product_name, company_name

>    from product p,

>         company c

>    where p.company_id = c.company_id;

>

>BEGIN dbms_refresh.refresh('"SCOTT"."TEST_MV"'); END;

>

>Yields:

>

>ORA-12004: REFRESH FAST cannot be used for snapshot "SCOTT"."TEST_MV"

>ORA-06512: at "SYS.DBMS_SNAPSHOT", line 587

>ORA-06512: at "SYS.DBMS_SNAPSHOT", line 644

>ORA-06512: at "SYS.DBMS_IREFRESH", line 561

>ORA-06512: at "SYS.DBMS_REFRESH", line 207

>ORA-06512: at line 1

>

>Haven't been able to find anything in the Oracle docs that I haven't

>done.  Any and all help will be greatly appreciated.  Thanks.

>

>Ed.

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.