Materialized View Please Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Materialized View Please Help

  1. #1
    Join Date
    Aug 2001
    Posts
    40

    Materialized View Please Help

    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;

  2. #2
    Join Date
    Aug 2001
    Posts
    40

    Re: Materialized View Please Help

    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;

Posting Permissions

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



Click Here to Expand Forum to Full Width