Re: how to actually enable/disable INDEX usage...

We think we figured this out yesterday. Using 8.1.7

Try this:

ALter index my_ind unusable;
alter session set SKIP_UNUSABLE_INDEXES = true; Do inserts
alter index my_ind rebuild;

Of course anyone else that tries to access the table using the index will fail.

Let me know how it goes.

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com
Author of: 'So You Want to be an Oracle DBA?'

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:b202mh$166ui6$1_at_ID-114658.news.dfncis.de...
> Hello all,
>
> I created a batch process that first populates
> batch INSERTs several [time serie] tables and
> afterwards execute an additional batch for updating
> those tables (too inefficient doing all from the insert)
> so for achieving the UPDATEs I really need an updated
> index working on some fields. The problem is that I need
> to really disable the index before the batch INSERTs and
> enable & rebuild it just before the UPDATEs also avoiding
> an intermediate commit as side effect like e.g. when using
> the DDL "EXECUTE IMMEDIATE CREATE INDEX"
> approach...
>
> I found that the ALTER INDEX ENABLE / DISABLE can
> only be used with Intermedia/Text indexes (CONTEXT) but
> not with ordinary or FB indexes. I also tried marking the index
> as UNUSABLE but that makes the initial INSERTs to fail.
>
> Can anyone give advise?
>
> TIA,
> Best Regards,
> Giovanni
>
>
>
>
Received on Fri Feb 07 2003 - 05:45:01 CST