Re: alter index ... disable ?

yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in message news:<3ebc2213_at_news.victoria.tc.ca>...
> Is there a way to temporarily disable an index so that a group of updates
> do not require the index to be unpdated until some later time?
>
> You can do this with a primary constraint index, but I don't see how to do
> this on other "ordinary" indexes.
>
> I examined the ALTER INDEX command, but it does basically the reverse of
> what I want. It stops _queries_ from using the index, but INSERT's etc
> still update the index.
>
> What I want is to stop updating the index until after a load of data has
> been added. (Obviously queries have to stop using the index, but that's
> ok).
>
> Right now I DROP the index and then CREATE it afterwards, but that means I
> have to know the columns of the index. It's easy enough to know the
> columns, but it would be even easier to simply reference the index by name
> "TURN INDEX xx OFF" and then "TURN INDEX xx BACK ON", and occasional
> changes to the indexes would be easier to manage as nothing would change
> unless you added (or removed) an index, and even then it would still be
> simpler as just the name of the index would be added or removed.
>
> Suggestions welcome.

Try this:

1. Alter index UNUSABLE
2. alter session set skip_unusable_indexes=true; (Without this, you will get errors at step 3)
3. update your table
4. Alter index rebuild
   • Jusung Yang