Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: using "alter index xxx unusable"

Re: using "alter index xxx unusable"

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 13 Dec 2005 15:02:51 +0000 (UTC)
Message-ID: <dnmnqr$lhu$1@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).

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005
Received on Tue Dec 13 2005 - 09:02:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US