Re: APPEND hint and index maintenance

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org