Indexes are interesting objects — they can dramatically improve performance but their management can be, well, tricky. Depending upon how data is inserted into and deleted from a table the size an index can attain could be surprising to the DBA. How can the size be surprising? Let’s take an example through a number of iterations and see what Oracle does with the index, and explain why the results shouldn’t be unexpected.
Setting the stage, we’ll create a table and a primary key index, load 200000 rows, delete the existing rows and insert new keys then see how the index responds. We’ll do this several times, under differing conditions, to see if the behavior changes and, if so, why. By the end of the example we should know how index leaf blocks are used and re-used and why some dead space can remain in an index even though general wisdom says otherwise. We begin:
SQL>
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL>
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));
Table created.
SQL>
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL>
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 374 1
SQL>
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL>
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
2 as
3 n number;
4 m number;
5 begin
6 select min(id),max(id) into n,m from biggy;
7 for i in 1..200000 loop
8 delete from biggy where id=n+i-1;
9 insert into biggy values(m+i,'Big index test');
10 if mod(i,p_commit_after)=0 then
11 commit;
12 end if;
13 end loop;
14 commit;
15 end;
16 /
Procedure created.
SQL>
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL>
SQL> exec delete_insert_rows(1000)
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Check current index structure
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 245378 45378 495 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 45378 200000 18.49310
SQL>
SQL> --
SQL> -- Do it again
SQL> --
SQL>
SQL> exec delete_insert_rows(2000)
PL/SQL procedure successfully completed.
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 293295 93295 587 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 93295 200000 31.80927
SQL>
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL>
SQL> column object_id new_value objid
SQL>
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';
OBJECT_ID
----------
69706
SQL>
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL>
SQL> alter session set events 'immediate trace name treedump level &objid';
Session altered.
SQL>
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL>
SQL> exec delete_insert_rows(3000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(4000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(5000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(6000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(7000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(8000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(9000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(10000)
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 3 484820 284820 1011 5
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 284820 200000 58.74758
We find that the way we’ve deleted and added the rows has affected how the leaf blocks are managed using a standard primary key index. Let’s reverse the index and try this exercise again, reporting only the final outcome:
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 210249 10249 523 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 10249 200000 4.87470
Notice that the index hasn’t grown as it did in the prior example due in part to the reverse-key index which, allows new rows to use leaf blocks vacated by the deletion of rows with lower key values. Fewer populated branch blocks remain having unusable deleted leaf blocks so the index does not increase in size as rapidly. Does ASSM affect this? The next pass through these woods will use a tablespace with manual segment space management along with the reverse-key index; again only the final outcome is reported:
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 210249 10249 523 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 10249 200000 4.87470
SQL>
Manual segment space management doesn’t appear to help the situation. Another thought on mitigating this behavior is to set session_cached_cursors to 0; let’s see what that does:
SQL>
SQL> --
SQL> -- Set session_cached_cursors to 0
SQL> --
SQL> -- May improve the situation further
SQL> --
SQL>
SQL> alter session set session_cached_cursors = 0;
Session altered.
SQL>
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 237132 37132 495 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 37132 200000 15.65879
SQL>
This puts us back to where we were at the beginning — straight primary key index — so setting session_cached_cursors doesn’t appear to do any good. It may help with the reverse-key index:
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 210249 10249 523 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 10249 200000 4.87470
SQL>
That setting doesn’t appear to help in either situation. The reverse-key index, from the testing shown, is the only one of the listed fixes that actually provides any benefit.
So, what happened? The initial pass, with the standard index, causes Oracle to wait to reuse empty leaf blocks until the branch block they are attached to is empty; since we deleted the smallest available key then inserted a new largest key, the leaf block released could not immediately be reused as the branch block still had leaf blocks attached to it. Somewhere around the middle of the whole delete/insert process the leaf blocks we released at the beginning of the process were finally available for reuse. Reversing the key on the primary key index allowed reuse of the leaf blocks by the new keys since, in reverse order, they could ‘fit in’ to the key order of the index. Manual segment space management didn’t do much to improve this nor did setting session_cached_cursors to 0.
Of course the ideal method is to delete the rows in batches with the intent of freeing the branch block so the empty leaf blocks can be reused but piecemeal deletes and inserts can and will happen in OLTP systems so such a scenario can be repeated in a running production database. An interesting side note on this is that primary key indexes aren’t usually rebuilt as reverse-key indexes unless block contention is high for the index, yet that action can also dramatically reduce the number of empty leaf blocks in the index after rows are deleted. It may be worth considering the use of a reverse-key primary key index to keep the index size ‘reasonable’.
It may be a rare occurrence to have an ever-increasing index even though volumes of data have been deleted but knowing what to do to help correct the situation may prove invaluable should the situation arise. In my opinion it’s better to know something you may not need rather than need something you do not know.
