Re: fast refresh for materialized views is too slow

mikeeria_at_interia.pl (Michael) wrote in message news:<875e251b.0402100841.76356393_at_posting.google.com>...
> 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

Ive found that materialized views are not as useful as alot of people think. Query Re-write has alot of limitations(MVs with joins dont work), and refreshing is really slow. Ive often found it faster to just drop the table, do create table as no logging, then re-create the indexes at the same time by running them as jobs.

I still use materialized views, but I've found CTAS to be perfectly acceptable in many cases. Received on Wed Feb 11 2004 - 07:41:21 CST