Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Checking the rebuildability of an index
Hmmm.
Though Tom Kyte is usually right on the money, the following statement from the article is questionable:
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 USAReceived on Tue Aug 06 2002 - 17:13:33 CDT
> -----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).
![]() |
![]() |