Re: Online Index Rebuild Tuning

As far as I remember the online rebuild does a FFS (Fast Full Scan) of the existing index' leaf blocks, then builds the new one and finally does some clever stuff before switching over (renaming the new index from a temporary segment to a permanent). So there's a lot of reading and writing involved (IO considerations) and the sort area will probably be used just as heavily as if you created a new index (but I'm not too sure about that) and finally there's of course the usual storage consideration (LMT's, etc.). That's about it, I believe.

Mogens

Mark Richard wrote:

>Folks,
>
>I know that when creating indexes a couple of settings such as
>SORT_AREA_SIZE can have a big impact on duration. What settings apply
>during online rebuilds? Are the rules the same? What tips do you have?
>
>Basically we have some very large indexes in an OLTP system (several
>indexes are across ~250m rows, several GB in physical storage) which have
>fairly low density due to deletes and updates. In looks like the time has
>come to rebuild then to gain some performance. Any other suggestions
>regarding tricks to avoid this, etc would be greatly appreciated.
>
>Thanks,
> Mark.
>
>PS: If you going to suggest things which are version specific we're
>dealing with 8.1.7.4 on Solaris.
>
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Privileged/Confidential information may be contained in this message.
> If you are not the addressee indicated in this message
> (or responsible for delivery of the message to such person),
> you may not copy or deliver this message to anyone.
>In such case, you should destroy this message and kindly notify the sender
> by reply e-mail or by telephone on (61 3) 9612-6999.
> Please advise immediately if you or your employer does not consent to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message
> that do not relate to the official business of
> Transurban City Link Ltd
> shall be understood as neither given nor endorsed by it.
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).