I face problem with materilzed view via temproary tablespace segments
It expand my temproary tablespace to become very huge 30+Gb and
once i tried to make join with another materilzed view the query run very slowly
Can any one help in what i need to do
regards
This is example of my main materilized view
CREATE OR REPLACE VIEW brands_trans_all_v (
fldmainid,
fldstore,
fldstoreheading1,
alu,
supitemno,
description,
flddocdate,
fldvoucomm1,
qtysold,
total_sold,
qtyrcvd,
total_rcvd,
qtytrnsfrd,
intransfred,
outtransfered,
total_trnsfrd,
intrnsf_value,
outtrnsf_value )
AS
select /*+ ALL_ROWS */ fldmainid, fldStore, fldStoreHeading1, NFLDALU ALU,NFLDDESC1 SupItemNo,nflddesc2 Description,flddocdate, FLDVOUCOMM1,0 QtySold,0 TOTAL_SOLD, sum(decode(fldvoutype, 'Receive', nflditmrcvd, -nflditmrcvd)) QtyRcvd, sum(decode(fldvoutype, 'Receive', nfldExtPrc, -nfldExtPrc)) Total_Rcvd, 0 QtyTrnsfrd,0 INTransfred ,0 OutTransfered ,0 Total_Trnsfrd,0 INTRNSF_VALUE,0 OUTTRNSF_VALUE from tblfrmrvouchers where fldmark='1' and fldvoustatus in('Regular', 'Changed') and fldvoutype in ('Receive','Return') group by fldmainid, fldStore, fldStoreHeading1, NFLDDESC1,nflddesc2,flddocdate,NFLDDESC3,NFLDDCSNAME,NFLDALU ,NFLDATTR ,NFLDSIZE , nfldDCS,FLDVOUCOMM1
union all
select /*+ ALL_ROWS */ fldmainid, fldStore, fldStoreHeading1,NFLDALU , NFLDDESC1 ,nflddesc2 ,flddocdate,FLDSLIPCOMM1,0,0,0,0,sum(decode(fldSlipType, 'In Slip', nfldqty, -nfldQty)),sum(decode(fldSlipType, 'In Slip', nfldqty, 0)) ,sum(decode(fldSlipType, 'Out Slip', nfldqty, 0)) , sum(decode(fldSlipType, 'In Slip', nfldExtPrc,-nfldExtPrc)),sum(decode(fldSlipType, 'In Slip', nfldExtPrc, 0)) ,sum(decode(fldSlipType, 'Out Slip', nfldExtPrc)) from tblslips where fldmark=1 and fldslipstatus in ('Regular','Changed') group by fldmainid, fldStore, fldStoreHeading1, NFLDDESC1,nflddesc2,flddocdate,NFLDALU,FLDSLIPCOMM1
union all
select /*+ ALL_ROWS */ Fldmainid,fldStore, fldStoreHeading1,NFLDALU ALU,NFLDDESC1 SupItemNo,nflddesc2 Description, flddocdate, FLDINVCCOMMENT1,sum(decode(fldinvchdrrcpttype, 'Sale', nfldqty, -nfldQty)) , sum(decode(fldinvchdrrcpttype, 'Sale', NFLDEXTORIGPRC ,-NFLDEXTORIGPRC)) , 0 ,0 , 0 ,0 ,0,0,0,0 from tblinvoices where fldmark='1' and fldinvchdrrcptstatus in ('Regular', 'Changed') and fldinvchdrrcpttype in ('Sale','Return') group by fldmainid, fldStore, fldStoreHeading1,NFLDDESC1,nflddesc2,flddocdate,NFLDALU , nfldDCS,FLDINVCCOMMENT1
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
That's very little data, you shouldn't be experiencing such an ordeal.
Just to see if we can lure Oracle to do something different please try altering a little your session environment by adding commands below before your CREATE MATERIALIZED VIEW command...
alter session set db_file_multiblock_read_count = 64;
alter session set sort_area_retained_size = 250331648;
alter session set sort_area_size = 250331648;
alter session set sort_direct_writes = true;
if it doesn't work, you can try to pre-build your MVIEW table.
1- Create table brands_trans_all_v with all columns you need.
2- Refurbish you mview creation script to insert into table brands_trans_all_v all the data you want to have in your MView
3- Issue a create materialized view brands_trans_all_v on prebuilt table... command
At this point you should be the happy owner of mview brands_trans_all_v
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Reply With Quote
Bookmarks