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: Join in materialized view = A "Complex" query?

Re: Join in materialized view = A "Complex" query?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 20 Nov 2002 01:02:44 +1000
Message-ID: <jirC9.79949$g9.224687@newsfeeds.bigpond.com>


"Stuart MacKinnon" <applicationz_at_paradise.net.nz> wrote in message news:56db38fe.0211181826.19202d85_at_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.

Hi Stuart,

And yet complex it is ;)

A view is deemed to be complex by Oracle if it contains a join condition or some form of aggregation. You have a join, therefore it's a complex view.

Oracle can have problems refreshing a MV if it's complex as it may not make sense to do so per se or Oracle is simply unable to do so. If you look in the Data Warehouse manual, you will find the complete list of restrictions or rules that must be followed for complex views to be fast refreshable. Note that Oracle becomes a little cleverer in what it can fast refresh with each new release and lifts some of the restrictions.

Now having a quick squiz at your MV, the fact you are not recording the rowids in the MV logs nor selecting the rowids in your select definition appears to be one problem. Also be sure that you have sufficient PK/Unique constraints in your (inner) joined columns as again Oracle will chuck a wobbly.

My suggestion would be to at least check out both suggestions above, have a read of the Data Warehouse manual and ensure there are no other violations.

Good luck with it all

Richard
>
> 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 Tue Nov 19 2002 - 09:02:44 CST

Original text of this message

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