Re: Materialized views with joins

Ed:

The problem is that this is not a "simple" snapshot. Oracle defines this somewhere, and I don't recall all of the rules, but I do know that it cannot have a join in it.

Subqueries are allowed but not joins.

What you may want to do is to create two separate snapshots (one for each table) and then at the snapshot site, define the view that references the two snapshots.

Tom Best

<ezarecor_at_my-deja.com> wrote in message news: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.
Received on Thu Jun 29 2000 - 00:00:00 CDT