APPEND hint and index maintenance

[Vsevolod Afanassiev]

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

[DA Morgan]

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

[joel garry]

Search asktom with the terms:
"Append hint" index unusable

It seems redo is generated so the index changes can be rolled back,
rather than making them unusable. You can mark them unusable as a
trick to make things go faster, then rebuild afterwards.

A bit of googling for the second question finds things like this:
http://www.freelists.org/archives/oracle-l/06-2004/msg01433.html

Of course, proper testing, while time-consuming, can make these things
more clear. Wish I had time...

jg
--
@home.com is bogus.
"We were at a Drive-in theater operator convention in Vegas or some
place, and a couple came up to us and said, 'Hey, you guys bought us a
new house!'" - Tommy Chong, Cheech & Chong reunion radio interview,
4/18/08

[Vsevolod Afanassiev]

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

> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -