-
Dear All,
When I tried to create a materialized view with this command ,
CREATE MATERIALIZED VIEW test_view
BUILD IMMEDIATE
REFRESH FORCE ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
cm012.cm012_nric_fin_no nric
FROM
cm012_mem_info_dtl cm012
UNION ALL
SELECT
ck020.ck020_nric_fin_no
FROM ck020_ppnt_parti_maint ck020; , I got the error
"ORA-30370: set operators are not supported in this context."
I want to refresh the materialized view on every commit on the master tables. Can this be achieved with the "union" clause by another method ??.
Thanks in advance
Louis.
-
One of the previous restrictions on materialized views has been removed in Oracle9i
Database Release 2. You can now specify the fast refresh option on a materialized view
utilizing the UNION ALL construct. To determine the table from which a row originates,
since all the rows from each table are present, you use a column, named MARKER , that is
an alphanumeric constant. Each table is assigned a constant value for all rows.
Materialized views can be built on top of this view. For Oracle9i Database Release 2 a
materialized view can be fast refreshable with aggregate functions and joins.
Materialized view logs must be created on all the base tables, using the WITH ROWID option.
What you want to do, is not supported in 9.0.x.
It's a new feature of 9.2.
Hope this helps
Gert
