fast refresh for materialized views is too slow

Platform: HPUX
Oracle : Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
Area : Materialized Views

On a reporting environment with:
-star_transformation_enabled = TRUE

   sort_area_size = 104857600
-We have tables:

	FAC_CUSTOMER (facts table )
	DIM_1 (dimension 1)
	DIM_2 (dimension 2)
	...
	DIM_10 (dimension 10)

Materialized view is created on these tables to increase performance.

Steps done
1) analyze tables
2) create logs on facts table and dimensions

        WITH ROWID option
3) create indexes on m_row$$ and snaptime$$ for the facts table.

4) create materialized view log with fast refresh

	CREATE MATERIALIZED VIEW RPT.CUSTOMER_MV
	TABLESPACE RPTMVLD
	NOLOGGING
	NOCACHE
	PARALLEL
	REFRESH FAST
	  WITH ROWID
	 USING DEFAULT LOCAL ROLLBACK SEGMENT
	ENABLE QUERY REWRITE
	

5) build indexes on MV + analyze
6) analyze MV

Creation of steps 1 to 6 with 1M records in DIM_CUSTOMER and <10 records in each dimension table.
took 1h30min.

Refresh using
DBMS_SNAPSHOT.REFRESH
('CUSTOMER_MV',NULL,'LARGERBS',TRUE,FALSE,1,0,3,TRUE); and with 100,000 records(updates and inserts) in the fact table log and empty dimension logs took aroud 9hrs. I noticed that the package performs delete, inserts instead of updates even though the updated field is not the primary key.

Any ideas what could be causing this poor performance?

TIA
Michal Received on Tue Feb 10 2004 - 10:41:06 CST