Oracle-L: Re: Online Index Rebuild Tuning

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Thu, 05 Dec 2002 13:35:31 -0800
Message-ID: <F001.00513D8B.20021205133531@fatcity.com>

Connor,

That's a good point - something which I really hadn't thought about. Unfortunately many of the indexes relate to foreign keys and primary keys, which are an ever increasing value here. I've already tried rebuilding one or two small indexes and they shrunk from ~180MB to ~70MB.

Also, I have heard about compressing indexes, but it is something I have never used before. Can anyone shed some light on the topic? Are there any drawbacks (ie: reduced IO but increased processing)?

Thanks,

Mark.

                                                                                                                   
                    Connor                                                                                         
                    McDonald             To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <hamcdc_at_yahoo.       cc:                                                                       
                    co.uk>               Subject:     Re: Online Index Rebuild Tuning                              
                    Sent by:                                                                                       
                    root_at_fatcity.c                                                                                 
                    om                                                                                             
                                                                                                                   
                                                                                                                   
                    05/12/2002                                                                                     
                    20:24                                                                                          
                    Please respond                                                                                 
                    to ORACLE-L

The first question is whether you really need to rebuild them. If the indexes columns are such that the values are likely to be reused, then I wouldn't bother - since that deleted space will get reused as required.

Cheers
Connor

Mark Richard <mrichard_at_transurban.com.au> 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: Mark Richard
> INET: mrichard_at_transurban.com.au
>
> 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).
>

Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

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).




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   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: Mark Richard
  INET: mrichard_at_transurban.com.au

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).

Received on Thu Dec 05 2002 - 15:35:31 CST