At The Touch Of A Button

It intrigues me that some DBAs can be lost without tools like Oracle Enterprise Manager or TOAD, so much so that they can’t complete a task without a GUI. What makes this even more disconcerting is these DBAs can execute tasks that they may be unable to complete absent such tools. If what the tool does ‘behind the scenes’ is a mystery to the users it stands to reason that a user, using a GUI, could do some damage to a database by executing misunderstood tasks simply by pressing ‘buttons’.

When I started as a DBA [the earth’s crust was still cooling and dirt didn’t yet have its official name] there was the command line. That was it. Nothing else. No GUI, no OEM, no slick and nifty applications coded to make DBA life easier. A database was managed at the SQL> or SVRMGR> prompt (depending upon what needed to be done). Pretty graphs didn’t exist, alerts didn’t get generated unless the DBA wrote a script and scheduled it through cron (or the Windows scheduler) to check the database for space or memory or process count and send an email to the DBA should any of the acceptable criteria be violated. Yes, it was a hard life for a DBA, with all of that scripting and manual labor [typing is such back-breaking work]. A DBA had to know what commands did what and when to use them. A DBA also had to know where to excavate performance data, storage numbers, memory usage and user activity from the data dictionary by actually using the manuals and looking things up. Now tools like OEM, TOAD and others make it easy for someone to be a DBA by making most tasks as easy as ‘point and click’, which is a real disservice to the modern DBA, in my opinion.

What if other, daily tasks were modified so that even the uneducated could perform them? Would anyone want someone behind the wheel of a car who didn’t have any instruction at all in how to drive or operate the vehicle? Would anyone want a carpenter, plumber, electrician or mechanic performing any work with the newest power tools but having absolutely no idea how to operate them safely and properly? Clearly no one would want a surgeon operating with the latest gadgets but absent a medical degree. Yet, this is what allows people to be DBAs in the modern world — no knowledge of the intricacies of the database they manage, no knowledge of the commands necessary to perform basic functions such as adding a datafile to a tablespace, resizing a datafile in a database, adding a user account, creating roles, granting roles — the list can go on. Sit them in front of a GUI tool and explain the basics to them (“navigate here, press this button”) and they’re immediately DBAs. The prospect is disturbing.

It’s my privilege to know a number of really good DBAs in this world, DBAs who do know how to create a database, turn on and off archivelogging, restore and recover a database from a reliable backup, how to take reliable backups and do it all from the command line interface. These same DBAs use OEM, RMAN and TOAD to make their lives a bit easier, and I do the same thing so I see no issue with that. I also know (and know of) some DBAs who can’t do the job without OEM or TOAD — I’ve been told this in several interviews I’ve held when looking for additional DBA resources. Some of the most basic questions weren’t answered satisfactorily as I was given step-by-step directions on how to navigate to the page where that particular button resides instead of being told the commands necessary to complete the task in question. In an emergency situation OEM or TOAD may not be available and DBAs who don’t know the command line may be looking for another employer.

It’s my belief that enterprises who train DBAs need to concentrate not only on the tools but on the basic knowledge as well, educating their students not only in OEM but in how to go about managing a database absent those nifty tools. Understanding how the tool works only makes for better DBAs and frees them from being tethered to a graphical user interface, an interface they are dependent upon to perform the most basic and mundane of DBA tasks.

Education and training are demanded by society for teachers, doctors, lawyers, dentists, even insurance agents (not to disparage insurance agents). Why the industry doesn’t demand the same of DBAs is a mystery. [Certification, in many cases, is a requirement on the resume but ‘brain dumps’ and courses exist to ‘train’ those uneducated in the chosen DBMS so such ‘credentials’ can be acquired absent any real work experience. Many of these courses are centered around GUI management tools; sadly the underlying framework is glossed over in deference to learning to navigate the chosen graphical interface. Such an environment produces, in the Oracle arena, Oracle Certified Professionals completely absent any professional experience.] Yes, experience counts but if that experience is nothing more than a set of rote instructions on how to navigate a GUI tool how much worth does it bring to the employer? Not much, really.

Database administration is a respected profession, and most DBAs in the workforce are qualified and capable. Occasionally a few get through who meet the description I’ve given here. It’s those few I write about, and ask that they further their education and learn how their chosen DBMS works and how, in an emergency, to do their jobs absent any flashy graphic tools.

I don’t believe that’s too much to ask.

My, How You’ve Grown

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 behaviour 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  
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 as the index has more than doubled in size; a good portion of that storage is empty leaf blocks that could not be reused by the new keys because they are out of the key range for the associated branch block. Let’s reverse the index and try this exercise again:

  
SQL> 
SQL> --
SQL> -- Drop the existing table and index then recreate
SQL> -- 
SQL> 
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
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        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
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     202645        2645        512          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                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*), min(id), max(id) from biggy;

  COUNT(*)    MIN(ID)    MAX(ID)
---------- ---------- ----------
    200000     400001     600000

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     205095        5095        512          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                              5095       200000    2.48421

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
----------
     69709

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                                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> 

The reverse-key index made a substantial impact on the number of empty leaf blocks left unused in the index. Let’t see if manual segment space management causes a change in behaviour over the prior tests using ASSM:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
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 when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
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     201000        1000        379          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                              1000       200000     .49751

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     202000        2000        382          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                              2000       200000     .99010

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
----------
     69711

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> -- 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     210000       10000        422          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                             10000       200000    4.76190

SQL> 

We note that manual segment space management didn’t really make much of a difference in the storage with the standard primary key index; the index is still much larger than it ‘should’ be. Will it make a difference with the reverse-key version? Let’s test it and see:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
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        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
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     202645        2645        512          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                              2645       200000    1.30524

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     205095        5095        512          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                              5095       200000    2.48421

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
----------
     69714

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> -- 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> 

No real difference seen with the reverse-key index so the segment space management (at least in 11gR2) isn’t a factor. One thought on mitigating this behaviour 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> -- Go through the whole process one more time
SQL> -- with and without a reverse-key index
SQL> -- and using ASSM then MSSM to see which is better
SQL> --
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  
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     225031       25031        440          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                             25031       200000   11.12336

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     228120       28120        446          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                             28120       200000   12.32685

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
----------
     69716

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> -- 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> 

Nothing changed for the standard index; let’s again test the reverse-key index and see what that produces:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
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        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
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     202645        2645        512          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                              2645       200000    1.30524

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     205095        5095        512          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                              5095       200000    2.48421

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
----------
     69718

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> -- 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> 

Again no change is realized; we try again with manual segment space management and collect the results:

  
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
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 when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
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     201000        1000        379          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                              1000       200000     .49751

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     202000        2000        382          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                              2000       200000     .99010

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
----------
     69720

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> -- 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     210000       10000        422          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                             10000       200000    4.76190

SQL> 

Again, no change. One more time with the reverse-key index, using manual segment space management:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
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        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
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     202645        2645        512          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                              2645       200000    1.30524

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     205061        5061        512          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                              5061       200000    2.46805

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
----------
     69722

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> -- 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.87470SQL> 

Notice the setting didn’t do much of anything to improve the situation.

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.

My two cents.