Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Meterialized View Creation Prob

    CAN I create FAST REFERECH ON COMMIT
    Materialized View on the following Query..

    SELECT
    PE.LOAN_NUMBER AS LOAN_NUMBER, PE.DUE_DATE
    FROM
    (SELECT LOAN_NUMBER, PROCESS_ID FROM WF_LOAN_PROCESS, PROCESS WHERE PROCESS_ID =
    OBJECT_ID AND STATE = 2) LP,
    PROCESS P,WF_PROCESS_EXTENSION PE
    WHERE
    LP.LOAN_NUMBER = PE.LOAN_NUMBER AND
    LP.PROCESS_ID = PE.ROOT_PROCESS_ID AND
    PE.PROCESS_ID = P.OBJECT_ID AND
    P.STATE = 2
    GROUP BY PE.LOAN_NUMBER, PE.DUE_DATE;

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Definitions:

    FAST
    Specify FAST for incremental refresh method, which performs the refresh according
    to the changes that have occurred to the master tables. The changes are stored
    either in the materialized view log associated with the master table
    (for conventional DML changes) or in the direct loader log (for direct-load INSERT operations).
    For both conventional DML changes and for direct-path loads, other conditions may
    restrict the eligibility of a materialized view for fast refresh.


    Restrictions:
    When you specify FAST refresh at create time, Oracle verifies that the materialized view
    you are creating is eligible for fast refresh. When you change the refresh method to FAST
    in an ALTER MATERIALIZED VIEW statement, Oracle does not perform this verification.
    If the materialized view is not eligible for fast refresh, Oracle will return an error
    when you attempt to refresh this view.

    Materialized views are not eligible for fast refresh if the defining query contains
    an analytic function.

    COMPLETE
    Specify COMPLETE for the complete refresh method, which is implemented by executing
    the materialized view's defining query. If you request a complete refresh,
    Oracle performs a complete refresh even if a fast refresh is possible.

    FORCE
    Specify FORCE if, when a refresh occurs, you want Oracle to perform a fast refresh
    if one is possible or a complete refresh otherwise.

    ON COMMIT
    Specify ON COMMIT if you want a fast refresh to occur whenever Oracle commits a
    transaction that operates on a master table of the materialized view.

    Restriction:
    This clause is supported only for materialized join views
    and single-table materialized aggregate views.

    ON DEMAND
    Specify ON DEMAND if you want the materialized view to be refreshed on demand by
    calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT
    and ON DEMAND, ON DEMAND is the default.


    HTH
    Gregg

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    I created the FAST REFRESH ON DEMAND MV on the above query but it dont let me create ON COMMIT. Can i use FAST REFRESH ON COMMIT when i use subquery in FROM clause...

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Restriction:
    This clause is supported only for materialized join views
    and single-table materialized aggregate views.

Posting Permissions

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