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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Checking the rebuildability of an index

Re: Checking the rebuildability of an index

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 07 Aug 2002 07:28:24 -0800
Message-ID: <F001.004AD888.20020807072824@fatcity.com>

If so, I've been laboring under this misconception for some time.

Time to dig into the docs. I'll be back...

Jared

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).
Received on Wed Aug 07 2002 - 10:28:24 CDT

Original text of this message

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