We would like to be able to use fast refresh on a materialised view which contains a union.
This has worked when the union uses the same table. However it does not seem to work with a different table name even though the primary key, and columns selected are identical. We are using Oracle9i Enterprise Edition Release 9.2.0.1.0.

The example that worked:

Table name QH_PAT.
Name Null? Type
------------------------------- -------- ----
FCLTY_ID NOT NULL VARCHAR2(6)
PAT_ID NOT NULL VARCHAR2(8)
SEX NOT NULL VARCHAR2(6)
BIRTH_DATE NOT NULL DATE
BIRTH_DATE_EST_CODE VARCHAR2(6)
VALID_FROM NOT NULL DATE
VALID_TO NOT NULL DATE
FCLTY_UNIQUE_ID NOT NULL VARCHAR2(12)

Primary Key:
alter table qh_pat
add constraint qh_pat_pk
primary key
(
fclty_id,
fclty_unique_id
);


Snapshot log script:
create snapshot log on QH_PAT
with primary key

Materialized View script:
create snapshot qh_pat_mv as
select fclty_id,
fclty_unique_id,
pat_id,
sex,
birth_date_est_code
from qh_pat
where sex = ‘1’
union
select fclty_id,
fclty_unique_id,
pat_id,
sex,
birth_date_est_code
from qh_ pat
where sex = ‘2’
/

The example that creates a complex materialized view that will not allow fast refresh:

Table Name: QH_WORK_PAT
FCLTY_ID NOT NULL VARCHAR2(6)
PAT_ID NOT NULL VARCHAR2(8)
SEX VARCHAR2(6)
BIRTH_DATE VARCHAR2(8)
BIRTH_DATE_EST_CODE VARCHAR2(6)
VALID_FROM NOT NULL VARCHAR2(12)
VALID_TO VARCHAR2(12)
FCLTY_UNIQUE_ID NOT NULL VARCHAR2(12)

Primary Key:
alter table qh_work_pat
add constraint qh_work_pat_pk
primary key
(
fclty_id,
fclty_unique_id
);

Snapshot log script:
create snapshot log on QH_WORK_PAT
with primary key


Materialized View Script:
create snapshot qh_pat_mv as
select fclty_id,
fclty_unique_id,
pat_id,
sex,
birth_date_est_code
from qh_pat
union
select fclty_id,
fclty_unique_id,
pat_id,
sex,
birth_date_est_code
from qh_work_pat

In the first example using QH_PAT only, I can include ‘refresh fast with primary key’ in the script. This also is ok if QH_WORK_PAT is used for both queries.
In the second example using QH_PAT and QH_WORK_PAT. If I put ‘refresh fast with primary key’ in the create script I get the following error.
Creating snapshot QH_PAT_MV
qh_work_pat
*
ERROR at line 18:
ORA-12015: cannot create a fast refresh materialized view from a complex query

According to the 9i documentation a union does not make a materialized view complex unless one of the defining queries is complex. I can not see any mention of having different table names as a cause of ’complex’.

Have you any ideas.