DEL_LF_ROWS Index Rebuild Criteria ? (Codex) May 22, 2011
Posted by Richard Foote in DEL_LF_ROWS, Indexing Myth, Oracle Indexes.trackback
In the same article and book where the erroneous claims regarding the BLKS_GETS_PER_ACCESS were made, we also find the following recommendation:
“Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes“.
This is a very common claim however no matter how often it might be stated, it’s fundamentally flawed. Note My Oracle Support previously made these claims as well, such as with Metalink note 122008.1, although this has now been totally revised with the recommendation withdrawn.
It’s a flawed index rebuild criteria for two very good reasons.
1) It assumes this deleted space is somehow “wasted” or “deadwood” and needs to be removed. However, in the majority of scenarios, it’s nothing more than free space which will be subsequently reused by later inserts into the index. Therefore, basing a rebuild criteria on the percentage of deleted space will likely include indexes that will not benefit from a rebuild.
2) It assumes the DEL_LF_ROWS index statistic somehow accounts for all the “deleted” space within an index. However this statistic can significantly under-estimate the potential “wasted” space within an index. Therefore basing a rebuild criteria on the percentage of “currently marked” deleted space can possibly miss indexes that might actually benefit from a rebuild (or coalesce).
Any criteria which selects indexes that don’t need to be rebuilt but misses out on those that do, is as I say, fundamentally flawed. Basing a rebuild criteria on the percentage of currently marked deleted index entries suggests a lack of understanding of both how Oracle indexes work and the actual meaning behind DEL_LF_BLKS.
This of course has been discussed many times before, but a quick demo to illustrate. First, I’m going to create a table with 10 rows with a unique ID column.
SQL> create table bowie (id number, code number, name varchar2(30)); Table created. SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <=10; 10 rows created. SQL> commit; Commit complete. SQL> create index bowie_id_i on bowie(id); Index created.
If we now delete four of these rows:
SQL> delete bowie where id in (2,4,6,8); 4 rows deleted. SQL> commit; Commit complete. SQL> analyze index bowie_id_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_delete from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETE ------- ----------- ---------- 10 4 40
Indeed, we currently have 4 index entries marked as deleted. If we look at a dump of this index block:
Block header dump: 0x01c0521b
Object id on Block? Y
seg/obj: 0x13039 csc: 0x00.e30e53 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x1c05218 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0005.017.00004219 0x00c002b4.0a36.07 –U- 4 fsc 0x0038.00e30e79
Leaf block dump
===============
header address 220340836=0xd222264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 10
kdxcofbo 56=0x38
kdxcofeo 7916=0x1eec
kdxcoavs 7860
kdxlespl 0
kdxlende 4
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 52 13 00 00
row#1[8012] flag: —D–, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 c0 52 13 00 01
row#2[8000] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 c0 52 13 00 02
row#3[7988] flag: —D–, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 c0 52 13 00 03
row#4[7976] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 c0 52 13 00 04
row#5[7964] flag: —D–, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 c0 52 13 00 05
row#6[7952] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 c0 52 13 00 06
row#7[7940] flag: —D–, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 c0 52 13 00 07
row#8[7928] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 c0 52 13 00 08
row#9[7916] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 c0 52 13 00 09
—– end of leaf block dump —–
We note there are currently 4 rows with the delete flag set (eg. row#1[8012] flag: —D–). In order to make the transaction more efficient, Oracle doesn’t physically remove the deleted index entries but marks them as logically deleted. However, any subsequent DML within the index block will result in all the deleted entries being physically removed from the leaf block.
For example, here we insert a new index entry with a value of 42. Note this value didn’t exist previously and is outside all the current values within the index leaf block:
SQL> insert into bowie values (42, 1, 'MAJOR TOM'); 1 row created. SQL> commit; Commit complete. SQL> analyze index bowie_id_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_delete from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETE ---------- ----------- ---------- 7 0 0
Note that this has resulted in the removal of the previously logically deleted index entries. There are currently no deleted index entries within the leaf block. A block dump now clearly shows the deleted index entries are now all gone:
Block header dump: 0x01c0521b
Object id on Block? Y
seg/obj: 0x13039 csc: 0x00.e31029 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x1c05218 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0005.012.00004213 0x00c002b4.0a36.31 –U- 1 fsc 0x0000.00e3102b
Leaf block dump
===============
header address 220340836=0xd222264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 7904=0x1ee0
kdxcoavs 7902
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 52 13 00 00
row#1[8000] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 c0 52 13 00 02
row#2[7976] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 c0 52 13 00 04
row#3[7952] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 c0 52 13 00 06
row#4[7928] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 c0 52 13 00 08
row#5[7916] flag: ——, lock: 0, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 c0 52 13 00 09
row#6[7904] flag: ——, lock: 2, len=12
col 0; len 2; (2): c1 2b
col 1; len 6; (6): 01 c0 52 15 00 00
—– end of leaf block dump —–
So deleted space in most scenarios is nothing more than free space that can be cleaned out and reused by subsequent DML operations.
Lets look at another example, this time on a larger table with 1M rows with two indexes. The first index is effectively unique while the second index has 100 evenly distributed values (and so 10000 occurrences of each value):
SQL> create table radiohead (id number, code number, name varchar2(30)); Table created. SQL> insert into radiohead select rownum, mod(rownum, 100), 'OK COMPUTER' from dual connect by level <= 1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> create index radiohead_code_i on radiohead(code); Index created. SQL> create index radiohead_id_i on radiohead(id); Index created.
If we now delete 40% of the table, those with an ID up to 400000, this has 2 different effects on our indexes. On the ID index, it will result in the first 40% of leaf blocks containing nothing but deleted index entries. On the CODE index, it will result in all the leaf blocks containing approximately 40% of deleted index entries. Note this delete is being performed within a single very large logical transaction, not via lots of small discrete transactions, so that none the deleted entries are cleaned out. Note this also requires a sufficiently large buffer cache to cache all this data to prevent read operations (including the validate structure command itself) from subsequently cleaning out the deleted index entries as well via subsequent block cleanout operations.
SQL> delete radiohead where id between 1 and 400000; 400000 rows deleted. SQL> commit; Commit complete. SQL> analyze index radiohead_code_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETED -------- ----------- ----------- 1000000 400000 40 SQL> analyze index radiohead_id_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETED -------- ----------- ----------- 1000000 400000 40
So both have a “large” percentage of deleted index entries, although the distribution of such deleted entries differs between the two indexes. A rebuild criteria simply based on the percentage of deleted entries would suggest both indexes need a rebuild. However, if we were to re-insert a similar amount of data again (note, even with a new range of values for the ID column):
SQL> insert into radiohead select rownum+1000000, mod(rownum,100), 'THE KING OF LIMBS' from dual connect by level <= 400000; 400000 rows created. SQL> commit; Commit complete. SQL> analyze index radiohead_code_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETED -------- ----------- ----------- 1000002 2 .0002 SQL> analyze index radiohead_id_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETED -------- ----------- ----------- 1007633 7633 .757517866
We notice that the vast majority of the deleted entries have automatically been cleaned out, without an index rebuild in sight. In the case of the ID index with 40% of index leaf blocks containing nothing but deleted index entries, even though a different range of values were inserted, the effectively “empty” leaf blocks were recycled within the index, removing all the associated deleted index entries in the process.
In the case of the CODE index with all the leaf blocks containing approximately 40% of deleted index entries, subsequent inserts into these leaf blocks cleaned out all the deleted index entries and reused the subsequently freed space.
Once you begin to understand how Oracle reuses deleted index space and how the free space related to previously deleted entries may not actually be reflected in the DEL_LF_ROWS statistic anyways, one begins to understand why basing an index rebuild criteria on the so-called ratio of deleted index entries is so flawed.
In fact, if you want to avoid some nonsensical index rebuild criteria based on DEL_LF_ROWS, all you need to do is simply delete yet more rows, as so wonderfully here explained by Jonathan Lewis.
To illustrate, we create a table and index and populate it with 100000 rows:
SQL> create table pink_floyd (id number, code number, name varchar2(30)); Table created. SQL> insert into pink_floyd select rownum, mod(rownum, 100), 'WISH YOU WERE HERE' from dual connect by level <= 100000; 100000 rows created. SQL> commit; Commit complete. SQL> create index pink_floyd_id_i on pink_floyd(id); Index created. SQL> analyze index pink_floyd_id_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETED -------- ----------- ----------- 100000 0 0
So far, so good. Let’s now deleted 40% of the rows throughout the table:
SQL> delete pink_floyd where mod(id,100) between 20 and 29 or mod(id, 100) between 40 and 49 or mod(id, 100) between 60 and 69 or mod(id, 100) between 80 and 89; 40000 rows deleted. SQL> commit; Commit complete. SQL> analyze index pink_floyd_id_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETED -------- ----------- ----------- 100000 40000 40
Oh dear, we have an index with 40% of the rows deleted. However, rather than rebuild the index, let’s simply delete a few more rows …
SQL> delete pink_floyd where mod(id, 100) = 1; 1000 rows deleted. SQL> commit; Commit complete. SQL> analyze index pink_floyd_id_i validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats; LF_ROWS DEL_LF_ROWS PCT_DELETED -------- ----------- ----------- 60000 1000 1.66666667
What do you know, now the ratio of deleted rows is just 1.67%, no need to rebuild it now !!
If you have a rebuild criteria which selects indexes that don’t actually need to be rebuilt and it misses out on those that potentially could benefit from a rebuild, while at the same time makes indexes look less in need of a rebuild the more you actually potentially fragment the index with further deletions, well as I said at the start, such a rebuild criteria is fundamentally flawed …
Richard Foote's Oracle Blog 
Thanks for Your invaluable input.
I’ve got one quesion as out of work for now .
Is Oracle able to reuse index space when indexed column is like current date (monolitically increasing). So if we delete some old rows (past dates) and inserted new rows are with higer (more recent) dates . Does Oracle reuse space from deleted rows ?
Regards
GregG
LikeLike
Hi Greg
As Niall explained, yes providing the index blocks are entirely empty. Whether it’s a number, date, varchar2 or whatever, it makes no difference.
The monotonically increasing example, with sparse deletes, is one of the classic examples where an index rebuild (or coalesce) perhaps might be worthwhile.
LikeLike
Greg
Richard’s radiohead example illustrates the case where the old values are completely deleted. If some stragglers are left behind then you can be left with blocks that won’t be re-used. The problem is the ratio doesn’t tell you if this is the case or not, and arguably you should be investigating why the old data isn’t completely removed. I have an example at http://orawin.info/blog/2011/05/23/monotonically-increasing-indexes/ (because it’s too long for a comment.
LikeLike
Hi Niall
Thanks for your input 🙂
Your example is very similar to the one I use in my seminar. Although such examples are certainly around, my experience is that with “cheaper” storage and the increasing need for online information, organisations are more and more reluctant to delete “stuff” these days, with logical deletes common or with older partitions made read-only. At my current site, we very rarely perform much physical deletes and when we do, it’s usually a known process and so any index impact can be easily identified.
A couple of comments even in the case when there are actual sparse deletes on monotonically increasing values:
1) Whether one needs to rebuild or not also depends on the activity on the table because if queries are only focused on the more recent data where the index is nice and compact with the older stuff only accessed in reports and long running batch processes, then performance is still not going to be impact (you just waste a bit of storage here and there)
2) Even if performance is impacted, unless the sparse deletes impacts a large percentage of the index (25%+) which is very very rare, then a coalesce (or shrink if possible) would be a far more efficient and less resource intensive option than a rebuild and do just as good a job.
LikeLike
Thank You.
GregG
LikeLike
Richard, Is there anymore explanation please on this bit from Jonathan’s blog entry about DEL_LF_ROWS that you’ve referred to ….
“……….. when I deleted a few more entries my second transaction wiped the deleted entries from any leaf blocks it visited, tidying the blocks (with a “heap block compress”) before marking a few more rows as deleted.”
Thanks.
LikeLike
Hi PD
When you delete more values from a block with a subsequent transaction, Oracle will not only change the block by marking these additional rows as deleted but will also do “housekeeping” and clean out any previously deleted index entries.
LikeLike
Hi Richard,
You said:
In order to make the transaction more efficient, Oracle doesn’t physically remove the deleted index entries but marks them as logically deleted. However, any subsequent DML within the index block will result in all the deleted entries being physically removed from the leaf block.
So is it really eficient? Probably only the previous transaction was efficient but the cost of that efficiency is paid by the subsequent DML – isnt it? On the contrary, had it been a huge delete, the user would already expect it to take considerable time but Oracle would return quicker whereas for a subsequent DML for which the user expected a faster response would take longer because it has to do the housekeeping.
This is not to argue about anything but to understand the correct perspective.
Thanks.
LikeLike
test
LikeLike
Hi PC
It’s more efficient for the transaction doing the deletion/updating and if it’s doing a substantial amount of work, it would be substantially more efficient.
Yes, subsequent DML’s might have to do more work but it’s also possible there might not be any subsequent DML and it’s quite likely any subsequent DML will only have to clean out small portions of the previous large deletion and so have a negligible impact.
It’s the premise of much of Oracle processing (such as delayed block cleanouts), don’t do now what you can delay until later so that it might never have to be done or at least the work shared by many more subsequent processes.
LikeLike
Another aspect to when the work gets done is that by leaving the index entries in place but marked as deleted, it’s much easier for other transactions to deal with read-consistency, and easier for this transaction to work out the “rollback;”. Of the two, allowing other concurrent sessions to do read-consistency efficiently is probably the more important target.
LikeLike
Sorry for the previous post.
How may I log in to your blog to have access to your demos?
LikeLike
Hi Jacek
There seems to be a problem with all the demos saved as simple text files as these are no longer supported by WordPress.
I’m in the process of replacing them with PDF which are supported but it will take me some time to complete the conversions and have them uploaded.
LikeLike
“but it will take me some time to complete the conversions and have them uploaded” – reminds me of Tanel Poder’s statement: “I plan to fix the broken links some time between now and my retirement.”
LikeLike
[…] value for the indexed column, or to some extent, fast full index scans and full index scans (reference reference2 page […]
LikeLike
Excellent post. I have done my own much simpler version at the URL below. I think it was the only time when rebuilding an index made any difference!
LikeLike
Hi Andrew
Yes, if you perform a fast full index scan and can reduce the index by half, then it will clearly make a difference. That said, when I see a fast full index scan, I see a SQL tuning opportunity 🙂
LikeLike
Dear Richard,
Thank you for the advice. I’m relatively new to this and didn’t know that fast full index scans might be bad. Click on the link if you like simple chess problems – they are my latest venture!
Kind regards,
Andrew
LikeLike
[…] https://richardfoote.wordpress.com/2011/05/22/del_lf_rows-index-rebuild-criteria-codex/ […]
LikeLike