I am trying to figure out a query that will tell me which indexes need to be rebuilt based on how many deleted rows are present in the index. From what I have read, I need to use the INDEX_STATS view after the index as been analyzed.
However, I am running into inconsistencies in the data being reported by INDEX_STATS. For example:
ops$jeffh@ibdev1.us> drop table jh_index_stat
2 /
At this point, The data looks right. However, when more data gets put into the table and subsequently deleted, things get all screwed up...
ops$jeffh@ibdev1.us> insert into test1 select rownum+40000, column_name from all_tab_columns
2 /
Since all the entries in this index are unique and distinct, I expected 53024 for LF_ROWS and 26900 for the DEL_LF_ROWS for the third entry. What am I misunderstanding?
Well, I better make this one count, then , so here we go:
You start with 0, add 26,512 rows, hence 26,512 leaf rows
...so far, so good
You delete 13450 of them, hence 13,450 deleted leaf rows.
...still on track
Now.... you add back in another 26,512 rows, thereby expecting a total of 53,024 leaf rows, *but* you already deleted a bunch, so what if the index re-uses those deleted entries?
Assume, for example, that out of 13,450 empty leaf rows sitting around, say, oh, 9732 of them got re-used.
That leaves 26512 - 9732 or 16780 new leaf rows getting inserted:
26512 + 16780 = 43,292. Spiffy, eh?
Now, as we said, 9732 of those rows got re-used. Now, we delete a total of 13,450 rows again. 9732 of those rows occupied the same space as previously-deleted rows, so we only actually deleted 13450 - 9732 or 3718 *new* rows from the index. This brings our *total* of deleted rows to 13450 + 3718, or 17168.
Phew, gotta dot those i's when answering one for the man
Originally posted by chrisrlong Now.... you add back in another 26,512 rows, thereby expecting a total of 53,024 leaf rows, *but* you already deleted a bunch, so what if the index re-uses those deleted entries?
Assume, for example, that out of 13,450 empty leaf rows sitting around, say, oh, 9732 of them got re-used.
This is the part I don't understand. Since my index entries are all unique, I would expect that the index to re-use an entry UNLESS it had the exact same key value. For example, if I inserted a row with an ID=1, and deleted it, I would not expect that index row to be used again if I inserted ID=2. It should only get re-used if I insert another row with ID=1. (According to "Oracle Performance Tuning Tips & Techniques, Richard Niemiec" at least.)
I buy your explanation, I just don't understand why the index rows would get re-used.
Unfortunately, I do not have a solid answer for that, but I have a theory, so let's run with it.
I just performed your same test with slightly different results. After the second set of insertions, only a few hundred 'leaf rows' were 're-used'. I then performed the same steps a couple more times with similar results. I then inserted a new set, deleted the entire set, then re-inserted the entire set again (with higher ids, of course). In that case, almost *all* the leaf rows were re-used.
Given all that, my theory is this: I have a much bigger block size than you do - mine is 16K. (genetics - it's not your fault )
Given that, when I delete the 'semi-random' records, it is very rare that I delete *all* the records from an entire index block. You, on the other hand (so my theory goes) have a smaller block size, so you have more blocks that have *all* their entries deleted. It would seem that deleting all the entries in the index block will put that block back on the free list within the index to be subsequently re-used on the next insert. Therefore, its corresponding 'leaf rows' also get 're-used' or subtracted from the total during that insert.
As you pointed out, it is not possible to actually re-use a row in an index unless you are actually re-inserting the exact same value. However, once the block is empty, the block, and its accompanying count of deleted rows *can* be 're-used' in a fashion.
del_lf_rows or the deleted rows still in leaf blocks, if there are no rows in a leaf block, that leaf block is released and put under free list for leaf block splits.Other wise that block is not released.
del_lf_rows does not count the rows of the leaf blocks for which the whole leaf block becomes empty.
If u see ur stats, lf_rows and br_rows.
lf_rows according to u should have been total leaf rows inserted (minus some br_rows) but it is not so , so total rows allocated for leaf shows some of the leaf blocks did get full empty and thus space reused.
Originally posted by chrisrlong As you pointed out, it is not possible to actually re-use a row in an index unless you are actually re-inserting the exact same value. However, once the block is empty, the block, and its accompanying count of deleted rows *can* be 're-used' in a fashion.
True, I am working with a 2K block size (Don't laugh, I inherited it). I can agree with that theory. I didn't really think about what happens when the DB block becomes free. If the DB block becomes free and gets placed back on the free list, it looks like the corresponding data wouldn't get reported in the del_lf_rows because it's not really there anyway.
Thanks for the help. As always, it's appreciated...
Reply With Quote
Bookmarks