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

The MV really does not look too complex, does it? I have pretty much given up on trying to argue with ORACLE whether or not an MV is complex and fast refreshable - because ORACLE wins every time. I think ORACLE is little by little trying to make as many types of MV fast refreshable as possible. Your MV may be fast refreshable in the next release of ORACLE, who knows.

You can use DBMS_MVIEW.EXPLAIN to help you find out whether or not an MV is fast refreshable. And if not, for what reasons. Though I found the info. is not always 100% acccurate.

It seems an MV is considered complex if it involves joining of 2 remote tables. You may have to use nested MV to get what you need. Create an local MV which joins the 2 tables , then create a remote MV on top of this local MV. Both MVs can be fast refreshable - in your case.

• Jusung Yang

applicationz_at_paradise.net.nz (Stuart MacKinnon) 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.
>
> 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 - 13:24:06 CST