Join in materialized view = A "Complex" query?

From: Stuart MacKinnon <applicationz_at_paradise.net.nz>
Date: 18 Nov 2002 18:26:14 -0800
Message-ID: <56db38fe.0211181826.19202d85@posting.google.com>

Hi all, I am hoping someone out there can help me out on this.

When I try to create materialized views with joins in my report database I get: "ORA-12015: cannot create a fast refresh snapshot from a complex query".

I am working on a reporting database (HMSDSS) which contains fast-refreshed materialized views of tables living on a production database (HMSPROD). (Both databases are version 8.1.7).

I want to create additional fast-refreshed materialized views in HMSDSS which are based on the materialized views of the base tables.

E.g. the tables called "ALIAS" and "ADMISSION" in HMSPROD are replicated onto HMSDSS as materialized views with exactly the same names.

In HMSDSS, I can create FAST REFRESH ON DEMAND materialized view on a single base materialized view (e.g. "ALIAS") like this:

CREATE MATERIALIZED VIEW SXM_TEST_ALIAS
  tablespace data01
  BUILD IMMEDIATE
  REFRESH FAST ON DEMAND
  AS
SELECT

  ALIAS.SEQU,
  ALIAS.LOC_PAT_ID,
  ALIAS.LAST_NAME

FROM
ALIAS_at_HMSDSS
WHERE
ALIAS.SEQU > 123150;

But when I try to join ALIAS and ADMISSION like in the script below, I get: "ORA-12015: cannot create a fast refresh snapshot from a complex query."

CREATE MATERIALIZED VIEW SXM_TEST_DISCHRG_TODAY   tablespace data01
  BUILD IMMEDIATE
  REFRESH FAST ON DEMAND
  AS
SELECT
  ALIAS.SEQU,
  ADMISSION.ALIAS_SEQU,
  ALIAS.LOC_PAT_ID,
  ALIAS.LAST_NAME,
  ADMISSION.DISCHRG_DATE
FROM
  ALIAS_at_HMSDSS,
  ADMISSION_at_HMSDSS
WHERE
  ADMISSION.ALIAS_SEQU = ALIAS.SEQU;

Now it doesn't *look* too complex to me.

For your reference, here are the snapshot logs I created to allow fast refreshes:

create materialized view log on
ADMISSION
with primary key (alias_sequ, dischrg_date) including new values;

~~~~~ AND ~~~~~ create materialized view log on
ALIAS
with primary key (loc_pat_id, last_name) including new values;

Any comments very gratefully received!

Regards
Stuart MacKinnon
NZ Received on Mon Nov 18 2002 - 20:26:14 CST