Oracle’s Ever Increasing Index

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles