Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View Build Time
Materialized View Build Time [message #248868] Mon, 02 July 2007 09:11 Go to next message
Messages: 3
Registered: July 2007
Junior Member

I have a number of materialized views on a fact with just a few hundred million rows (it will have more than 15 billion in the future). The time it takes to completely build the views, is about 11 hours for each (we then use PCT and FAST refresh for new data). My question is: if we ever change a detail table (add a column for example) then the materialized views will need to be rebuilt with a complete refresh, which will be a very long time. Is there a way of constructing the views such that the build time will be much less and query rewrite will use them?


Re: Materialized View Build Time [message #249035 is a reply to message #248868] Tue, 03 July 2007 02:13 Go to previous messageGo to next message
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

A materialized view is the best way of achieving the needful.
But i don't think your case hold good.
Re: Materialized View Build Time [message #249087 is a reply to message #249035] Tue, 03 July 2007 06:02 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just because you create a Fast (even PCT) refresh MV, doesn't mean it will actually be fast to refresh.

The SQL that Oracle generates to refresh an MV can be quite complex. You may need to create indexes on the MV, the source tables (and even the MV logs!) that are not useful to other queries.

When you perform a refresh, trace the results. Run them through TK*Prof to find out what SQLs Oracle is performing and then create indexes to tune those statements. Also consider whether Oracle is making a good decision to refresh with DML vs Partition maintenance - there can be some difficulty in getting a PCT refresh MV to actually refresh with truncate/insert partition.

Also, Fast refreshes work with delete/insert, not update. This can wreak havoc with index space utilisation, which in turn affects the performance of future refreshes. See the tip here on determining whether to rebuild indexes.

Ross Leishman

[Updated on: Tue, 03 July 2007 06:05]

Report message to a moderator

Previous Topic: O/S Executable file
Next Topic: Optimizing help appreciated
Goto Forum:

Current Time: Fri Aug 18 01:50:09 CDT 2017

Total time taken to generate the page: 0.21408 seconds