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: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 - 04:29:52 CDT
