Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Location
    Leverkusen
    Posts
    2

    Question Unanswered: ORA-12054 when creating materialized view

    Oracle Version: 9.2.0.3.0

    When creating a materialized view, I received "ORA-12054 : cannot set
    the ON COMMIT refresh attribute for the materialized view" error
    message.

    My view worked fine so far in the first version without 'Cat' filter

    DROP MATERIALIZED VIEW LOG ON PETS;
    DROP MATERIALIZED VIEW LOG ON PETS_OWNER;

    CREATE MATERIALIZED VIEW LOG ON PETS WITH ROWID;
    CREATE MATERIALIZED VIEW LOG ON PETS_OWNER WITH ROWID;

    --------------------------------------------------------------------------
    -- Materialized View with fast option (COMMIT aktualisiert) - IO
    --------------------------------------------------------------------------
    -- is working
    DROP MATERIALIZED VIEW MV_PETS_COMMIT;

    CREATE MATERIALIZED VIEW MV_PETS_COMMIT
    REFRESH FAST ON COMMIT
    AS SELECT a.pet_id, a.pet_kind, a.pet_name, b.owner, a.ROWID
    pets_rowid, b.ROWID pets_owner_rowid
    FROM PETS a, PETS_OWNER b
    WHERE a.PET_ID = b.PET_ID (+);

    --------------------------------------------------------------------------
    -- filter 'cats' PETS table
    --------------------------------------------------------------------------
    -- ORA-12054: Refresh-Attribut ON COMMIT kann für Materialized View nicht festgelegt werden
    CREATE MATERIALIZED VIEW MV_PETS_COMMIT_NIO1
    REFRESH FAST ON COMMIT
    AS SELECT a.pet_id, a.pet_kind, a.pet_name, b.owner, a.ROWID
    pets_rowid, b.ROWID pets_owner_rowid
    FROM PETS a, PETS_OWNER b
    WHERE a.PET_ID = b.PET_ID (+)
    AND a.PET_KIND = 'Cat';

    --------------------------------------------------------------------------
    -- PETs Sub-Select in FROM clause
    --------------------------------------------------------------------------
    -- ORA-12054: Refresh-Attribut ON COMMIT kann für Materialized View nicht festgelegt werden
    CREATE MATERIALIZED VIEW MV_PETS_COMMIT_NIO2
    REFRESH FAST ON COMMIT
    AS SELECT a.pet_id, a.pet_kind, a.pet_name, b.owner, a.ROWID
    pets_rowid, b.ROWID pets_owner_rowid
    FROM (SELECT * FROM PETS a WHERE PET_KIND = 'Cat') a,
    PETS_OWNER b
    WHERE a.PET_ID = b.PET_ID (+);

    --------------------------------------------------------------------------
    -- view version
    --------------------------------------------------------------------------
    CREATE OR REPLACE VIEW PETS_COMMIT_V
    AS SELECT a.pet_id, a.pet_kind, a.pet_name, b.owner, a.ROWID
    pets_rowid, b.ROWID pets_owner_rowid
    FROM PETS a, PETS_OWNER b
    WHERE a.PET_ID = b.PET_ID (+)
    AND a.PET_KIND (+) = 'Cat';

    -- ORA-12054: Refresh-Attribut ON COMMIT kann für Materialized View nicht festgelegt werden
    CREATE MATERIALIZED VIEW MV_PETS_COMMIT_NIO3
    REFRESH FAST ON COMMIT
    AS SELECT * FROM PETS_COMMIT_V;


    Any ideas ?

    regards, C. Hauth

  2. #2
    Join Date
    Jan 2007
    Posts
    5
    Fast refresh has some restrictions on outer joins. See the documentation for details.

    You have these choices:
    1. Use complete refresh (Can be very slow, because upon commit the whole M.View will be refreshed)
    2. Change the query
    3. Use triggers to update the summary tables

  3. #3
    Join Date
    Mar 2002
    Location
    Leverkusen
    Posts
    2
    skchonghk, thanks for you fast reply.

    I know about these restrictions but what restriction in detail makes our statement construction impossible to use. Outer joines can be used.

    1. complete refresh is too slow for our data amount. there're lots of changes in parallel.
    2. change the query to what?
    3. triggers are possible, but I wanted to take advantage of oracle self management of these mviews. No triggers getting invalid and so on.

  4. #4
    Join Date
    Jan 2007
    Posts
    5
    See the documentation here:

    http://www.dbforums.com/showthread.p...30#post6255230

    Search the section "Restrictions on Fast Refresh on Materialized Views with Joins Only". This should be the restriction violated:

    ...if there are outer joins, the WHERE clause cannot have any selections. ...

    I think that explains why MV_PETS_COMMIT works but MV_PETS_COMMIT_NIO1 does not.

    BTW, MV_PETS_COMMIT has served the purpose of MV_PETS_COMMIT_NIO1 actually. And, I wonder this situation really needs M.View, as the query may not be too slow, if the tables are properly indexed.

    Adrian

Posting Permissions

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