Oracle-L: Re: re Rebuilding Indexes in Oracle Apps -- an update

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Tue, 04 Nov 2003 10:04:24 -0800
Message-ID: <F001.005D58F6.20031104100424@fatcity.com>

Jared,

I don't see how index skip scans could benefit more from a rebuild than from coalesce (providing the index height remains the same). Skip scan doesn't scan the whole index like FFS does, it just does several scans for each value set in beginning of concatenated index (+some more mechanisms).

Maybe I'm missing something here, what did you have in mind?

Tanel.

If the index is based simply on the unique key, and for some reason you are using index_ffs on it, then rebuilding will cut down the number of scanned blocks.

It would be interesting to see how skip scans are affected by this as well.

Jared

       Hemant K Chitale <hkchital_at_singnet.com.sg> 
        Sent by: ml-errors_at_fatcity.com 
         11/01/2003 12:34 AM 
         Please respond to ORACLE-L 

               
                To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 
                cc:         
                Subject:        RE: re Rebuilding Indexes in Oracle Apps -- an update

Richard et al,

{for those who've been following the thread on Rebuilding Indexes ...}

I've just been reading the AskTom thread on rebuilding indexes at
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6601312252730

and picked on the important line
"Coalesce... reclaim the free space from mostly empty index leaf blocks that will not be reused otherwise due to your increasing sequence. "

Richard has also pointed COALESCE as a better option. COALESCE would be a better option than REBUILD for Indexes on monotonically increasing sequences where older values are purged periodically.

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com

  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net   --
  Author: Hemant K Chitale
   INET: hkchital_at_singnet.com.sg

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Tanel Poder
INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------