Materialized View Please Help

[r_adusumil]

Hi,

i have following query that builds a materialized view,
i have two databas instances,i was able to create materialized view in one instance and in the other instance which has more number of records in the underlying tak emore time.not more time i have been running this query for quite some time an dthis is still running,

please let ke know if there is any problem in creating the Materialized View.

Plz Help,
Thank You

create MATERIALIZED view id_hist_mv
build immediate
as
SELECT /*+RULE use_nl(b,bh1)*/ DISTINCT
b.bid_number,
b.bid_received_date bid_date, -- Date placed Bid was Received
max(decode(bh1.bid_status,'SC',bh1.created_date,null)) sc_date, -- (latest) Date placed bid went to SC status
max(decode(bh2.bid_status,'CP',bh2.created_date,null)) cp_date, -- Date placed bid went to CP status
max(decode(bh3.bid_status,'RTC',bh3.created_date,null)) rtc_date, -- Date placed bid went to RTC status
max(decode(bh4.bid_status,'PRESCR',bh4.created_date,null)) prescr_date, -- Date placed bid went to PRESCR status
max(decode(bh5.bid_status,'CIR',bh5.created_date,null)) iv_requested_date, -- Date placed bid went to CIR status
max(decode(bh6.bid_status,'IVINTERN',bh6.created_date,null)) iv_Delphi_date,-- Date placed bid went to IVINTERN status
max(decode(bh7.bid_status,'IVCUST',bh7.created_date,null)) iv_Cust_date, -- Date placed bid went to IVCUST status
max(decode(bh8.bid_status,'REBID',bh8.created_date,null)) iv_Rebid_date, -- Date placed bid went to REBID status
b.st_rate bid_rate -- Rate on the Bid
FROM
bid b, bid_his bh1, bid_his bh2, bid_his bh3, bid_his bh4, bid_his bh5, bid_his bh6,
bid_his bh7,bid_his bh8
WHERE ( b.bid_number = bh1.bid_number(+) ) -- To get the Date placed bid went to SC status
and ( b.bid_number = bh2.bid_number(+) ) -- To get the Date placed bid went to CP status
and ( b.bid_number = bh3.bid_number(+) ) -- To get the Date placed bid went to RTC status
and ( b.bid_number = bh4.bid_number(+) ) -- To get the Date placed bid went to PRESCR status
and ( b.bid_number = bh5.bid_number(+) ) -- To get the Date Delphi requested Interview (placed bid went to CIR status)
and ( b.bid_number = bh6.bid_number(+) ) -- To get the Date of Interview (placed bid went to IVINTERN status)
and ( b.bid_number = bh7.bid_number(+) ) -- To get the Date of Interview (placed bid went to IVCUST status)
and ( b.bid_number = bh8.bid_number(+) ) -- To get the Rebid Date (placed bid went to REBID status)
and ( b.bid_status in ('SC','CP'))
and ( bh1.bid_status in ('SC') and bh1.status_changed_flag = 'Y' )
and ( bh2.bid_status in ('CP') and bh2.status_changed_flag = 'Y' )
and ( bh3.bid_status in ('RTC','SC', 'CP') and bh3.status_changed_flag = 'Y' )
and ( bh4.bid_status in ('PRESCR','SC','CP') and bh4.status_changed_flag = 'Y' )
and ( bh5.bid_status in ('CIR','SC','CP') and bh5.status_changed_flag = 'Y' )
and ( bh6.bid_status in ('IVINTERN','SC','CP') and bh6.status_changed_flag = 'Y' )
and ( bh7.bid_status in ('IVCUST','SC','CP') and bh7.status_changed_flag = 'Y' )
and ( bh8.bid_status in ('REBID','SC','CP') and bh8.status_changed_flag = 'Y' )
GROUP BY
b.bid_number,
b.bid_received_date, -- Date placed Bid was Received
b.st_rate -- Rate on the Bid;

[r_adusumil]

Originally posted by r_adusumil
Hi,

i have following query that builds a materialized view,
i have two databas instances,i was able to create materialized view in one instance and in the other instance which has more number of records in the underlying tak emore time.not more time i have been running this query for quite some time an dthis is still running,

please let ke know if there is any problem in creating the Materialized View.

Is there any thing that has to be cleaned up at the database level...this materialized view already existed in the database instance earlier i dropped th eexisting one and added 2 new columns and its hanging now..plz advise

Plz Help,
Thank You

create MATERIALIZED view id_hist_mv
build immediate
as
SELECT /*+RULE use_nl(b,bh1)*/ DISTINCT
b.bid_number,
b.bid_received_date bid_date, -- Date placed Bid was Received
max(decode(bh1.bid_status,'SC',bh1.created_date,null)) sc_date, -- (latest) Date placed bid went to SC status
max(decode(bh2.bid_status,'CP',bh2.created_date,null)) cp_date, -- Date placed bid went to CP status
max(decode(bh3.bid_status,'RTC',bh3.created_date,null)) rtc_date, -- Date placed bid went to RTC status
max(decode(bh4.bid_status,'PRESCR',bh4.created_date,null)) prescr_date, -- Date placed bid went to PRESCR status
max(decode(bh5.bid_status,'CIR',bh5.created_date,null)) iv_requested_date, -- Date placed bid went to CIR status
max(decode(bh6.bid_status,'IVINTERN',bh6.created_date,null)) iv_Delphi_date,-- Date placed bid went to IVINTERN status
max(decode(bh7.bid_status,'IVCUST',bh7.created_date,null)) iv_Cust_date, -- Date placed bid went to IVCUST status
max(decode(bh8.bid_status,'REBID',bh8.created_date,null)) iv_Rebid_date, -- Date placed bid went to REBID status
b.st_rate bid_rate -- Rate on the Bid
FROM
bid b, bid_his bh1, bid_his bh2, bid_his bh3, bid_his bh4, bid_his bh5, bid_his bh6,
bid_his bh7,bid_his bh8
WHERE ( b.bid_number = bh1.bid_number(+) ) -- To get the Date placed bid went to SC status
and ( b.bid_number = bh2.bid_number(+) ) -- To get the Date placed bid went to CP status
and ( b.bid_number = bh3.bid_number(+) ) -- To get the Date placed bid went to RTC status
and ( b.bid_number = bh4.bid_number(+) ) -- To get the Date placed bid went to PRESCR status
and ( b.bid_number = bh5.bid_number(+) ) -- To get the Date Delphi requested Interview (placed bid went to CIR status)
and ( b.bid_number = bh6.bid_number(+) ) -- To get the Date of Interview (placed bid went to IVINTERN status)
and ( b.bid_number = bh7.bid_number(+) ) -- To get the Date of Interview (placed bid went to IVCUST status)
and ( b.bid_number = bh8.bid_number(+) ) -- To get the Rebid Date (placed bid went to REBID status)
and ( b.bid_status in ('SC','CP'))
and ( bh1.bid_status in ('SC') and bh1.status_changed_flag = 'Y' )
and ( bh2.bid_status in ('CP') and bh2.status_changed_flag = 'Y' )
and ( bh3.bid_status in ('RTC','SC', 'CP') and bh3.status_changed_flag = 'Y' )
and ( bh4.bid_status in ('PRESCR','SC','CP') and bh4.status_changed_flag = 'Y' )
and ( bh5.bid_status in ('CIR','SC','CP') and bh5.status_changed_flag = 'Y' )
and ( bh6.bid_status in ('IVINTERN','SC','CP') and bh6.status_changed_flag = 'Y' )
and ( bh7.bid_status in ('IVCUST','SC','CP') and bh7.status_changed_flag = 'Y' )
and ( bh8.bid_status in ('REBID','SC','CP') and bh8.status_changed_flag = 'Y' )
GROUP BY
b.bid_number,
b.bid_received_date, -- Date placed Bid was Received
b.st_rate -- Rate on the Bid;