Re: "Throttling" a session's IO

On 4/2/07, Smith, Steven K - MSHA <Smith.Steven_at_dol.gov> wrote:
>
> One option:
>
> Does the data in the mview need to be accessible while it is refreshing?
> Does this mview need to be refreshed in the same refresh group as other
> mviews (data concurrency)? If the answer to these 2 questions is no,
> then you can put this mview in it's own refresh group. This would cause
> the refresh to use a 'truncate' instead of a 'delete' which doesn't
> generate undo records.

My previous post links to an Oracle-L article that does not require a separate refresh group, if such a thing is necessary. As an aside, I did not realize that putting the MV in a separate refresh group would cause a TRUNCATE rather than a DELETE.

The test I ran was a single MV, effectively in its own refresh group. On 10g, the default method to remove data from the table during a complete refresh was DELETE.

If you need access to the mview while it is refreshing, then create 2
> mviews - _1 and _2 with a synonym of the original mview to point to _1
> or _2. Refresh then on an alternating schedule and recreate the synonym
> after the refresh completes. This isn't 100% accessibility to the
> mview, but it is pretty close.
>
>

Another method that sounds very interesting was proposed by Gints Plivna, and I believe it is in production use.

http://www.gplivna.eu/papers/mat_views_search.htm#_Toc141625502

Both method require you to use some method that will avoid killing in flight queries when the data is refreshed.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l