Refreshing materialized view

[himridul]

CREATE MATERIALIZED VIEW IND_UNMATCHED_MV
PARALLEL
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
aolindividualid
,truvuebestadressid
,infirstname
,inmiddlename
,inlastname
,rownum row_number
FROM individual
WHERE
aolindividualid NOT EXISTS (
SELECT /*+ HASH_AJ */
aolindividualid
FROM individual_tmp
) ;

DBMS_MVIEW.Refresh('IND_UNMATCHED_MV', 'C');

THIS REFRESH FUNCTION IS WORKING FINE WHEN BASE TABLE HAS 50 MILLION ROWS .

BUT REFRESHING FOR 150 MILLION OF ROWS IT'S GIVING THIS ERROR :

ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P014 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


My questions are :
1. Why it's indicating tablespace TEMP while I'm doing this materialized view in default tablespace ?

2.Does "NOT EXISTS " degrade the functionality of materialized view?

Any help will be appreciated ..

 

[sem]

1. TEMP tablespace is used for sorting that in turn is performed according to your hint.

2. Any additional condition may degrade its creation. Though once having been created it acts like an ordinary table.

Regards, Dima

http://seminihin.narod.ru

 

[DEEDS114]

Sem - Ref 186-775308

Refreshing Materialized Views

Could you tell me what size is your temporary table space.