Query Help Urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Query Help Urgent

  1. #1
    Join Date
    Aug 2001
    Posts
    40

    Query Help Urgent

    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 and this is still running,

    I noticed one thing of all teh joins i have if run with any 6 joins it runs fine and if i add the seventh join it fails,
    is there some thing to dod with init.ora parameters
    should we have small sort area size or any other paramaters to be taken care off...
    (i checked the parameter differences between the two instances and i found that theinstanc ein which it runs has less sort area size,please advise)

    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;
    Share on Google+

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Share on Google+

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