-
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;
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|