APPEND hint and index maintenance
Date: Fri, 18 Apr 2008 03:28:06 -0700 (PDT)
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.
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 126.96.36.199
Thanks Received on Fri Apr 18 2008 - 05:28:06 CDT