Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Regarding Materialized View , plz help

    Hi ALL,

    I've three tables each having 70-80 millions of data.I need to join these 3 tables for spooling the data into a file . For that , I'm creating a materialized view in Oracle 9i. The tables may be updated daily.But I have to generate the file after every 14 days. I need the refresh data in the tables
    in the time of file generation(every 14 days) . There are many packages using this materialized view.

    1. So should I use materialized view?
    2. If I use then what will be the properties to be set?
    I've gone through : REFRESH ON DEMAND,REFRESH ON COMMIT.
    3.What will be the properties needed to increase the performance of materialized view.
    4.What is the content of materialized log file?Does it contain only the updated rows ?

    Any suggestion will be highly appreciated .

    Thanks in advance ....
    himridul

  2. #2
    Join Date
    Jan 2004
    Posts
    66
    I need fresh data everytime , I mean that file will be generated once in 14 days . The file would be generated once within 14 days.Now when the view will be called , it should return the fresh data from the database .
    I'm expecting at least 100 millions of rows from this materialized view. can I use REFRESH ON DEMAND option? The query of the materialized view will be like this :

    CREATE MATERIALIZED VIEW M_XX AS
    /*
    WHAT WILL BE THE PARAMETERS HERE?
    */

    SELECT
    A.INDIVIDUALID,
    C.BESTCONTACTADDRESSID,
    B.ACCOUNTID,
    B.SERVICEID,
    D.ADDRESSID
    FROM
    CUSTOMER A,
    CUSTOMERACCOUNT B,
    INDIVIDUAL C,
    ADDRESS D,
    (
    SELECT INDIVIDUALID,
    max(REGISTRATIONDATE) REGISTRATIONDATE
    from CUSTOMERACCOUNT
    group by INDIVIDUALID
    ) E
    WHERE
    A.INDIVIDUALID=B.INDIVIDUALID(+)
    AND (A.INDIVIDUALID = E.INDIVIDUALID(+)
    AND NVL(B.REGISTRATIONDATE,to_date('12312099','mmddyyy y')) =
    NVL(E.REGISTRATIONDATE,to_date('12312099','mmddyyy y'))
    )
    AND A.INDIVIDUALID=C.INDIVIDUALID
    AND C.BESTCONTACTADDRESSID = D.ADDRESSID(+)

    Thanks for ur anticipation .....
    himridul

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    you could have it refresh every 14 days ...
    Considering you don't need it but 26 times a year you want to make sure you are not doinf needless refreshing.

    Look up the docs on parameters that would fit this mold.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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