Home » RDBMS Server » Performance Tuning » Materialized View Refresh Performance (Oracle 10g)
Materialized View Refresh Performance [message #359638] Mon, 17 November 2008 15:01 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

I have created 12 materialized fetching the data from same table.
All the views are having same query with only different column name and Create MV script is something like this:


CREATE MATERIALIZED VIEW SUMMARY
TABLESPACE T1
   BUILD IMMEDIATE
   REFRESH COMPLETE
   START WITH SYSDATE
   NEXT  SYSDATE + 15/(24*60)
   AS   
  SELECT x.firm, x.user,
  COUNT(*),
  x.date
  FROM detail x
GROUP BY x.firm, x.user,date
/



The referesh time for all the views is 15 minutes and DETAIL table is having more than 50 Millions of records in it

Can anyone guide me what steps should i follow to improve the refresh performance of Materialized views?

Thanks and Regards,
Re: Materialized View Refresh Performance [message #359651 is a reply to message #359638] Mon, 17 November 2008 19:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You need to make it REFRESH FAST instead of REFRESH COMPLETE.

Ross Leishman
Re: Materialized View Refresh Performance [message #359908 is a reply to message #359638] Tue, 18 November 2008 13:30 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
If you need a complete refresh each time (for example the detail table moves more than 10% of its records in your refresh period), then you can investigate the following

Depending on your needs (Read the manual on these options).

You can

1. add parallel (hardware supporting), partition the mview and add parallel, create the mview with nologging.

2. when you refresh, you can have the atomic_refresh=false option which does a truncate instead of a delete on the refresh. again read the manual to see if this fits your needs.
Previous Topic: jobsq slave
Next Topic: parallel hint
Goto Forum:
  


Current Time: Wed Dec 07 12:41:39 CST 2016

Total time taken to generate the page: 0.08628 seconds