Home » SQL & PL/SQL » SQL & PL/SQL » Refresh time for Materialized View  () 1 Vote
Refresh time for Materialized View [message #221555] Tue, 27 February 2007 07:38 Go to next message
lakshmijill
Messages: 1
Registered: February 2007
Junior Member

Hi,

I created one materialized View with complete refresh option.
it takes long to refresh.How can we find how much time exactly it takes to refresh.

Also what would be the View status while it is refreshing.



Thanks
Narayana
Re: Refresh time for Materialized View [message #221597 is a reply to message #221555] Tue, 27 February 2007 10:19 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
What Oracle version ?
>I created one materialized View with complete refresh option.
>it takes long to refresh.How can we find how much time exactly >it takes to refresh.

in SQL*Plus
set timing on
exec dbms_mview.refresh('owner.mview_name')

>Also what would be the View status while it is refreshing

What do you mean by view status?
In Oracle 9i complete refresh is done using truncate and insert /*+append*/
In Oracle 10g complete refresh by default is done using delete and conventional insert although you can force the previous way using atomic_refresh = false.

So in 9i you cannot access the data while it refreshes in 10g you can but the refresh may be veryyyyyyyyyy looooooong.

You can see how to overcome the 9i situation with two alternate MVs in my paper Effective search in a normalized application at http://www.gplivna.eu/papers/mat_views_search.htm

Gints Plivna
http://www.gplivna.eu

[Updated on: Tue, 27 February 2007 10:20]

Report message to a moderator

Previous Topic: Calling Function within a package
Next Topic: Passing Resultsets to Java - Urgent
Goto Forum:
  


Current Time: Sat Dec 10 05:16:53 CST 2016

Total time taken to generate the page: 0.06984 seconds