Re: materialized view indexes

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:SMAWb.1041$_g.347_at_twister.socal.rr.com...
> In the "Oracle9i Data Warehousing Guide" it says "If insufficient
> temporary space is available to rebuild the indexes, then you must
> explicitly drop each index or mark it UNUSABLE prior to performing the
> refresh operation."
>
> However, when I try to set the indexes UNUSABLE, Oracle automatically
> reenables them when I do the materialized view refresh. How can I get
> Oracle to refresh the materialized view and leave the indexes for me to
> rebuild afterwards? Basically, I want to bypass the undo for the index
> build and it appears that a normal complete refresh doesn't do that.
>
>
> Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
> With the Partitioning and Oracle Data Mining options
> JServer Release 9.2.0.2.0 - Production
>
> --
> Richard Kuhler
>

Richard,

This is an expected behaviour.
The reason this happens is because a complete refresh first issues a "truncate table <mview> purge snapshot log" statement.

A truncate table in turn validates all unusable indexes because a truncated index cannot be unusable (it having no keys ... so no reason why it should be unusable).

This is a behavior you cannot work around by marking indexes unusable.
You probably only have the option of dropping and recreating these indexes.

Anurag Received on Wed Feb 11 2004 - 22:02:31 CST