Materialized views with joins

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 Wed Jun 28 2000 - 00:00:00 CDT