My, How You’ve Grown
Indexes are interesting objects -- they can dramatically improve performance but their management can be, well, tricky. Depending upon how data is inserted into and deleted from a table the size an index can attain could be surprising to the DBA. How can the size be surprising? Let's take an example through a number of iterations and see what Oracle does with the index, and explain why the results shouldn't be unexpected.Setting the stage we'll create a table and a primary key index, load 200000 rows, delete the existing rows and insert new keys then see how the index responds. We'll do this several times, under differing conditions, to see if the behaviour changes and, if so, why. By the end of the example we should know how index leaf blocks are used and re-used and why some dead space can remain in an index even though general wisdom says otherwise. We begin:
SQL>
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL>
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));
Table created.
SQL>
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL>
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level <= 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.
‘Magical’ Indexes
In a forum I frequent the following question was presented (I have reposted the question in its entirety here):Suppose I have a table structure like T1(C1,C2,C3,C4....).
I creates a index indx1 on (C1,C2,C3). I issue three select
statements like :
1.select * from T1 where C1=<>
2.select * from T1 where C2=<>
3.select * from T1 where C3=<>
4.select * from T1 where C1= <> and C2= <> and C3 =<>
How the optimizer will use the index for each statements.
Regards,
Sanjoy
Of course the only way to begin to answer that, since the original poster did not provide any DDL or sample data, is to create an example and execute it (we'll ignore the fact that his three questions are numbered 1 through 4). I've posted the results below, this running on an Oracle 11.1.0.6 instance; the statistics reported by autotrace have been removed as they provided no additional value to the example. Notice how the plans change as the data 'topography' changes, illustrating that the original question was ambiguous, at best:
SQL>
SQL> --
SQL> -- Suppose I have a table structure like T1(C1,C2,C3,C4....).
SQL> -- I creates a index indx1 on (C1,C2,C3). I issue three select
SQL> -- statements like :
SQL> --
SQL> -- 1.select * from T1 where C1=<>
SQL> -- 2.select * from T1 where C2=<>
SQL> -- 3.select * from T1 where C3=<>
SQL> -- 4.select * from T1 where C1= <> and C2= <> and C3 =<>
SQL> --
SQL> --
SQL> -- How the optimizer will use the index for each statements.
SQL> --
SQL> --
SQL> -- Regards,
SQL> -- Sanjoy
SQL> --
SQL>
SQL> --
SQL> -- Create the table in question
SQL> --
SQL> create table t1(
2 c1 number,
3 c2 varchar2(20),
4 c3 date,
5 c4 varchar2(10),
6 c5 number,
7 c6 number
8 );
Table created.
SQL>
SQL> --
SQL> -- Create the index specified
SQL> --
SQL> create index indx1
2 on t1(c1,c2,c3);
Index created.
SQL>
SQL> --
SQL> -- Load test data
SQL> --
SQL>
SQL> --
SQL> -- Data with unique C1, C2 and C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (i, 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 26-JAN-10 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 30-MAR-36 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 26-JAN-10 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 46 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 30-MAR-36 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, and a reasonably selective C1
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 43), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, with a cycling C1 set of values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 05-FEB-30 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 45 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with cycling C1 and C2 values and unique C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||mod(i,3), trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 28-NOV-08 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 5 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 42 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
SQL>
SQL> set autotrace off
SQL>Note that as the selectivity of the leading columns (C1 and C2) decreases the plans change from TABLE ACCESS FULL to INDEX SKIP SCAN when column C1 is not included in the where clause. Note also that the queries did NOT change and that histograms made no difference in the plans.This changes for 11.2.0.2:
SQL>
SQL>
SQL> --
SQL> -- I creates a index indx1 on (C1,C2,C3). I issue three select
SQL> -- statements like :
SQL> --
SQL> -- 1.select * from T1 where C1=<>
SQL> -- 2.select * from T1 where C2=<>
SQL> -- 3.select * from T1 where C3=<>
SQL> -- 4.select * from T1 where C1= <> and C2= <> and C3 =<>
SQL> --
SQL> --
SQL> --
SQL> --
SQL> -- Regards,
SQL> -- Sanjoy
SQL> --
SQL>
SQL> --
SQL> -- Create the table in question
SQL> --
SQL> create table t1(
2 c1 number,
3 c2 varchar2(20),
4 c3 date,
5 c4 varchar2(10),
6 c5 number,
7 c6 number
8 );
Table created.
SQL>
SQL>
SQL> --
SQL> -- Create the index specified
SQL> --
SQL> create index indx1
2 on t1(c1,c2,c3);
Index created.
SQL>
SQL>
SQL> --
SQL> -- Load test data
SQL> --
SQL>
SQL> --
SQL> -- Data with unique C1, C2 and C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (i, 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 07-MAY-13 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
739 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
59 consistent gets
0 physical reads
0 redo size
739 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
59 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 10-JUL-39 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
740 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
433 Testing record 433 07-MAY-13 Filler 433 433
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
739 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
7748 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
59 consistent gets
0 physical reads
0 redo size
739 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 11 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
59 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9993 Testing record 9993 10-JUL-39 Filler 451 363
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
740 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, and a reasonably selective C1
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 43), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 9 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
738 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 16 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 16 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
84 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
8 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 9 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
738 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
9 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 16 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 16 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
84 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, with a cycling C1 set of values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
738 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 20 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 20 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
100 consistent gets
0 physical reads
0 redo size
733 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
2 Testing record 7748 17-MAY-33 Filler 408 44
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
738 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 9 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 20 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 20 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
100 consistent gets
0 physical reads
0 redo size
733 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 46 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;
Table truncated.
SQL>
SQL> --
SQL> -- Data with cycling C1 and C2 values and unique C3 values
SQL> --
SQL> begin
2 for i in 1..10101 loop
3 insert into t1
4 values (mod(i, 3), 'Testing record '||mod(i,3), trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
733 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=433)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='Testing record 7748')
filter("C2"='Testing record 7748')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);
C1 C2 C3 C4 C5 C6
---------- -------------------- --------- ---------- ---------- ----------
0 Testing record 0 09-MAR-12 Filler 9 9
Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C3"=TRUNC(SYSDATE@!+9))
filter("C3"=TRUNC(SYSDATE@!+9))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
733 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 552572096
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 43 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
"C3"=TRUNC(SYSDATE@!+9993))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
562 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL>
Notice that in the later release INDEX SKIP SCAN is chosen by the optimizer when any indexed column other than the leading column is used in the where clause.
So how does Oracle treat a 'select * from t1 where c3 = trunc(sysdate+9)' query when the index is built on columns (c1,c2,c3)? That depends entirely upon the data and how skewed (or not) it may be. [It also depends upon the Oracle version in use, as releases after 8.1.7.4 implemented changes in the available query plans and how indexes could be used. Again note the differences between how 11.1.0.6 and 11.2.0.2 behave.] Because of deletes/inserts a plan can change even though the query has not, so there is no 'definitive' answer to the question as written. The conditions are simply too vague to produce repeatable results.
And that's a definite maybe.
How Dynamic
Passing a list of values to a function or procedure should be, well, simple, and it is, really, unless you have a dynamic list of unknown length. Simply trying to use the supplied string, as-is, can be disappointing:SQL> --
SQL> -- Let's try this the simple way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
2 return number
3 is
4 lv_ct number;
5
6 cursor get_empinfo is
7 select count(*)
8 from emp
9 where deptno in subtype_list;
10
11 begin
12
13 open get_empinfo;
14 fetch get_empinfo into lv_ct;
15 close get_empinfo;
16
17 return lv_ct;
18 end;
19 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- The function created without error
SQL> --
SQL> -- Let's see if it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;
select instring_list_test('10,20,30') from dual
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "BING.INSTRING_LIST_TEST", line 14
SQL>
SQL> --
SQL> -- That's silly, it should work ...
SQL> --
Since '10,20,30' isn't a number, and Oracle can't magically separate the individual values the function call fails. It would succeed were there one value in this dynamic list, but real-life situations usually aren't that simple and straightforward. Whatever shall we do? We need to 'get dirty' and actually code a way for Oracle to separate the values, make them numbers and populate a table, dynamically, so we can select from that table and generate a usable list. So, let's try this again and see if we can get this to do what we want:
SQL> --
SQL> -- Let's try this again
SQL> --
SQL> -- We'll create a table type first
SQL> --
SQL> create or replace type InNumTab is table of number;
2 /
Type created.
SQL>
SQL> --
SQL> -- Now we'll use that table type to massage
SQL> -- the supplied string into a usable list
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
2 return number
3 is
4 --
5 -- The parsed value
6 --
7 lv_subtyp number;
8 --
9 -- The table we'll populate
10 --
11 lv_sublist InNumTab := InNumTab();
12 --
13 -- A place for the result
14 --
15 lv_ct number;
16 --
17 -- A variable so we can 'walk' the string
18 --
19 startpos number:=1;
20 --
21 -- Record counter to extend the table
22 --
23 rec number:=1;
24
25 --
26 -- Query using the dynamic IN list
27 --
28 cursor get_empinfo (enums InNumTab) is
29 select count(*)
30 from emp
31 where deptno in (select column_value from table(cast(enums as InNumTab)));
32
33 begin
34 --
35 -- Extend the table so we can start populating it
36 --
37 lv_sublist.extend(rec);
38
39 --
40 -- 'Walk' the provided string
41 -- The loop exits when no value separator is found
42 -- We expect the value separator to be a comma
43 --
44 loop
45 exit when instr(subtype_list, ',', startpos) = 0;
46 lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,',', 1)-1);
47 lv_sublist(rec) := lv_subtyp;
48 startpos := instr(subtype_list, ',', startpos)+1;
49 rec := rec+1;
50 --
51 -- After each addition we extend the table
52 --
53 lv_sublist.extend(rec);
54 end loop;
55
56 --
57 -- We extend the table one more time to hold our last value
58 --
59 rec := rec+1;
60 lv_sublist.extend(rec);
61 lv_subtyp := substr(subtype_list, startpos);
62 lv_sublist(rec) := lv_subtyp;
63
64 --
65 -- Get the count
66 --
67 open get_empinfo(lv_sublist);
68 fetch get_empinfo into lv_ct;
69 close get_empinfo;
70
71 --
72 -- Return the value to the caller
73 --
74 return lv_ct;
75 end;
76 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;
INSTRING_LIST_TEST('10,20,30')
------------------------------
27
SQL> select instring_list_test('10,20') from dual;
INSTRING_LIST_TEST('10,20')
---------------------------
12
SQL> select instring_list_test('10') from dual;
INSTRING_LIST_TEST('10')
------------------------
3
SQL> select instring_list_test('10,20,30,40') from dual;
INSTRING_LIST_TEST('10,20,30,40')
---------------------------------
42
SQL> select instring_list_test('10,20,30,40,50,60') from dual;
INSTRING_LIST_TEST('10,20,30,40,50,60')
---------------------------------------
60
SQL> select instring_list_test('10,20,30,40,50') from dual;
INSTRING_LIST_TEST('10,20,30,40,50')
------------------------------------
54
SQL>
Notice that the length of the list is immaterial as the loop keeps running until the desired record separator is no longer present; we need to code one additional parse of the supplied string to extract the last value and place it in our dynamic table. We then use the CAST and TABLE functions when we query this 'table'; the result is a list of values, rather than the original string, which makes IN very happy indeed.So what if you don't want to always use a comma to separate your data values? Don't panic, we can re-write the function to accept a second parameter, the record separator:
SQL> --
SQL> -- We'll try this another way, passing in the desired record separator value
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
2 return number
3 is
4 --
5 -- The parsed value
6 --
7 lv_subtyp number;
8 --
9 -- The table we'll populate
10 --
11 lv_sublist InNumTab := InNumTab();
12 --
13 -- A place for the result
14 --
15 lv_ct number;
16 --
17 -- A variable so we can 'walk' the string
18 --
19 startpos number:=1;
20 --
21 -- Record counter to extend the table
22 --
23 rec number:=1;
24
25 --
26 -- Query using the dynamic IN list
27 --
28 cursor get_empinfo (enums InNumTab) is
29 select count(*)
30 from emp
31 where deptno in (select column_value from table(cast(enums as InNumTab)));
32
33 begin
34 --
35 -- Extend the table so we can start populating it
36 --
37 lv_sublist.extend(rec);
38
39 --
40 -- 'Walk' the provided string
41 --
42 -- We provide the desired record separator
43 --
44 loop
45 exit when instr(subtype_list, recsep, startpos) = 0;
46 lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
47 lv_sublist(rec) := lv_subtyp;
48 startpos := instr(subtype_list, recsep, startpos)+1;
49 rec := rec+1;
50 --
51 -- After each addition we extend the table
52 --
53 lv_sublist.extend(rec);
54 end loop;
55
56 --
57 -- We extend the table one more time to hold our last value
58 --
59 rec := rec+1;
60 lv_sublist.extend(rec);
61 lv_subtyp := substr(subtype_list, startpos);
62 lv_sublist(rec) := lv_subtyp;
63
64 --
65 -- Get the count
66 --
67 open get_empinfo(lv_sublist);
68 fetch get_empinfo into lv_ct;
69 close get_empinfo;
70
71 --
72 -- Return the value to the caller
73 --
74 return lv_ct;
75 end;
76 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;
INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
27
SQL> select instring_list_test('10;20',';') from dual;
INSTRING_LIST_TEST('10;20',';')
-------------------------------
12
SQL> select instring_list_test('10',',') from dual;
INSTRING_LIST_TEST('10',',')
----------------------------
3
SQL> select instring_list_test('10:20:30:40',':') from dual;
INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
42
SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;
INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
60
SQL> select instring_list_test('10,20,30,40,50',',') from dual;
INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
54
SQL>
And it works like a charm, returning correct values for the counts requested. Is that cool, or what?In Oracle 10g and later releases the MEMBER OF operator can be used in place of the table function used in the prior two examples:
SQL>
SQL> --
SQL> -- We'll try this yet another way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
2 return number
3 is
4 --
5 -- The parsed value
6 --
7 lv_subtyp number;
8 --
9 -- The table we'll populate
10 --
11 lv_sublist InNumTab := InNumTab();
12 --
13 -- A place for the result
14 --
15 lv_ct number;
16 --
17 -- A variable so we can 'walk' the string
18 --
19 startpos number:=1;
20 --
21 -- Record counter to extend the table
22 --
23 rec number:=1;
24
25 --
26 -- Query using the dynamic IN list
27 --
28 cursor get_empinfo (enums InNumTab) is
29 select count(*)
30 from emp
31 where deptno member of enums;
32
33 begin
34 --
35 -- Extend the table so we can start populating it
36 --
37 lv_sublist.extend(rec);
38
39 --
40 -- 'Walk' the provided string
41 --
42 -- We expect the value separator to be a comma
43 --
44 loop
45 exit when instr(subtype_list, recsep, startpos) = 0;
46 lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
47 lv_sublist(rec) := lv_subtyp;
48 startpos := instr(subtype_list, recsep, startpos)+1;
49 rec := rec+1;
50 --
51 -- After each addition we extend the table
52 --
53 lv_sublist.extend(rec);
54 end loop;
55
56 --
57 -- We extend the table one more time to hold our last value
58 --
59 rec := rec+1;
60 lv_sublist.extend(rec);
61 lv_subtyp := substr(subtype_list, startpos);
62 lv_sublist(rec) := lv_subtyp;
63
64 --
65 -- Get the count
66 --
67 open get_empinfo(lv_sublist);
68 fetch get_empinfo into lv_ct;
69 close get_empinfo;
70
71 --
72 -- Return the value to the caller
73 --
74 return lv_ct;
75 end;
76 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;
INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
27
SQL> select instring_list_test('10;20',';') from dual;
INSTRING_LIST_TEST('10;20',';')
-------------------------------
12
SQL> select instring_list_test('10',',') from dual;
INSTRING_LIST_TEST('10',',')
----------------------------
3
SQL> select instring_list_test('10:20:30:40',':') from dual;
INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
42
SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;
INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
60
SQL> select instring_list_test('10,20,30,40,50',',') from dual;
INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
54
SQL>
The problem, though simple to state, isn't quite as simple to solve, as proven above. That's because Oracle is a database, it isn't your brain, so it can't draw on prior experience and know that '10,20,30' is glorified shorthand for 10,20,30. And because it's software (really, it is) running on a computer, it does what you tell it to do, whether or not those instructions provide the output you'd intended. Since it can't think like you do you'll have to start 'thinking' like it does, and that may throw a wrench into your logical picture of the situation. Knowing what to do with that wrench is half of the battle.Passing dynamic lists to procedures and functions is possible, it just isn't as easy as you'd like to think, especially the first time through. But, knowing how to navigate such a situation makes life a bit easier in the IT realm, which may make you pretty nifty.
Of course such knowledge is no substitute for the ultimate cool of driving a Lamborghini to the dollar store ...
That’s Fetching!
Sometimes, despite the best of intentions, things don't go exactly as planned. And that's really annoying when you're writing database-centric applications and the presumptions you made regarding the data turn out to be ... wrong. One possible undesired outcome revolves around returning more rows than initially expected. And Oracle has a couple of ways to tell us that we need to re-think how our code processes that data.Let's build a couple of tables and use them to illustrate these points:
SQL> create table lotsa_data(
2 data_id number,
3 data_set number,
4 data_val varchar2(40),
5 proc_dt date
6 );
Table created.
SQL>
SQL> create table ref_data(
2 data_id number,
3 data_set number
4 );
Table created.
SQL>
So, let's now load those tables with data:
SQL> begin
2 for i in 1..10000 loop
3 insert into lotsa_data
4 values(i, mod(i, 17), 'Test data statement '||i, sysdate);
5 insert into ref_data
6 values(i, mod(i,17));
7 end loop;
8
9 commit;
10
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
Now comes the fun part: let's use a PL/SQL block to update some of the records in the LOTSA_DATA table. We'll use the RETURNING INTO clause to attempt to retrieve values from the modified records:
SQL> declare
2 dataid number;
3 dataset number;
4 dval varchar2(40);
5 begin
6 update lotsa_data
7 set data_id = data_id + 10
8 where data_set = 16
9 returning data_id, data_set, data_val into dataid, dataset, dval;
10 end;
11 /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6
SQL>
It appears we've updated far more than one row, and Oracle wasn't exactly happy about that. We expected to modify one row of data, and we updated a considerably greater number than that, so the variables which were intended to contain the returned values couldn't process that request. Oracle throws the ORA-01422 error to indicate we'd overflow the placeholders and, well, it won't allow that to happen. Can we fix this so it will work? Certainly; we'll use a collection and BULK COLLECT instead:
SQL> declare
2 type ldat_tab_typ is table of lotsa_data%rowtype index by binary_integer;
3
4 d_tab ldat_tab_typ;
5 begin
6 update lotsa_data
7 set data_id = data_id + 10
8 where data_set = 16
9 returning data_id, data_set, data_val, proc_dt bulk collect into d_tab;
10
11 for i in 1..d_tab.count loop
12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val);
13 end loop;
14 end;
15 /
26 16 Test data statement 16
43 16 Test data statement 33
60 16 Test data statement 50
77 16 Test data statement 67
94 16 Test data statement 84
111 16 Test data statement 101
128 16 Test data statement 118
145 16 Test data statement 135
162 16 Test data statement 152
179 16 Test data statement 169
349 16 Test data statement 339
366 16 Test data statement 356
383 16 Test data statement 373
400 16 Test data statement 390
417 16 Test data statement 407
434 16 Test data statement 424
451 16 Test data statement 441
468 16 Test data statement 458
485 16 Test data statement 475
502 16 Test data statement 492
519 16 Test data statement 509
536 16 Test data statement 526
553 16 Test data statement 543
570 16 Test data statement 560
587 16 Test data statement 577
604 16 Test data statement 594
621 16 Test data statement 611
638 16 Test data statement 628
655 16 Test data statement 645
672 16 Test data statement 662
689 16 Test data statement 679
706 16 Test data statement 696
723 16 Test data statement 713
740 16 Test data statement 730
757 16 Test data statement 747
[... lots more data here ...]
7591 16 Test data statement 7581
7608 16 Test data statement 7598
7625 16 Test data statement 7615
7642 16 Test data statement 7632
[... and more here ...]
9580 16 Test data statement 9570
9597 16 Test data statement 9587
9614 16 Test data statement 9604
9631 16 Test data statement 9621
9648 16 Test data statement 9638
9665 16 Test data statement 9655
9682 16 Test data statement 9672
9699 16 Test data statement 9689
9716 16 Test data statement 9706
9733 16 Test data statement 9723
9750 16 Test data statement 9740
9767 16 Test data statement 9757
9784 16 Test data statement 9774
9818 16 Test data statement 9808
9835 16 Test data statement 9825
9852 16 Test data statement 9842
9869 16 Test data statement 9859
9886 16 Test data statement 9876
9903 16 Test data statement 9893
9920 16 Test data statement 9910
9937 16 Test data statement 9927
9954 16 Test data statement 9944
9971 16 Test data statement 9961
9988 16 Test data statement 9978
10005 16 Test data statement 9995
PL/SQL procedure successfully completed.
SQL>
Voila!! No error generated, and the update succeeded. What a comforting thought. (Note that not all of the result set has been included, as it was a LONG list.)(If this error is generated by internal code accessing data dictionary views [say, when using the exp or imp utilities] it's likely that one or more views have been corrupted and need to be rebuilt. The easiest way to do that is to:
Shutdown the database
Take a cold backup
Start it in restricted mode
Run $ORACLE_HOME/rdbms/admin/catalog.sql to rebuild the data dictionary
Do not proceed until you have a good cold backup of the database in its current state, as something could go horribly wrong with the catalog rebuild requiring a restore and a call to Oracle support.
This isn't likely to occur, but stranger things have happened. If you're not comfortable with this then contact Oracle support and have them assist you in resolving the issue.)
There are occasions when Oracle errors checking for extra rows in an exact fetch and returns an ORA-01423 message. This error is the 'tip of the iceberg', as other errors which caused/contribute to this are reported. After receiving one of these errors it's necessary to check the entire error stack to reveal the underlying problem.
Of course an ORA-01422 isn't the only error Oracle can generate when there are too many rows:
SQL> select data_id, data_set, data_val, proc_dt
2 from lotsa_data
3 where data_id = (select data_id from ref_data where data_set = 16)
4 /
where data_id = (select data_id from ref_data where data_set = 16)
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
SQL>
This one is easier to fix, as it requires only a change from '=' to 'IN':
SQL> select data_id, data_set, data_val, proc_dt
2 from lotsa_data
3 where data_id IN (select data_id from ref_data where data_set = 16)
4 /
[Lots of data returned]
SQL>
It's good to know the data an application can generate, but it's also good to know how to fix coding blunders and missteps should they arise because the data didn't match the initial assumptions; someone (yes, maybe even you) may make a mistake. It's no crime, we all make them. And being able to recover from them is the key to successful application design and implementation.And that, in the vernacular of old, is 'fetching'.
Execute This!
A while back I wrote on the causes and solutions for apparently missing tables and views, but I didn't address the problem of 'missing' packages/procedures/functions. Silly me. Let's correct that.Oracle newsgroups and blogs are filled with suggestions, tips, techniques and scripts intended to help the DBA with his or her chores, and many of these offerings utilize 'standard' packages and procedures installed by Oracle at database creation. Of course some of these packages/procedures/functions aren't meant for the common, every-day user to utilize, and the privileges on those objects are limited to specific types of accounts. Some of these are even restricted to use by SYS as SYSDBA and no one else. There are many, though, that are suitable for any user to execute, provided that user has the requisite privileges. And, unfortunately, such privileges may not have been granted to the user desiring access; calling or attempting to describe such procedures/packages/functions then results in the following undesired output:
SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist
SQL>And, from a PL/SQL block you can get the following unnerving message: PLS-00201: identifier 'dbms_lock' must be declared
But, hey, you KNOW it's there, because all of these wonderful scripts can't be wrong. And they're not; the user account in use simply has not been granted execute privilege on that package. And the same rules apply here that I listed in my prior post:* the user has no execute privilege on the package/procedure/function
* a synonym is missing and the user is attempting to access the object by name
How to fix this glaring omission? Either grant execute on the desired object to the requesting user, or create a synonym to allow access by name. How can you tell which is required? If this:
SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist
SQL>and this: SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist
SQL>are the end results then the user has no execute privilege on the package/procedure/function. If, however, access by name fails: SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist
SQL>but access by owner.name succeeds: SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKNAME VARCHAR2 IN
LOCKHANDLE VARCHAR2 OUT
EXPIRATION_SECS NUMBER(38) IN DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
LOCKMODE NUMBER(38) IN
TIMEOUT NUMBER IN DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
LOCKMODE NUMBER(38) IN
TIMEOUT NUMBER IN DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
FUNCTION RELEASE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
FUNCTION REQUEST RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
LOCKMODE NUMBER(38) IN DEFAULT
TIMEOUT NUMBER(38) IN DEFAULT
RELEASE_ON_COMMIT BOOLEAN IN DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
LOCKMODE NUMBER(38) IN DEFAULT
TIMEOUT NUMBER(38) IN DEFAULT
RELEASE_ON_COMMIT BOOLEAN IN DEFAULT
PROCEDURE SLEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SECONDS NUMBER IN
SQL>then the issue is a missing synonym. Knowing the corrective action required (and, of course, taking that action) will allow the user to access the desired package/procedure/function.Knowing what packages/procedures/functions you CAN access is information which is fairly easy to obtain:
select owner, object_name
from all_objects
where object_type in ('PACKAGE','FUNCTION','PROCEDURE');
You'll get a list (possibly a LONG list) of packages, procedures and functions (and the assiciated owners) which you're allowed to execute: OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS STANDARD
SYS DBMS_STANDARD
SYS DBMS_REGISTRY
SYS DBMS_REGISTRY_SERVER
SYS XML_SCHEMA_NAME_PRESENT
SYS UTL_RAW
SYS PLITBLM
SYS SYS_STUB_FOR_PURITY_ANALYSIS
SYS PIDL
SYS DIANA
SYS DIUTIL
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS SUBPTXT2
SYS SUBPTXT
SYS DBMS_PICKLER
SYS DBMS_JAVA_TEST
SYS DBMS_SPACE_ADMIN
SYS DBMS_LOB
SYS UTL_SYS_COMPRESS
SYS UTL_TCP
SYS UTL_HTTP
SYS DBMS_TRANSACTION_INTERNAL_SYS
SYS DBMS_SQL
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS DBMS_SYS_SQL
SYS DBMS_OUTPUT
SYS DBMS_LOGSTDBY
SYS DBMS_SESSION
SYS DBMS_LOCK
SYS UTL_FILE
SYS DBMS_TYPES
SYS GETTVOID
SYS XMLSEQUENCEFROMXMLTYPE
SYS XQSEQUENCEFROMXMLTYPE
SYS XMLSEQUENCEFROMREFCURSOR
...
If the package/procedure/function is in that list, but you still can't access it by name you're simply missing a synonym. And, if it's not in that list you have no access to that object so you'll need to discuss that issue with your DBA. [ A special case can exist through PL/SQL where a user has access to a table/view/package/procedure/function by virtue of a role but PL/SQL can't 'see' it. Many packages/procedures/functions are compiled with AUTHID DEFINER (the default) and, as such, won't use privileges granted through a role. Modifying the procedure to be AUTHID CURRENT_USER can fix that problem and allow PL/SQL to traverse the entire privilege tree. If the procedure cannot be modified (such as Oracle-supplied packages, procedures and functions) the only way to 'fix' that is to have privileges directly on the object in question. If you find yourself in this boat talk with your DBA to see if he/she can accomodate you.]
I'll state again in this post that not all Oracle users are destined to access or use all of the installed packages/procedures/functions Oracle supplies. There may be very good reasons in your organization for not having access to a specific package, procedure or function, so don't be surprised if your request is met with a glorious
"Nope, sorry, can't do that."
Security is the watchword of late, and some organizations may frown upon just any user having privilege to execute certain code, because granting such access may open security holes in the database. Pete Finnigan has an excellent website listing the security issues with Oracle releases; it's worth the time to peruse his site to get a feel for what could disrupt an Oracle installation and give you a 'heads up' on why, possibly, you can't use a certain package or procedure.
It never hurts to ask. Just don't be surprised if the answer is "No" because there is probably a very good reason for that response.
