DEL_LF_ROWS Index Rebuild Criteria ? (Codex) May 22, 2011
Posted by Richard Foote in DEL_LF_ROWS, Indexing Myth, Oracle Indexes.19 comments
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 