Don Morrison Presentation
Thanks to Don Morrison for his presentation on Ruby and espousing one man’s love for a language.
Read MoreMy, 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 374 1
SQL>
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL>
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
2 as
3 n number;
4 m number;
5 begin
6 select min(id),max(id) into n,m from biggy;
7 for i in 1..200000 loop
8 delete from biggy where id=n+i-1;
9 insert into biggy values(m+i,'Big index test');
10 if mod(i,p_commit_after)=0 then
11 commit;
12 end if;
13 end loop;
14 commit;
15 end;
16 /
Procedure created.
SQL>
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL>
SQL> exec delete_insert_rows(1000)
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Check current index structure
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 245378 45378 495 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 45378 200000 18.49310
SQL>
SQL> --
SQL> -- Do it again
SQL> --
SQL>
SQL> exec delete_insert_rows(2000)
PL/SQL procedure successfully completed.
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 293295 93295 587 1
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 93295 200000 31.80927
SQL>
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL>
SQL> column object_id new_value objid
SQL>
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';
OBJECT_ID
----------
69706
SQL>
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL>
SQL> alter session set events 'immediate trace name treedump level &objid';
Session altered.
SQL>
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL>
SQL> exec delete_insert_rows(3000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(4000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(5000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(6000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(7000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(8000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(9000)
PL/SQL procedure successfully completed.
SQL> exec delete_insert_rows(10000)
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 3 484820 284820 1011 5
SQL>
SQL> SELECT name,
2 del_lf_rows,
3 lf_rows - del_lf_rows lf_rows_used,
4 to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
5 FROM index_stats
6 where name = upper('biggy_pk');
NAME DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK 284820 200000 58.74758
We find that the way we've deleted and added the rows has affected how the leaf blocks are managed using a standard primary key index 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 374 1
SQL>
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 374 1
SQL>
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL>
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
2 as
3 n number;
4 m number;
5 begin
6 select min(id),max(id) into n,m from biggy;
7 for i in 1..200000 loop
8 delete from biggy where id=n+i-1;
9 insert into biggy values(m+i,'Big index test');
10 if mod(i,p_commit_after)=0 then
11 commit;
12 end if;
13 end loop;
14 commit;
15 end;
16 /
Procedure created.
SQL>
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL>
SQL> exec delete_insert_rows(1000)
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Check current index structure
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 374 1
SQL>
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in 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 <= 200000;
200000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL>
SQL> analyze index biggy_pk validate structure;
Index analyzed.
SQL>
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK 2 200000 0 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.87470
SQL>
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.
You are responsible for making that feature work. Write a test. Just do it…
Today the PM of a project I am working on sent an email with a small list of issues that we needed to get resolved before shipping an early build to the customer for a weekly review. In his list of issues MY NAME was tagged next to a feature that I KNOW was working. I dev’d it, tested, saw it work.
Now the project I’m working on encourages unit tested code, which is a fantastic project to be on since I am a big proponent of Unit/Integration/Automated tests. Heck I wrote a tool to help run them easier (StatLight).
What I did.
The problem was this. I dev’d a feature out in like 5 minutes, took about 2 seconds to decide if I should write a unit test to prove my feature worked and this is where I failed. Manually verified my change checked in the production code without its test and I was hurriedly moved on to the next task.
About 20 min later I get a quick I.M. from a co-worker saying he had a small merge conflict in the file I just checked-in. Quickly told him how to get around his merge issue (not realizing after he checked in) that my “quick 5min dev task” was accidently removed in the merge.
What I SHOULD have done!
What I should have done was write the 2 lines of test code first. (you can argue test after/test first, I prefer test-first). Proven my code wasn’t working, by running the test, and then implement the 5 min feature making the test pass. Then when my co-worker ran into his merge issue.
Test would have failed telling him his merge didn’t go as planned.
This would have also avoided
- PM wouldn’t have had to discover the issue, Screenshot and write up an email.
- I wouldn’t have had to peruse source control history to understand why my “working” feature wasn’t working.
- I wouldn’t have had to willingly confess my sins in this post.
If your feature doesn’t have a coinciding automated test. How do you know it’s still working?
Happy Testing!
Read MoreStatLight v1.6 is Out
Get The Build
What is StatLight?
In short, it allows you to run your Silverlight tests on a Continuous Integration server or alongside your daily development.
Fore more information go check out the project site http://statlight.codeplex.com
Some ramblings (about the project).
It’s funny how the release cycle of this open source project has turned out. I don’t have any sort of calendar reminder that pops up saying “it’s been 4 or 5 months and time to get out a new release”. However, since I first open sourced the project I’ve put out a new release on a pretty consistent 4/5 month cycle and have done so since December 2009. It just so happens about every 4/5 months there is enough features and or fixes piled on top that It’s time to put out an “official” build. Not bad for a part time (free to the community) gig.
Thanks to the guys at CodeBetter and JetBrains for giving us a TeamCity build server, I put a build of StatLight up on their TeamCity server. This has been great for myself. When issues or feature request come in, all I have to do is dev them out, push the code out to GitHub and the TeamCity build produces an artifact that people can pull down and use/test right away.
What’s the difference between a TeamCity build and an “official” release of StatLight? To be honest, NOTHING. When I decide it’s time to release an “official” build, I head over to the TeamCity, download the latest build and throw it up on CodePlex and NuGet and call it the “official” release. I guess the biggest difference is this means I now have to go update the documentation out on the CodePlex site. I’m not particular happy with this process and hoping my new pet project using http://sphinx.pocoo.org/ to generate a CHM documentation file will help with future versioning of the tool. I’m working on a new blog post for how I got this working…
Some more ramblings (about this release)
This build comes with some pretty major internal refactorings. Finally, after 3 years, I’ve put in an IOC container (really liking TinyIOC for this task). This cleaned up quite a bit of the codebase. Introduced a couple regressions, bug the community was great in helping me figure this out and I’ve beefed up my test coverage to avoid this in the future. There is more cleanup to do in the codebase, but much of the hard work was done and it’ll be small refactorings as I see them going forward.
Some of this build’s highlights:
I’d highly recommend reading the release notes on the download page to get a full list of features/fixes. http://statlight.codeplex.com/releases/view/73869
- Support for Silverlight 5.
- Overhauled the “Continuous” mode. (Provide better U.I. and support for multiple xaps/dlls)
- Note: I was getting a bug report that it could occasionally run a xap multiple times. Hopefully it’s fixed now, but if you see it and can reproduce it. Let me know.
- MSTest TRX output format. (Note: This was requested heavily, but when it was added I didn’t receive much feedback on whether it was working or not… So if you have issues, first start a Discussion or file an Issue)
Special thanks to Bob Brumfield for fixing a number of pathing issues after a directory re-structuring I made in the project. With his fixes we were able to get Silverlight 5 support into the project sooner than later.
Sort-of known issue with this build.
Due to the large scale refactoring that happened within StatLight I wouldn’t be surprised if a few regressions were introduced. Hopefully not as I’ve setup a large amount of automated tests around the project. But It’s hard to get everything.
I’ve had a few people report an issue with symptoms similar to the following.
If you experience any sort of “Invalid or malformed application: check manifest” error. This is probably due to a StatLight web server side exception not being reported out correctly. Try downloading the latest from the TeamCity build (follow the link on the StatLight home page).
What’s to come?
If you feel brave, and clone the current codebase you could get some early access to a version I have working with the Windows Phone Emulator. This was actually an early branch of the project I created many moons ago, and one that a user forked and added a couple fixes to a while back. I’ve since merged this into the main-line of StatLight.
This early version doesn’t yet support the full gamut of what StatLight’s Silverlight version supports, but I’m hoping to get it in there. It should work with the recent builds of the Microsoft.Silverlight.Testing that work on the phone. If you’re interested in helping out here, the big reason I don’t support most everything on the phone is related to the big ol monster build script in StatLight. I have to add some magic dust to this script and make dual compile other projects for both Silverlight and the phone. The codebase itself shouldn’t have to change much, but the build scripts and integration tests will require the majority of the effort.
Read MoreNNSDG Holiday Party 2011
The NNSDG Holiday Party for 2011 was a resounding success with a fun time had by all. There was Nerdy Jeopardy where Table 3 pulled out the come from behind victory worthy of a Hollywood movie. Table 1 had a certain cheater which disqualified his entire table. Table 2 made an epic blunder with their Final Nerdy Jeopardy wager.
Would like to thank our generous sponsors:
- TekSystems who sponsored the food and kicked in some cool swag. Not only do they sponsor the Holiday Party, but they sponsor us all year and for that we thank them.
- Syncfusion who sponsored with their award-winning Essential Studio Enterprise Edition and ASP.NET MVC user interface components. This is a perpetual license with 45 days of support and updates included. Please contact Jonathan Godwin at 919.650.2908 or by email at jonathangodwin@syncfusion.com if you have any questions!
- Rylem who sponsored the party with a $100 Amazon Gift Certificate. See their Networking Flier for more information on how to get hired for the Amazon.com Prime team
- Ryan Polk who sponsored the party with a nice Zinfandel
We would like to congratulate our winners:
- Jason Jarrett won the TekSystems USB and pen thing.
- Jesse Anderson won the $100 Amazon Gift Certificate and Zinfandel.
- Natalie Malekos won the Syncfusion Essential Studio Enterprise Edition license.
NNSDG Sad to Announce the Passing of Loren Loiseau
Loren Loiseau (1964 – 2011)
Julia Jaundalderis is sad to report the death of her husband Loren Loiseau, who passed away suddenly on November 5, 2011.
Loren was 45 years old and was a loving husband and father. He spent his career as a Software Professional in the Telecommunications, Software and Gaming Industries. A man of great brilliance and integrity, he was well respected in his field.
Loren was a member of the PMI Northern Nevada Chapter and volunteered in many aspects; assisting with policy and procedures library as well as being a knowlegable speaker at several of our dinner meetings. He will be deeply missed by family, friends,and colleagues.
Loren’s Memorial service will be held Saturday, November 19, 2011 at 3:00pm at Sierra Bible Church,3195 Everett Drive, Reno NV 89503. (If sending flowers for the service, they need to arrive by 3:00pm Friday, November 18.)
Cards to the widow and daughter, Arija Loiseau, may be sent to 660 House Blend Ln, Henderson NV 89052.
Read MoreThat’s Your Problem
Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being 'locked into' a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let's look at a few problems and their solutions to see how to change the way you think about, and look at, a problem.Jonathan Lewis provides the first problem we consider, although it actually appeared in the comp.databases.oracle.server newsgroup several years ago. It's gone through several iterations since its original offering and we'll consider the most recent of those here. The problem:
Two mathematicians met at their college reunion. Wanting to keep current they started discussing their lives as only mathematicians can:
Mathematician #1: So, do you have any children?
Mathematician #2: Yes, three girls.
Mathematician #1: Wonderful! What are their ages?
Mathematician #2: I'll give you a clue: the product of their ages is 36.
Mathematician #1: Hmmm, good clue but not nearly enough information.
Mathematician #2: Well, the sum of their ages is the number of people in this room.
Mathematician #1: (After looking about the room) That's still not enough information.
Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.
Mathematician #1: I have it now -- say, are the twins identical?
Given that all of the information needed to solve the problem is in the problem, what are the ages of the three girls?
The problem seems unsolvable at first glance but there is more information available than is originally seen. Let's state what we know from the problem:
1 -- There are three girls
2 -- Their ages, multiplied together, have a product of 36
3 -- The sum of their ages is (to us, anyway) an undisclosed number
4 -- The oldest daughter has a hamster with a wooden leg
A strange collection of facts, some might say. But, looking deeper into the problem we can find some logic and answers not obvious from casual inspection. Let's start with the product of the ages:
SQL> --
SQL> -- Generate an age list for the girls
SQL> --
SQL> -- Maximum age is 36
SQL> --
SQL> with age_list as (
2 select rownum age
3 from all_objects
4 where rownum <= 36
5 )
6 select *
7 from age_list;
AGE
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
36 rows selected.
SQL>
SQL> --
SQL> -- Return only the age groupings whose product
SQL> -- is 36
SQL> --
SQL> -- Return, also, the sum of the ages
SQL> --
SQL> -- This restricts the set of values needed to
SQL> -- solve the problem
SQL> --
SQL> with age_list as (
2 select rownum age
3 from all_objects
4 where rownum <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= age1.age
15 and age3.age >= age2.age
16 and age1.age*age2.age*age3.age = 36
17 )
18 select *
19 from product_check
20 order by 1,2,3;
YOUNGEST MIDDLE OLDEST SUM PRODUCT
---------- ---------- ---------- ---------- ----------
1 1 36 38 36
1 2 18 21 36
1 3 12 16 36
1 4 9 14 36
1 6 6 13 36
2 2 9 13 36
2 3 6 11 36
3 3 4 10 36
8 rows selected.
SQL>
Notice we return not only the product of the ages but also the sums of the various combinations, as we'll need this information later on in the problem. Now another 'fact' emerges:
5 -- Knowing the sum of the ages doesn't help matters much
This reveals that there is more than one combination of ages which produce the same sum:
QL> --
SQL> -- Find, amongst the acceptable values,
SQL> -- those sets where the summed value is
SQL> -- the same
SQL> --
SQL> -- This is necessary as providing the sum
SQL> -- was of little direct help in solving the
SQL> -- problem
SQL> --
SQL> with age_list as (
2 select rownum age
3 from all_objects
4 where rownum <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= age1.age
15 and age3.age >= age2.age
16 and age1.age*age2.age*age3.age = 36
17 ),
18 summed_check as (
19 select youngest, middle, oldest, sum, product
20 from (
21 select youngest, middle, oldest, sum, product,
22 count(*) over (partition by sum) ct
23 from product_check
24 )
25 where ct > 1
26 )
27 select *
28 from summed_check;
YOUNGEST MIDDLE OLDEST SUM PRODUCT
---------- ---------- ---------- ---------- ----------
2 2 9 13 36
1 6 6 13 36
SQL>
Now we know the number of people in the room and why the sum wasn't enough information to solve the problem. The final 'nail in the coffin' (so to speak) is the owner of the hamster with the wooden leg; the problem states:
Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.
It's not the hamster, it is the fact that the oldest daughter (there's only one) exists. Knowing that last piece of information provides the final answer:
SQL>
SQL> --
SQL> -- Return the one set of values meeting all of
SQL> -- the criteria:
SQL> --
SQL> -- Product of 36
SQL> -- Sum of some unknown number
SQL> -- Oldest child exists
SQL> --
SQL> with age_list as (
2 select rownum age
3 from all_objects
4 where rownum <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= age1.age
15 and age3.age >= age2.age
16 and age1.age*age2.age*age3.age = 36
17 ),
18 summed_check as (
19 select youngest, middle, oldest, sum, product
20 from (
21 select youngest, middle, oldest, sum, product,
22 count(*) over (partition by sum) ct
23 from product_check
24 )
25 where ct > 1
26 )
27 select *
28 from summed_check
29 where oldest > middle;
YOUNGEST MIDDLE OLDEST SUM PRODUCT
---------- ---------- ---------- ---------- ----------
2 2 9 13 36
SQL>
The ages of the girls are 9, 2 and 2 which also clarifies the question of identical twins.
The problem was solved in a systematic and (to me, at least) logical way by breaking the problem down into workable pieces.
So you don't encounter such problems at college reunions or parties (what a dull life that must be); you may encounter them at work. This next problem was presented in the Oracle PL/SQL group:
Hi,
I have 3 columns of data
Column 1:subscription
Column 2: invoice number
Column 3: Service
I need to seperate the subscription types into new, renewals and additional
which is fine but the next bit i am having trouble
Each invoice number can have 1 or more service
e.g.
Invoice Number Service
123 Photocopying
123 Printing
123 Scan & Store
234 Photocopying
234 Scan & Store
345 Photocopying
345 Printing
I apply a rate for each service e.g.
photocopying = 1.5
printing = 1.7
but if Scan and store is in an invoice with photocopying we charge an extra
1.5
but if printing is a service with the scan and store a different rate
applies 1.7
so i can't just count scan and store and apply a rate i have to figure out
if it is with photocopying or with printing and then apply the rate
What I want to be able to do is creat a table with columns that calculates
this
so i get a 4 columns:
Service usage rate total
photocopying 3 1.5 4.5
Printing 2 1.7 3.4
Scan & Store 1 1.5 1.5
Scan & Store w/Print 1 1.7 1.7
The problem comes in when i'm trying to count scan and store wit/without
printing. I can't figure it out.
I import the report from an excel spreadsheet into acces and want to run a
query that does all this...
thanks in advance,
ainese
With this problem I decided to change the table a bit and add a numeric SERVICE_CD column:
SQL> Create table subscription(
2 subscr_type varchar2(15),
3 invoice number,
4 service varchar2(40),
5 service_cd number
6 );
Table created.
SQL>
SQL> insert all
2 into subscription
3 values('RENEWAL',123,'Photocopying',0)
4 into subscription
5 values('RENEWAL',123,'Printing',2)
6 into subscription
7 values('RENEWAL',123,'Scan '||chr(38)||' Store',5)
8 into subscription
9 values('ADDITIONAL',234,'Photocopying',0)
10 into subscription
11 values('ADDITIONAL',234,'Scan '||chr(38)||' Store',5)
12 into subscription
13 values('NEW',345,'Photocopying',0)
14 into subscription
15 values('NEW',345,'Printing',2)
16 select * From dual;
7 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
Using the wm_concat() function and the BITAND operator produced results that will make the final solution easier to code; using BITAND allows Oracle to generate a result based upon the sum of the SERVICE_CD values and by properly choosing those SERVICE_CD entries make it easier to isolate the various combinations:
SQL> select subscr_type, invoice, services,
2 bitand(service_cds, 0) col1,
3 bitand(service_cds, 2) col2,
4 bitand(service_cds, 7) col3
5 from
6 (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
7 from subscription
8 group by subscr_type,invoice);
SUBSCR_TYPE INVOICE SERVICES COL1 COL2 COL3
--------------- ---------- ---------------------------------------- ---------- ---------- ----------
NEW 345 Photocopying,Printing 0 2 2
RENEWAL 123 Photocopying,Printing,Scan & Store 0 2 7
ADDITIONAL 234 Photocopying,Scan & Store 0 0 5
SQL>
Knowing which BITAND results indicate which chargeable combinations allows using DECODE to produce a version of the desired results:
SQL> column services format a40
SQL> break on report skip 1
SQL> compute sum of photocopy printing scan_and_store scan_and_store_w_prt on report
SQL>
SQL> select subscr_type, invoice, services,
2 decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
3 decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
4 decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
5 decode(bitand(service_cds, 7), 7, 1.7, 0) scan_and_store_w_prt
6 from
7 (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
8 from subscription
9 group by subscr_type,invoice);
SUBSCR_TYPE INVOICE SERVICES PHOTOCOPY PRINTING SCAN_STORE SCAN_STORE_PRT
----------- ------- ---------------------------------- --------- -------- ---------- --------------
NEW 345 Photocopying,Printing 1.5 1.7 0 0
RENEWAL 123 Photocopying,Printing,Scan & Store 1.5 1.7 0 1.7
ADDITIONAL 234 Photocopying,Scan & Store 1.5 0 1.5 0
--------- -------- ---------- --------------
sum 4.5 3.4 1.5 1.7
SQL>
All services in this example are charged the appropriate rates, including the adjustments made for certain combinations of service.
One last problem is one found often on the web:
Display the second highest salary in the employee table
Display the employee id, first name, last name and salary for employees earning the second highest salary
Depending on which question is asked several solutions present themselves. The first is the 'obvious' solution:
SQL> select salary
2 from
3 (select salary from employees order by 1 desc)
4 where rownum = 2;
no rows selected
SQL>
which doesn't work because ROWNUM is never set to 1 so it can't get to 2. A modest rewrite produces:
SQL> select salary
2 from
3 (select rownum rn, salary from
4 (select salary from employees order by 1 desc))
5 where rn = 2;
SALARY
----------
17000
1 row selected.
SQL>
which still might not be the correct answer as more than one person may have the same salary, including the highest. Another rewrite, using DENSE_RANK() provides the solution:
SQL> select salary from
2 (select salary, dense_rank() over (order by salary desc) rk
3 from employees)
4 where rk=2;
SALARY
----------
17000
17000
2 rows selected.
SQL>
To produce more information the above query needs a small modification:
SQL> select employee_id, first_name, last_name, salary
2 from
3 (select employee_id, first_name, last_name, salary, rank() over (order by salary desc) rk
4 from employees)
5 where rk =2;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
101 Neena Kochhar 17000
102 Lex De Haan 17000
2 rows selected.
SQL>
RANK() and DENSE_RANK() do just what they're named -- rank the requested values -- but only DENSE_RANK() will not skip ranking numbers when duplicate values exist:
SQL> select salary, rank() over (order by salary desc) rk
2 from employees;
SALARY RK
---------- ----------
24000 1
17000 2
17000 2
14000 4
13500 5
13000 6
12000 7
12000 7
12000 7
11500 10
...
Notice that the third highest salary is ranked 4 with RANK(); not so with DENSE_RANK():
SQL> select salary, dense_rank() over (order by salary desc) rk
2 from employees;
SALARY RK
---------- ----------
24000 1
17000 2
17000 2
14000 3
13500 4
13000 5
12000 6
12000 6
12000 6
11500 7
11000 8
11000 8
11000 8
10500 9
10500 9
10000 10
10000 10
10000 10
10000 10
...
which is why DENSE_RANK() was used to solve the problem.
Problem solving, when given a little thought, isn't a terrible chore if you know how to read the problem and extract known information. From that you can eventually arrive at a solution (and, yes, multiple solutions can exist depending upon how you think about the problem). The above are examples to get you started thinking in the 'right' direction. As always, some practice at solving problems is recommended so take these problems, work them through, change data, work them through again (and you may find holes in my solutions that I didn't consider). The more you practice, the more you learn.
A train leaves station A at 3:30 PM and travels west at 50 miles per hour ...
