Re: APPEND hint and index maintenance

I did and the index stayed valid. This is strange as when I used SQL*Loader the index was unusable
during the load.

On Apr 19, 2:23 am, DA Morgan <damor..._at_psoug.org> wrote:
> Vsevolod Afanassiev wrote:
> > Sorry for asking what seems to be an obvious question:
>
> > Records are inserted into a table A using
> > INSERT /*+ APPEND */ into A SELECT * FROM B;
> > There are indexes on table A, and these indexes are in VALID state
> > (DBA_INDEXES.STATUS = 'VALID')
> > before the insert.
>
> > Questions:
> > 1. Would these indexes be marked UNUSABLE by Oracle when INSERT is
> > running?
> > I always thought that the answer is YES, the indexes will be marked
> > unusable
> > at least for some time when INSERT is running. For example, if table A
> > was empty
> > before INSERT, 1 million records were inserted, and INSERT took 1 min
> > then at least for the last 10 - 15 seconds the indexes will be marked
> > unusable.
> > When INSERT completes the indexes will become VALID again
> > (unless there was some problem during insert).
>
> > 2. Does INSERT with APPEND hint lock table A in exclusive mode
> > preventing any other INSERT/UPDATE/DELETE?
> > I always thought that the answer is YES.
>
> > This is for 9.2.0.8
>
> > Thanks
>
> The answer to your question is something you can determine with a
> few minutes of coding and a few minutes of testing. I would recommend
> you do so.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
Received on Fri Apr 18 2008 - 16:57:44 CDT