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.