| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Checking the rebuildability of an index
Also there is another point that the article did not talk about which the
BLEVEL or the Height of the index.
If the index had BLEVEL of 4 and rows = 1000000 and after sometime BLEVEL =
5 with rows = 2000000.
Deleting 1000000 rows will not bring BLEVEL back to 4 which means more time
is needed to traverse the tree.
Index rebuild takes care of this.
Waleed
-----Original Message-----
Sent: Tuesday, August 06, 2002 6:14 PM
To: Multiple recipients of list ORACLE-L
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
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.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 Tue Aug 06 2002 - 17:58:45 CDT
 |
 |