Re: APPEND hint and index maintenance
Date: Fri, 18 Apr 2008 09:33:53 -0700 (PDT)
On Apr 18, 3:28 am, Vsevolod Afanassiev <vafanass..._at_yahoo.com> 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.
> 1. Would these indexes be marked UNUSABLE by Oracle when INSERT is
> I always thought that the answer is YES, the indexes will be marked
> 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
> 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
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...
-- @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/08Received on Fri Apr 18 2008 - 11:33:53 CDT