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