Materilized View % Temproary Tablespace

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

How large are your base tables and how large are your MVs?
Oracle version?
SGA/PGA sizes?

Thanks PAVB for your update

tblinvoices 1230940 ROWS
tblitems 1107630 ROWS
tblslips 242240 ROWS
tblfrmrvouchers 127330 ROWS

SGA SIZE 1745955136 Bytes
Oracle Enterprise Edition 9.2.0.1.0

Can you post the execution plans for each select statement?

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