Re: Fragmentation in Oracle
Date: 1997/11/09
Message-ID: <3465cfee.179321340_at_newshost>
On Sat, 8 Nov 1997 22:21:49 -0000, "Dave S." <dsingh_at_tgtsolutions.com> wrote:
>As DBA's we deal with what you call 'atypical' code. We must be aware of how
>Oracle handles storage items.
>
Agreed Dave, but lets look at what you said "Index Space is never reused". thats wrong. In *most* cases it is reused. In your case, it results in a 2x overhead (delete every row, insert every row). If this is what people you work with do, teach them about TRUNCATE. It'll solve the problem. Also, look at what they are doing and see if a commit wouldn't break their application. Think of the enourmous amount of time and resources you could save not feeling that you have to rebuild indexes all the time.
>Believe me Tom, this is not atypical, I have seen on almost all installs
>that I have worked on, specially if the code was developed by numerous
>developers.
>
>Further, if the delete is part of the transaction, AND, the code is to
>rollback the transaction, then this is the only way to deal with this, I
>mean, if the process is suppose to delete from the table and insert into the
>same table, some developers may want to rollback the ALL transactions that
>are involved. They may not be deleting / inserting 30,000 rows and as a
>result, the size of the rollback space used may not be critical.
>
Again, this 2x issue is only when you delete EVERY row in the table, INSERT every row in the table. If you delete a couple, insert a couple in the table and commit -- the index will not show this behaviour.
A modified test1, in one transaction delete 100 rows, insert 100 rows and then commit. The space management is still very efficient. The results were:
BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS EBYTES EBLOCKS -------- -------- -------- -------- -------- ----------- -------- --------
19 1,894 17 16 1 0 38,912 19
21 2,038 19 18 1 144 43,008 21
21 2,038 19 18 1 144 43,008 21
... above row repeated forever .....
so, the index grew to accomidate the extra 100 rows but then never grew again.
so yes, if you rebuild an index it will in general be smaller then the existing index but -- it will always grow back to what it was before the index rebuild because of the way you use it (in other words, except for really fragmented indexes -- test3 indexes where you randomly delete records for example -- rebuilding won't really buy you anything as the index will quickly come to be that large again. You haven't reclaimed any space except for the period of time immediately after the index build. If you use the data in the same way tomorrow as you did yesterday -- the index is just going to get that size again)
>Please, let's end this item, I think Doug's input explains this to my
>complete satisfaction. Thanks Doug.
>
Lets look at those statements then
<cut from the referenced post>
As far as I'm aware, index space can only be re-used if appropriate space is available in the correct area of the ordered sequence set. You are correct that the statement 'Index space IS NEVER REUSED' is incorrect. It is never reused in the sense that PCTUSED is inappropriate. An index block can only be reused for index entries which should reside in that block as a result of their key value. However, indexes do sometime grow infintely large over time even if rows are deleted, if the index is on an ever-increasing key, for example. There are situations where the *only* way to re-claim space in and index is to rebuild it, or at least that's what we used to teach people on Oracle's own Oracle7 Database Administration courses in the UK. </cut>
1- index space can only be re-used if appropriate space is available in the correct area of the order sequence set.
Well, I think test1 showed this wrong. I used a monotomically increasing number to insert into my indexed column. the index *never* grew. why? because I was inserting on the right hand side of the tree while at the same time deleting on the left hand side. As index blocks emptied on the left hand side, they were being reused on the right hand side. So the above statement needs to be modified to say something like:
index space can be re-used if values that fall in the range of data on that block are inserted or if the index entries on a block all become deleted.
2- 'Index space is never reused' is incorrect -- thats a correct statement. As is the comment about pctuserd.
3-However indexes do sometimes grow infinitely large over time even if rows are deleted, if the index is on an ever-increasing key for example.
I half agree with this statement. the way it is worded however would have people believe (as they appear to do) that if I use an ever increasing key -- I will have an index that grows and grows and thats that. But thats not the case. Test1 I ran (a typical situation -- add new data, delete old data) shows that space is resused very efficiently. Test3 shows that for the same set of data but deleteing randomly (not the oldest) the index grows over time. It is important to understand the differences between the time cases and when you need to rebuild and not. Both cases however did reuse index blocks, just differently and at different rate.
>Regards and thanks to all.
>
>Dave
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Nov 09 1997 - 00:00:00 CET