hello
I am having trouble figuring out this part , I am trying to create a MV so that it refresh on commit. Here is my code
-- Drop view
DROP MATERIALIZED VIEW vcat_search;
--drop snapshot log on entities;
--drop snapshot log on individuals;
--drop snapshot log on VA_client;
--
--create snapshot log on entities with primary key, rowid;
--create snapshot log on individuals with primary key, rowid;
--create snapshot log on VA_client with primary key, rowid;
drop materialized view log on entities;
drop materialized view log on individuals;
drop materialized view log on VA_client;
create materialized view log on entities with rowid , PRIMARY key ( entity_id) including new values;
create materialized view log on individuals with rowid, PRIMARY key (individuals_id) including new values;
create materialized view log on VA_client with rowid, PRIMARY key ( VA_client_id) including new values;
-- Create view for entity and individaul
CREATE MATERIALIZED VIEW vcat_search
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
entities.entity_id AS vcat_id,
entities.entity_name name,
'ENTITY' "Type"
FROM
entities
UNION ALL
SELECT
individuals.individuals_id AS vcat_id,
individuals.first_name
|| ' '
|| nvl2(individuals.middle_name, individuals.middle_name || ' ', '')
|| individuals.last_name
|| nvl2(individuals.second_last_name, ' '
|| individuals.second_last_name
|| ' ', '') name,
'INDIVIDUAL' "Type"
FROM
individuals
UNION ALL
SELECT
VA_client.VA_client_id AS vcat_id,
VA_client.client_name name,
'VA CLIENT' "Type"
FROM
VA_client
error
Error report -
ORA-12052: cannot fast refresh materialized view VCAT.VCAT_SEARCH
12052. 00000 - "cannot fast refresh materialized view %s.%s"
*Cause: Either ROWIDs of certain tables were missing in the definition or
the inner table of an outer join did not have UNIQUE constraints on
join columns.
*Action: Specify the FORCE or COMPLETE option. If this error is got
during creation, the materialized view definition may have be
changed. Refer to the documentation on materialized views.
can we achieve refresh fast on Commit, i know it works for On demand.
Please suggest.
Thanks