Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: Materialized View question

    I have the following MV that I want to implement.

    Since I have not created logs before I wanted some advice/help.

    Basically, the 3 tables that are join RARELY get inserted into. IF a row gets inserted, I need the MV to auto-update. Would the REFRESH FAST ON COMMIT take care of this??

    Otherwise, what else would I need to do?

    PHP Code:
    CREATE MATERIALIZED VIEW ACTIVE_DEVICE_MV
    BUILD IMMEDIATE
    refresh fast on commit
    AS
    SELECT ad.org_idad.device_idad.cust_ida.CREATED_DT
    FROM ACCOUNT_DEVICE ad
    ACCOUNT aGATEWAY_PREMISE gp
    WHERE
    a
    .org_id ad.org_id AND
    a.org_id gp.org_id AND
    a.svc_type_cd 'E' AND
    a.svc_type_cd ad.svc_type_cd AND
    a.cust_id ad.cust_id AND
    a.cust_id gp.cust_id AND
    a.prem_seq_nbr ad.prem_seq_nbr AND
    a.prem_seq_nbr gp.prem_seq_nbr AND
    a.active_ind 'Y' AND
    gp.active_ind 'I'AND
    gp.org_id ad.org_id AND
    gp.cust_id ad.cust_id
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes the main options are to refresh the MV on commit, or on demand. This is very much driven by whether the view is queried rarely and how quick you want that query to perform.

    If you're getting 10,000 inserts daily on the tables but only one query on the view then probably on demand might be the better option.

    Other more complex options include building two MVs on one table. One (the larger) which covers age old data and isn't refreshed and one which covers say the last month on a good selective index. A separate view unions the two. The larger, older MV is refreshed via an overnight job and the smaller 'current' view is refreshed on demand or on commit.

    Separating old data which will never (unlikely) change from current data like this can offer huge performance advantages.

    Hth
    Bill

Posting Permissions

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