union in materialized view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: union in materialized view

  1. #1
    Join Date
    Feb 2002
    Posts
    23
    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.




  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width