Regarding Materialized View , plz help

[himridul]

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 ....

 

[sem]

Do you packages need "fresh" data or 14-days old also suits? How many rows do you expect to get from that MV?

Regards, Dima

http://seminihin.narod.ru

 

[himridul]

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','mmddyyyy')) =
NVL(E.REGISTRATIONDATE,to_date('12312099','mmddyyyy'))
)
AND A.INDIVIDUALID=C.INDIVIDUALID
AND C.BESTCONTACTADDRESSID = D.ADDRESSID(+)

Thanks for ur anticipation .....