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

Giovanni,
we did discover it does not work with Unique or Primary key indexes, but on regular indexes it works great.

Steve

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:b20cgf$17kd00$1_at_ID-114658.news.dfncis.de...
> Hello Stephen,
>
> It works great... ;-)
>
> Thank you very much!!
>
> Best Regards,
> Giovanni
>
> "Steve Ashmore" <sashmore_at_neonramp.com> wrote in message
> news:v47721a7kis004_at_corp.supernews.com...
> > 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 Sat Feb 08 2003 - 05:04:15 CST