Re: using "alter index xxx unusable"

From: Michael Peel <peelm_at_NOSPAMaccuread.com>
Date: Tue, 13 Dec 2005 15:14:30 GMT
Message-ID: <Xns972B9B411761DpeelmNOSPAMaccureadc@194.6.79.66>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:dnmnqr$lhu$1_at_nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com:

> "Michael Peel" <peelm_at_NOSPAMaccuread.com> wrote in message 
> news:Xns972B925A72A1BpeelmNOSPAMaccureadc_at_194.6.79.66...

>> Hi all,
>>
>> First time caller here, so a bit nervous.
>>
>> Oracle 10.1.0.4 (HPUX)
>>
>> I'm building a consolidated table of about 30 million rows for a
>> reporting type instance.
>>
>> I'm using the following steps.
>>
>> Mark indexes on consol table unused.
>> Setting skip_unusable_indexes = true for the session
>> Truncating the consol table.
>> Insert using append.
>> Rebuild indexes.
>>
>> What I've found is that after the truncate table statement the
>> indexes on the table become valid. I therefore have to re-issue the
>> alter index unusable statement, before the insert.
>>
>> Not that it's a major issue, just wondered if this was normal....
>>
>> Cheers,
>>
>> Michael Peel
>> D.B.A
>> I.S. Services
>> AccuRead LTD
>>

> 
> 
> This behaviour is a little surprising, but totally normal.
> As you truncate a table, you automatically truncate
> all the indexes . So the indexes are suddenly self-consistent
> again which makes the indexes usable again.  (Oracle is
> not aware of the fact that you might really want them to
> be unusable - normally it's an unfortunate accident).
> 
> However, you probably don't need to set the indexes
> unusable to benefit from the insert /*+ append */, as
> the indexes are maintained with an array insert after the
> insert is complete.  (There are some funny side-issues
> with enabled foreign key constraints, though, but too
> many if/then/buts to put into a short note).
> 

Jonathan,

Thanks for the speedy response…

Makes sense.

I’ll do some benchmarking around marking the indexes as unsable, and not. I was getting fairly large redo with the insert and the indexes in tact, but I guess this redo is now associated with the index rebuild. I’ll mark the build as nologging and see what happens.

Thanks again.

M. Received on Tue Dec 13 2005 - 09:14:30 CST