Re: Checking the rebuildability of an index

After pondering this a bit while trying to find info in the docs, I recalled a little more on this.

Conner and Mike ( and Tom ) are correct in that slots can be reused in an index block, provided that block is still on the free list.

Once an index block is full ( only PCTFREE space left ), it is taken off of the free list.

Subsequent deletes affecting that block do not put it back on the freelist, unless the delete(s) completely empty the block.

Makes me wonder why indexes work this way, and don't use PCTUSED like table blocks. Maybe in some testing long ago, this caused index blocks to play ping-pong with the index freelists for some reason. I wonder if anyone at Oracle has revisited this in recent years.

Back to the docs to try and back this up.

Jared ( who should think longer before he types )

On Wednesday 07 August 2002 02:29, Connor McDonald wrote:
> Tom is correct...I like using the telephone book as an
> analogy
>
> If you erase some entries on a page for a some people
> called "Smith", then you can reuse those slots for any
> surname that still keeps that page (and hence the
> telephone book) in the correct order. Obviously a
> "Jones" could not go in there (since it belongs with
> the pages with "J", but any people with a last name
> that is "near" that of Smith will be able to. It will
> not matter if the page in question still had other
> entries on it.
>
> hth
> connor
>
> --- Jared.Still_at_radisys.com wrote: > Hmmm.
>
> > Though Tom Kyte is usually right on the money, the
> > following statement
> > from the article is questionable:
> >
> > ==============================
> > But, the index on ename -- that's a little more
> > chaotic. Say we have
> > employees:
> >
> > Bill
> > Bob
> > Mary
> >
> > Say we fire Bob, now we have:
> >
> > Bill
> > X-Bob (deleted entry)
> > Mary
> > Should we rebuild an index like that? Probably NOT,
> > the reason -- we are
> > going
> > to hire Hillary:
> >
> > Bill
> > Hillary
> > Mary
> >
> > Hillary will reuse Bobs slot. There is no reason to
> > get rid of that slot
> > --
> > only to have to rebuild it again later. If the data
> > you are indexing has
> > a good
> > probability of reusing a slot like that --
> > rebuilding can actually slow
> > you down
> > over time (it takes time to split a block -- with
> > empty entries -- the
> > chances
> > we need to split are reduced. If you rebuild --
> > they'll go up -- you
> > unsplit
> > everything, got rid of the space. Now we have to
> > re-split again -- every
> > time
> > you rebuild). I find most indexes hit a steady
> > state -- if you rebuild
> > them,
> > they'll go back into that state over time. So
> > rebuilding is somewhat self
> >
> > defeating in that case.
> >
> > ==============================
> >
> > Hillary may reuse Bob's slot in a table data block,
> > if the deletion of Bob
> > puts
> > the block back on the freelist.
> >
> > Indexes do not use the same concept of a freelist,
> > which is why they do
> > not
> > have a PCTUSED parameter. Index blocks are not
> > reused until the entire
> > block is empty. The only time that Hillary's record
> > could reuse Bob's
> > slot
> > in the ENAME index is if Bob's record was the only
> > index entry in that
> > block
> > of the ENAME index.
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > "Jesse, Rich" <Rich.Jesse_at_qtiworld.com>
> > Sent by: root_at_fatcity.com
> > 08/06/2002 12:48 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: RE: Checking the
> > rebuildability of an index
> >
> >
> > Interesting. I just checked AskTom and sure
> > enough...
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:1134696::NO::F4950_P8_DISPLAYID
>,
>
> F4950_P8_CRITERIA:2290062993260,%7Bindex%7D%20and%20%7Brebuild%7D
>
> > (paste the mangled URL parts together)
> >
> > It's not exactly "never", but an "it depends". I
> > just ran into an "it
> > depends" situation today. How timely!
> >
> > I think I'll just gather stats on leaf block density
> > and deleted leaf rows
> > for now, but I would think that the height of the
> > index would have a
> > direct
> > effect on the performance of a query using that
> > index.
> >
> > Thanks!
> >
> > Rich Jesse System/Database
> > Administrator
> > Rich.Jesse_at_qtiworld.com Quad/Tech
> > International, Sussex, WI
> > USA
> >
> > > -----Original Message-----
> > > From: Post, Ethan [mailto:Ethan.Post_at_ps.net]
> > > Sent: Tuesday, August 06, 2002 1:38 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: Checking the rebuildability of an
> >
> > index
> >
> > > If memory serves correct (and it might not) both
> >
> > Tom Kyte and
> >
> > > Jonathan Lewis
> > > have stated that in most circumstances rebuilding
> >
> > indexes is
> >
> > > useless. I
> > > think Tom may have said he could count the # of
> >
> > times he had
> >
> > > to rebuild an
> > > index on one hand (something like that). Thus, I
> >
> > have
> >
> > > stopped worrying
> > > about this one so much and will only entertain
> >
> > rebuilds if I
> >
> > > start to see
> > > performance issues. Of course if you have the
> >
> > time and need
> >
> > > to look busy
> > > keep rebuilding those indexes :)
> > >
> > > By the way the reason it is useless is not that
> >
> > there is
> >
> > > never a performance
> > > gain but that most indexes that need to be rebuild
> >
> > quickly
> >
> > > degrade again and
> > > the period that one experiences a performance gain
> >
> > is
> >
> > > minimal. Operating
> > > off a poor memory here so hopefully I have not
> >
> > misrepresented anyone.
> >
> > > Ethan Post
> > > perotdba (AIM), epost1 (Yahoo)
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Jesse, Rich
> > INET: Rich.Jesse_at_qtiworld.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
>
> --------------------------------------------------------------------
>
> > 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.com
> > --
> > Author:
> > INET: Jared.Still_at_radisys.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
>
> --------------------------------------------------------------------
>
> > 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
>
> "Remember amateurs built the ark - Professionals built the Titanic"
>
> __________________________________________________
> 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: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).