Dear All,

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