Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them a collection/varray is defined as "an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection." The definition seems simple enough but it may be deceptively so. To make matters even more confusing to use a collection you must create a database type for it to reference; a varray requires a type as well but that type can be declared in the PL/SQL block. To clear the air a bit let's go through some examples of defining and using collections and varrays: The first example uses a collection to store vendor ids and then process them for a report. The code builds two 'tables' and compares the contents of them by loading collections and comparing one collection to the other; output is displayed for the conditions of the two tables being equal and the two tables not being equal:

SQL> 
SQL> set serveroutput on size 1000000
SQL> 
SQL> CREATE OR REPLACE type integer_table is table of integer;
  2  /

Type created.

SQL> 
SQL> DECLARE
  2  
  3  
  4   vendor_key_table   integer_table;
  5   vendor_key_table2   integer_table;
  6   CURSOR tst
  7   IS
  8      SELECT   purch_order, SUM (dollars),
  9        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 10          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 11           FROM DUAL
 12         UNION ALL
 13         SELECT 1 purch_order, 8 dollars, 123452 vendor_key
 14           FROM DUAL
 15         UNION ALL
 16         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 17           FROM DUAL
 18         UNION ALL
 19         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 20           FROM DUAL
 21         UNION ALL
 22         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 23           FROM DUAL)
 24      GROUP BY purch_order;
 25  
 26  
 27    CURSOR tst2
 28   IS
 29    SELECT purch_order, SUM (dollars),
 30        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 31          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 32           FROM DUAL
 33         UNION ALL
 34         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 35           FROM DUAL
 36         UNION ALL
 37         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 38           FROM DUAL
 39         UNION ALL
 40         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 41           FROM DUAL)
 42      GROUP BY purch_order;
 43   v_purch_order    NUMBER;
 44   v_dollars    NUMBER;
 45  
 46  
 47   mystr     VARCHAR2 (4000);
 48  
 49  
 50   v_purch_order2     NUMBER;
 51   v_dollars2     NUMBER;
 52  
 53  
 54   mystr2      VARCHAR2 (4000);
 55  BEGIN
 56   OPEN tst;
 57   open tst2;
 58  
 59  
 60   LOOP
 61      mystr := NULL;
 62      mystr2 := NULL;
 63  
 64  
 65      FETCH tst
 66       INTO v_purch_order, v_dollars, vendor_key_table;
 67  
 68  
 69      FETCH tst2
 70       INTO v_purch_order2, v_dollars2, vendor_key_table2;
 71  
 72  
 73      IF tst%NOTFOUND
 74      THEN
 75         EXIT;
 76      END IF;
 77  
 78  
 79      if vendor_key_table = vendor_key_table2 then
 80          dbms_output.put_line('equal');
 81      else
 82          dbms_output.put_line(' not equal');
 83      end if;
 84  
 85  
 86      -- loop through the collection and build a string so that
 87      -- we can display it and prove that it works
 88      FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
 89       FROM TABLE (vendor_key_table))
 90      LOOP
 91         mystr := mystr || ',' || cur1.vendor_key;
 92         -- /* based on the value of the sum, you can do something with each detail*/
 93         -- if v_dollars > 12 then
 94         --   UPDATE VENDOR SET paid_status = 'P' where vendor_key = cur1.vendor_key;
 95         -- end if;
 96      END LOOP;
 97  
 98  
 99      DBMS_OUTPUT.put_line (   'Purchace Order-> '
100       || TO_CHAR (v_purch_order)
101       || ' dollar total-> '
102       || TO_CHAR (v_dollars)
103       || ' vendorkey list-> '
104       || SUBSTR (mystr, 2));
105  
106  
107      -- loop throught the collection and build a string so that
108      -- we can display it and prove that it works
109      FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
110       FROM TABLE (vendor_key_table2))
111      LOOP
112         mystr2 := mystr2 || ',' || cur2.vendor_key;
113      END LOOP;
114  
115  
116      DBMS_OUTPUT.put_line (   'Purchace Order-> '
117       || TO_CHAR (v_purch_order2)
118       || ' dollar total-> '
119       || TO_CHAR (v_dollars2)
120       || ' vendorkey list-> '
121       || SUBSTR (mystr2, 2));
122  
123  
124   END LOOP;
125   CLOSE tst;
126   CLOSE tst2;
127  END;
128  /
not equal
Purchace Order-> 1   dollar total-> 11   vendorkey list-> 435235,123452
Purchace Order-> 1   dollar total-> 3   vendorkey list-> 435235
equal
Purchace Order-> 2   dollar total-> 16   vendorkey list-> 433738,387118,383738
Purchace Order-> 2   dollar total-> 16   vendorkey list-> 383738,387118,433738

PL/SQL procedure successfully completed.

SQL> 

Comparing the collections rather than looping through each table makes the work much easier to complete. Notice we only needed one type created; the same type satisfied the conditions for both collection tables.

The next example shows how things can go astray with the bulk collect operation when the limit does not evenly divide the result set. In the first part of the example we use the well-known 'exit when cursor%notfound;' directive with less than stellar results (we miss inserting 5 records into the second table); the second part of the example shows how to properly implement an exit from a bulk collect operation; this one uses a varray:

 
SQL> 
SQL> set echo on linesize 150 trimspool on
SQL> 
SQL> create table emp_test as select * From emp where 0=1;

Table created.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when get_emp_data%notfound;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000

9 rows selected.

SQL> 
SQL> truncate table emp_test;

Table truncated.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when emp_c.count = 0;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000
      7844 TURNER                                          1500
      7876 ADAMS                                           1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES                                            950
      7902 FORD                                            3000
      7934 MILLER                                          1300

14 rows selected.

SQL> 

What happened in the first part? Since the limit was more than the number of remaining records the NOTFOUND indicator was set at the end of the fetch. We had 5 records left to process in the varray but the 'exit when cursor%notfound;' statement terminated the loop BEFORE we could get the remainng 5 records inserted into our table, thus they were lost. Using the 'exit when collection.count = 0;' construct prevents us from missing records since the count was greater than 0 even when the NOTFOUND indicator was set. This allows us to process the remaining records in the varray before exiting the loop. [Yes, the exit could be coded at the end of the loop rather than the beginning and the 'exit when cursor%NOTFOUND;' would process the remaing records but that, to me, defeats the purpose of the conditional exit. As I see it we want to exit the loop when no more work is to be done, not look for partial sets of data to apply then exit before the next (unsuccessful) fetch.]

Our next example does two things: loads data using bulk collect into a varray then uses the FORALL loop construct to quickly process the collection and insert the data into a staging table. The second part is a bit contrived as it uses a collection to process deletes from a table -- deletes that could have easily been executed with a single SQL statement -- but it does show the power of using collections and varrays:

SQL> 
SQL> set echo on timing on
SQL> 
SQL> create table stage_data(
  2        uname varchar2(30),
  3        ujob varchar2(20),
  4        usal number
  5  );

Table created.

SQL> 
SQL> begin
  2        for i in 1..1000000 loop
  3        insert into stage_data
  4        values ('Blorp'||i, 'Job'||i, 1200*(mod(i,3)));
  5        end loop;
  6  
  7        commit;
  8  
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> create table forall_load(
  2        uname varchar2(30),
  3        ujob varchar2(20),
  4        usal number
  5  );

Table created.

SQL> 
SQL> create table forall_load2(
  2        uname varchar2(30),
  3        ujob varchar2(20),
  4        usal number
  5  );

Table created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE fast_way IS
  2  
  3  TYPE myarray IS TABLE OF stage_data%ROWTYPE;
  4  l_data myarray;
  5  
  6  CURSOR r IS
  7  SELECT *
  8  FROM stage_data;
  9  
 10  BEGIN
 11   OPEN r;
 12   LOOP
 13     FETCH r BULK COLLECT INTO l_data LIMIT 1000;
 14  
 15     FORALL i IN 1..l_data.COUNT
 16        INSERT INTO forall_load VALUES l_data(i);
 17     FORALL i IN 1..l_data.COUNT
 18        INSERT INTO forall_load2 VALUES l_data(i);
 19  
 20     EXIT WHEN l_data.count=0;
 21    END LOOP;
 22    COMMIT;
 23    CLOSE r;
 24  END fast_way;
 25  /

Procedure created.

SQL> 
SQL> exec fast_way;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from forall_load where uname like '%99999%';

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp99999                     Job99999                      0
Blorp199999                    Job199999                  1200
Blorp299999                    Job299999                  2400
Blorp399999                    Job399999                     0
Blorp499999                    Job499999                  1200
Blorp599999                    Job599999                  2400
Blorp699999                    Job699999                     0
Blorp799999                    Job799999                  1200
Blorp899999                    Job899999                  2400
Blorp999990                    Job999990                     0
Blorp999991                    Job999991                  1200

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp999992                    Job999992                  2400
Blorp999993                    Job999993                     0
Blorp999994                    Job999994                  1200
Blorp999995                    Job999995                  2400
Blorp999996                    Job999996                     0
Blorp999997                    Job999997                  1200
Blorp999998                    Job999998                  2400
Blorp999999                    Job999999                     0

19 rows selected.

SQL> select * from forall_load2 where uname like '%99999%';

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp99999                     Job99999                      0
Blorp199999                    Job199999                  1200
Blorp299999                    Job299999                  2400
Blorp399999                    Job399999                     0
Blorp499999                    Job499999                  1200
Blorp599999                    Job599999                  2400
Blorp699999                    Job699999                     0
Blorp799999                    Job799999                  1200
Blorp899999                    Job899999                  2400
Blorp999990                    Job999990                     0
Blorp999991                    Job999991                  1200

UNAME                          UJOB                       USAL
------------------------------ -------------------- ----------
Blorp999992                    Job999992                  2400
Blorp999993                    Job999993                     0
Blorp999994                    Job999994                  1200
Blorp999995                    Job999995                  2400
Blorp999996                    Job999996                     0
Blorp999997                    Job999997                  1200
Blorp999998                    Job999998                  2400
Blorp999999                    Job999999                     0

19 rows selected.

SQL> 

Trust me that the data loads took very little time to process. Here is the contrived part, but it is still a good example of the power of using collections:

SQL> CREATE OR REPLACE PROCEDURE del_rows IS
  2  
  3  TYPE myarray IS TABLE OF stage_data.uname%TYPE;
  4  l_data myarray;
  5  
  6  CURSOR r IS
  7  SELECT uname
  8  FROM stage_data
  9  where uname like '%9999%';
 10  
 11  BEGIN
 12   OPEN r;
 13   LOOP
 14     FETCH r BULK COLLECT INTO l_data LIMIT 1000;
 15  
 16     FORALL i IN 1..l_data.COUNT
 17        delete from forall_load where uname = l_data(i);
 18     FORALL i IN 1..l_data.COUNT
 19        delete from forall_load2 where uname = l_data(i);
 20  
 21     EXIT WHEN l_data.count=0;
 22    END LOOP;
 23    COMMIT;
 24    CLOSE r;
 25  END del_rows;
 26  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> exec del_rows;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from forall_load where uname like '%99999%';

no rows selected

SQL> select * from forall_load2 where uname like '%99999%';

no rows selected

SQL> 

Collections can be a real timesaver for bulk processng of data; they may not be applicable in every sitution but when the conditions are right they can make your job so much easier. Master collections and varrays and you may be able to amaze your friends.

Collections, anyone?

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

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

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

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

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

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

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

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

Indexes are interesting objects -- they can dramatically improve performance but their management can be, well, tricky. Depending upon how data is inserted into and deleted from a table the size an index can attain could be surprising to the DBA. How can the size be surprising? Let's take an example through a number of iterations and see what Oracle does with the index, and explain why the results shouldn't be unexpected.

Setting the stage we'll create a table and a primary key index, load 200000 rows, delete the existing rows and insert new keys then see how the index responds. We'll do this several times, under differing conditions, to see if the behaviour changes and, if so, why. By the end of the example we should know how index leaf blocks are used and re-used and why some dead space can remain in an index even though general wisdom says otherwise. We begin:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     245378       45378        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             45378       200000   18.49310

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

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     293295       93295        587          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             93295       200000   31.80927

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69706

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                3     484820      284820       1011          5

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                            284820       200000   58.74758

We find that the way we've deleted and added the rows has affected how the leaf blocks are managed using a standard primary key index as the index has more than doubled in size; a good portion of that storage is empty leaf blocks that could not be reused by the new keys because they are out of the key range for the associated branch block. Let's reverse the index and try this exercise again:

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

Table dropped.

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

Table created.

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

Index created.

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

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

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

PL/SQL procedure successfully completed.

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

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

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69709

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

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

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

Table created.

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

Index created.

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

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

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

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69711

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

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

  

SQL> drop table biggy purge;

Table dropped.

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

Table created.

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

Index created.

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

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

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

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69714

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

No real difference seen with the reverse-key index so the segment space management (at least in 11gR2) isn't a factor. One thought on mitigating this behaviour is to set session_cached_cursors to 0; let's see what that does:

  
SQL> 
SQL> --
SQL> -- Set session_cached_cursors to 0
SQL> --
SQL> -- May improve the situation further
SQL> --
SQL> 
SQL> alter session set session_cached_cursors = 0;

Session altered.

SQL> 
SQL> --
SQL> -- Go through the whole process one more time
SQL> -- with and without a reverse-key index
SQL> -- and using ASSM then MSSM to see which is better
SQL> --
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     225031       25031        440          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             25031       200000   11.12336

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

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     228120       28120        446          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             28120       200000   12.32685

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69716

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     237132       37132        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             37132       200000   15.65879

SQL> 

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

  

SQL> drop table biggy purge;

Table dropped.

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

Table created.

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

Index created.

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

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

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

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69718

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

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

  
SQL> drop table biggy purge;

Table dropped.

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

Table created.

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

Index created.

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

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

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

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69720

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

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

  

SQL> drop table biggy purge;

Table dropped.

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

Table created.

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

Index created.

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

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

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

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205061        5061        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5061       200000    2.46805

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69722

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.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.

Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being 'locked into' a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let's look at a few problems and their solutions to see how to change the way you think about, and look at, a problem.

Jonathan Lewis provides the first problem we consider, although it actually appeared in the comp.databases.oracle.server newsgroup several years ago. It's gone through several iterations since its original offering and we'll consider the most recent of those here. The problem:

Two mathematicians met at their college reunion. Wanting to keep current they started discussing their lives as only mathematicians can:

Mathematician #1: So, do you have any children?
Mathematician #2: Yes, three girls.
Mathematician #1: Wonderful! What are their ages?
Mathematician #2: I'll give you a clue: the product of their ages is 36.
Mathematician #1: Hmmm, good clue but not nearly enough information.
Mathematician #2: Well, the sum of their ages is the number of people in this room.
Mathematician #1: (After looking about the room) That's still not enough information.
Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.
Mathematician #1: I have it now -- say, are the twins identical?

Given that all of the information needed to solve the problem is in the problem, what are the ages of the three girls?

The problem seems unsolvable at first glance but there is more information available than is originally seen. Let's state what we know from the problem:

1 -- There are three girls
2 -- Their ages, multiplied together, have a product of 36
3 -- The sum of their ages is (to us, anyway) an undisclosed number
4 -- The oldest daughter has a hamster with a wooden leg

A strange collection of facts, some might say. But, looking deeper into the problem we can find some logic and answers not obvious from casual inspection. Let's start with the product of the ages:

  
SQL> --
SQL> -- Generate an age list for the girls
SQL> --
SQL> -- Maximum age is 36
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum  
SQL> --
SQL> -- Return only the age groupings whose product
SQL> -- is 36
SQL> --
SQL> -- Return, also, the sum of the ages
SQL> --
SQL> -- This restricts the set of values needed to
SQL> -- solve the problem
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  )
 18  select *
 19  from product_check
 20  order by 1,2,3;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         1          1         36         38         36                          
         1          2         18         21         36                          
         1          3         12         16         36                          
         1          4          9         14         36                          
         1          6          6         13         36                          
         2          2          9         13         36                          
         2          3          6         11         36                          
         3          3          4         10         36                          

8 rows selected.

SQL> 

Notice we return not only the product of the ages but also the sums of the various combinations, as we'll need this information later on in the problem. Now another 'fact' emerges:

5 -- Knowing the sum of the ages doesn't help matters much

This reveals that there is more than one combination of ages which produce the same sum:

  
QL> --
SQL> -- Find, amongst the acceptable values,
SQL> -- those sets where the summed value is
SQL> -- the same
SQL> --
SQL> -- This is necessary as providing the sum
SQL> -- was of little direct help in solving the
SQL> -- problem
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19        select youngest, middle, oldest, sum, product
 20        from (
 21         select youngest, middle, oldest, sum, product,
 22         count(*) over (partition by sum) ct
 23         from product_check
 24        )
 25        where ct > 1
 26  )
 27  select *
 28  from summed_check;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          
         1          6          6         13         36                          

SQL> 

Now we know the number of people in the room and why the sum wasn't enough information to solve the problem. The final 'nail in the coffin' (so to speak) is the owner of the hamster with the wooden leg; the problem states:

Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.

It's not the hamster, it is the fact that the oldest daughter (there's only one) exists. Knowing that last piece of information provides the final answer:

  
SQL> 
SQL> --
SQL> -- Return the one set of values meeting all of
SQL> -- the criteria:
SQL> --
SQL> -- Product of 36
SQL> -- Sum of some unknown number
SQL> -- Oldest child exists
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19        select youngest, middle, oldest, sum, product
 20        from (
 21         select youngest, middle, oldest, sum, product,
 22         count(*) over (partition by sum) ct
 23         from product_check
 24        )
 25        where ct > 1
 26  )
 27  select *
 28  from summed_check
 29  where oldest > middle;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          

SQL> 

The ages of the girls are 9, 2 and 2 which also clarifies the question of identical twins.

The problem was solved in a systematic and (to me, at least) logical way by breaking the problem down into workable pieces.

So you don't encounter such problems at college reunions or parties (what a dull life that must be); you may encounter them at work. This next problem was presented in the Oracle PL/SQL group:

Hi,


I have 3 columns of data


Column 1:subscription
Column 2: invoice number
Column 3: Service


I need to seperate the subscription types into new, renewals and additional
which is fine but the next bit i am having trouble


Each invoice number can have 1 or more service


e.g.
Invoice Number Service
123 Photocopying
123 Printing
123 Scan & Store
234 Photocopying
234 Scan & Store
345 Photocopying
345 Printing


I apply a rate for each service e.g.
photocopying = 1.5
printing = 1.7


but if Scan and store is in an invoice with photocopying we charge an extra
1.5


but if printing is a service with the scan and store a different rate
applies 1.7


so i can't just count scan and store and apply a rate i have to figure out
if it is with photocopying or with printing and then apply the rate


What I want to be able to do is creat a table with columns that calculates
this
so i get a 4 columns:


Service usage rate total
photocopying 3 1.5 4.5
Printing 2 1.7 3.4
Scan & Store 1 1.5 1.5
Scan & Store w/Print 1 1.7 1.7


The problem comes in when i'm trying to count scan and store wit/without
printing. I can't figure it out.


I import the report from an excel spreadsheet into acces and want to run a
query that does all this...


thanks in advance,
ainese

With this problem I decided to change the table a bit and add a numeric SERVICE_CD column:

  
SQL> Create table subscription(
  2          subscr_type varchar2(15),
  3          invoice number,
  4          service varchar2(40),
  5          service_cd number
  6  );

Table created.

SQL>
SQL> insert all
  2  into subscription
  3  values('RENEWAL',123,'Photocopying',0)
  4  into subscription
  5  values('RENEWAL',123,'Printing',2)
  6  into subscription
  7  values('RENEWAL',123,'Scan '||chr(38)||' Store',5)
  8  into subscription
  9  values('ADDITIONAL',234,'Photocopying',0)
 10  into subscription
 11  values('ADDITIONAL',234,'Scan '||chr(38)||' Store',5)
 12  into subscription
 13  values('NEW',345,'Photocopying',0)
 14  into subscription
 15  values('NEW',345,'Printing',2)
 16  select * From dual;

7 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Using the wm_concat() function and the BITAND operator produced results that will make the final solution easier to code; using BITAND allows Oracle to generate a result based upon the sum of the SERVICE_CD values and by properly choosing those SERVICE_CD entries make it easier to isolate the various combinations:

  
SQL> select subscr_type, invoice, services,
  2         bitand(service_cds, 0) col1,
  3         bitand(service_cds, 2) col2,
  4         bitand(service_cds, 7) col3
  5  from
  6  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  7  from subscription
  8  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                       COL1       COL2       COL3
--------------- ---------- ---------------------------------------- ---------- ---------- ----------
NEW                    345 Photocopying,Printing                             0          2          2
RENEWAL                123 Photocopying,Printing,Scan & Store                0          2          7
ADDITIONAL             234 Photocopying,Scan & Store                         0          0          5


SQL>

Knowing which BITAND results indicate which chargeable combinations allows using DECODE to produce a version of the desired results:

  

SQL> column services format a40
SQL> break on report skip 1
SQL> compute sum  of photocopy printing scan_and_store scan_and_store_w_prt on report
SQL>
SQL> select subscr_type, invoice, services,
  2         decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
  3         decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
  4         decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
  5         decode(bitand(service_cds, 7), 7, 1.7, 0) scan_and_store_w_prt
  6  from
  7  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  8  from subscription
  9  group by subscr_type,invoice);

SUBSCR_TYPE INVOICE SERVICES                           PHOTOCOPY PRINTING SCAN_STORE SCAN_STORE_PRT
----------- ------- ---------------------------------- --------- -------- ---------- --------------
NEW             345 Photocopying,Printing                    1.5      1.7          0              0
RENEWAL         123 Photocopying,Printing,Scan & Store       1.5      1.7          0            1.7
ADDITIONAL      234 Photocopying,Scan & Store                1.5        0        1.5              0
                                                       --------- -------- ---------- --------------
sum                                                          4.5      3.4        1.5            1.7

SQL>

All services in this example are charged the appropriate rates, including the adjustments made for certain combinations of service.

One last problem is one found often on the web:

Display the second highest salary in the employee table
Display the employee id, first name, last name and salary for employees earning the second highest salary

Depending on which question is asked several solutions present themselves. The first is the 'obvious' solution:

  
SQL> select salary
  2  from
  3  (select salary from employees order by 1 desc)
  4  where rownum = 2;

no rows selected

SQL>

which doesn't work because ROWNUM is never set to 1 so it can't get to 2. A modest rewrite produces:

  
SQL> select salary
  2  from
  3  (select rownum rn, salary from
  4  (select salary from employees order by 1 desc))
  5  where rn = 2;

    SALARY
----------
     17000


1 row selected.

SQL>

which still might not be the correct answer as more than one person may have the same salary, including the highest. Another rewrite, using DENSE_RANK() provides the solution:

  
SQL> select salary from
  2  (select salary, dense_rank() over (order by salary desc) rk
  3          from employees)
  4  where rk=2;

    SALARY
----------
     17000
     17000


2 rows selected.

SQL>

To produce more information the above query needs a small modification:

  
SQL> select employee_id, first_name, last_name, salary
  2  from
  3  (select employee_id, first_name, last_name, salary, rank() over (order by salary desc) rk
  4   from employees)
  5  where rk =2;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000


2 rows selected.

SQL>

RANK() and DENSE_RANK() do just what they're named -- rank the requested values -- but only DENSE_RANK() will not skip ranking numbers when duplicate values exist:

  
SQL> select salary, rank() over (order by salary desc) rk
  2          from employees;

    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          4
     13500          5
     13000          6
     12000          7
     12000          7
     12000          7
     11500         10
...

Notice that the third highest salary is ranked 4 with RANK(); not so with DENSE_RANK():

  
SQL> select salary, dense_rank() over (order by salary desc) rk
  2          from employees;

    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          3
     13500          4
     13000          5
     12000          6
     12000          6
     12000          6
     11500          7
     11000          8
     11000          8
     11000          8
     10500          9
     10500          9
     10000         10
     10000         10
     10000         10
     10000         10
...

which is why DENSE_RANK() was used to solve the problem.

Problem solving, when given a little thought, isn't a terrible chore if you know how to read the problem and extract known information. From that you can eventually arrive at a solution (and, yes, multiple solutions can exist depending upon how you think about the problem). The above are examples to get you started thinking in the 'right' direction. As always, some practice at solving problems is recommended so take these problems, work them through, change data, work them through again (and you may find holes in my solutions that I didn't consider). The more you practice, the more you learn.

A train leaves station A at 3:30 PM and travels west at 50 miles per hour ...

An oft-used (and subsequently oft-abused) execution path is parallel execution, usually 'instigated' by some sort of parallel hint. Developers, albeit with the best intentions, misuse and abuse this mechanism because of faulty logic, that 'logic' being that if one process executes in X amount of time then Y parallel processes will execute in X/Y amount of time, and nothing could be further from the truth in many cases. Yes, there are opportunities to use parallelism to speed up processing but in most of the cases I've seen it's doing more harm than good.

To illustrate this in a 'real world' situation say you're in a restaurant and the service appears to be slow; you think 'if I had three waitresses instead of one I'd get my food faster', but let's look at that from a different point of view. Three waitresses for one table means that all three waitresses need to communicate with each other to avoid repeating work done by the others (this, of course, takes time). Each waitress needs to be assigned specific duties but also must be available to take over for another if something happens (one broke her toe, for instance); such an occurrence requires reassignment of duties and adjusting the schedule to accomodate the change. Eventually you get your order but it will take MORE time than if a single waitress performed all of the necessary tasks.

Parallel processing does more than simply 'divide and conquer' as it requires several steps most developers may not know about or may ignore entirely. DSS and DW systems, running with large numbers of CPUs, can benefit from parallel processing as the load can be distributed among the CPUs reducing the load on a single processor. OLTP systems, on the other hand, usually involve operations which are quick to begin with and the overhead of implementing parallel processing is quite large compared to the overall execution time; additionally it may take longer to complete the parallel execution than it would to properly tune the query for single-threaded processing and eliminate the parallelism altogether.

So what does parallel processing bring to the table? Obviously the possibility, but not the guarantee, of reduced execution time. What does it cost? That depends on the system but for OLTP systems it usually creates more work than it accomplishes as a parallel query coordinator process is spawned, along with X number of parallel slaves all 'talking' to that coordinator as well as a parallel-to-serial operation by the coordinator to assemble the final results. Many OLTP systems aren't designed for parallel processing (and rightly so) as parallel execution is designed to utilize resources that may remain idle (CPU, memory) and are in sufficient quantity to warrant generating the additional overhead required. Queries and statements should be tuned properly (there, I've said it again) for speedy execution and slapping a parallel hint on them isn't the correct way to go about the tuning process. Looking at an example of the differences between parallel and serial execution might help clear the air.

  
SQL> select id, txtval, status
  2  from para_tst pt
  3  where id between 9001 and 34001;

        ID TXTVAL                          STATUS
---------- ------------------------------ -------
      9389 ALL_SCHEDULER_RUNNING_JOBS       VALID
      9390 USER_SCHEDULER_RUNNING_JOBS      VALID
      9391 USER_SCHEDULER_RUNNING_JOBS      VALID
[...]
     20772 /130d52e2_JDK2Sorter             VALID
     20773 /130d52e2_JDK2Sorter             VALID
     20774 /4c28cb16_ToolLogOptions         VALID
     20775 /4c28cb16_ToolLogOptions         VALID
     20776 /cbd9a55f_AbortException         VALID
     20777 /cbd9a55f_AbortException         VALID

591960 rows selected.

Elapsed: 00:02:03.68

Execution Plan
----------------------------------------------------------
Plan hash value: 350193380

---------------------------------------------------------------------------------------------------
| Id | Operation                | Name     | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |          |  593K|    20M|  1698   (2)| 00:00:21 |       |       |
|  1 |  PARTITION RANGE ITERATOR|          |  593K|    20M|  1698   (2)| 00:00:21 |    19 |    21 |
|* 2 |   TABLE ACCESS FULL      | PARA_TST |  593K|    20M|  1698   (2)| 00:00:21 |    19 |    21 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID" 
SQL> select /*+ parallel(pt 2) pq_distribute(pt partition) */
  2        id, txtval, status
  3  from para_tst pt
  4  where id between 9001 and 34001;

        ID TXTVAL                           STATUS
---------- ------------------------------- -------
     18404 /79bc64f9_JvmMemoryMeta           VALID
     18405 /b80019c2_EnumJvmThreadContent    VALID
     18406 /b80019c2_EnumJvmThreadContent    VALID
     18407 /bcfa29b5_EnumJvmThreadCpuTime    VALID
     18408 /bcfa29b5_EnumJvmThreadCpuTime    VALID
     17997 /b3bd73eb_CommonClassObject       VALID
     17998 /b3bd73eb_CommonClassObject       VALID
     17999 /c5a69e17_ServerSchemaObject1     VALID
[...]
     20724 /4bd3ef8d_KnownOptions4           VALID
     20725 /4bd3ef8d_KnownOptions4           VALID
     20726 /75d2b0ba_KnownOptions5           VALID
     20727 /75d2b0ba_KnownOptions5           VALID
     20728 /75e9b2d4_KnownOptions            VALID
     20729 /75e9b2d4_KnownOptions            VALID

591960 rows selected.

Elapsed: 00:02:23.59

Execution Plan
----------------------------------------------------------
Plan hash value: 1393746857

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation            | Name     | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ |IN-OUT| PQ Distr |
-------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |          |  593K|    20M|   942   (1)| 00:00:12 |       |       |       |      |          |
|  1 |  PX COORDINATOR      |          |      |       |            |          |       |       |       |      |          |
|  2 |   PX SEND QC (RANDOM)| :TQ10000 |  593K|    20M|   942   (1)| 00:00:12 |       |       | Q1,00 | P->S | QC (RAND)|
|  3 |    PX BLOCK ITERATOR |          |  593K|    20M|   942   (1)| 00:00:12 |    19 |    21 | Q1,00 | PCWC |          |
|* 4 |     TABLE ACCESS FULL| PARA_TST |  593K|    20M|   942   (1)| 00:00:12 |    19 |    21 | Q1,00 | PCWP |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("ID" 

On a system running 11.2.0.2 with absolutely no load the parallel execution takes almost twenty seconds longer; on a heavily used OLTP system this difference will likely be greater as the overhead to run in parallel is greater than that for the 'straight' query. Resources allocated in OLTP configurations are intended for multiple users performing concurrent processing thus there usually aren't 'extra' CPU cycles or memory to toss at parallel execution. Since the parallel query slaves will be waiting for resources (just like their user session counterparts) this will increase the response time and delay processing until such resources are freed. Notice also the execution path, which includes the parallel-to-serial operation that consolodates the outputs from the parallel query slaves into the final result set. Depending upon the server configuration there could be many more parallel slaves than shown here, and 'funneling' all of that data into a single 'pipe' consumes time and resources you most likely won't have in the OLTP environment.

When is it good practice to use parallel processing? One situation which comes to mind is in creating/populating tables using complex queries that may return faster in parallel than with standard serial processing. An application on which I worked was populating a table with a rather benign join but it was taking far too long to complete the load -- the elapsed time exceeded the batch processing window. Using parallelism (along with regularly updated statistics) dramatically reduced the response time from over an hour to less than two minutes, well within the batch window allowing the rest of the processing to continue. [Note that this was a batch process, run outside of the normal business day, which freed resources normally allocated to user sessions.] There are others, outside of data warehousing applications, but they're exceptions and not the rule.

So, we've learned that even though parallelism would appear to make things go faster in reality that's not often the case due to the extra overhead in managing additional processes and consolodating the individual results into the final result set. We've also learned that simply slapping a /*+ parallel */ hint into a query doesn't constitute tuning and doing so can make performance worse instead of better. The correct choice is to properly tune the query or queries in question using resources such as AWR and ASH reports, execution plans and wait statistics to pinpoint the problem area or areas to address. Parallelism isn't a silver bullet and wasn't intended to be and should be used sparingly, if at all.

Now, where's my sandwich?

Archiving older data is a complex task; local, national and sometimes international regulations dictate when, how and for how long the archived data must remain available. Add to that the seemingly insurmountable task of storing all of this data electronically and what appears, from those outside the IT arena, to be a simple act can end up as anything but simple. Within the context of an Oracle database there are methods of archiving data, some simple, some a bit more complex but still within the realm of possibility. Let's look at those options and what they can, and cannot, offer.

The first option which comes to mind (mine, anyway) involves partitioning, an Enterprise Edition option (which should not be a surprise since companies who generate reams of data to archive usually install this edition). Archiving in this scenario is fairly easy: convert the relevant partitions to stand-alone tables in their own tablespace, separate from the 'live' production data. If this data is now on its own storage it can even be moved to another database server to facilitate access and not impact daily production. Let's look at the steps involved with this option. First let's create a partitioned table:

 CREATE TABLE archive_test
( 
        dusty DATE,
        vol VARCHAR2(60),
        info NUMBER
)
PARTITION  BY RANGE ( dusty ) 
(
PARTITION really_old 
   VALUES LESS THAN ( TO_DATE('01-apr-1999','dd-mon-yyyy'))
   TABLESPACE older_than_dirt,
PARTITION quite_old 
   VALUES LESS THAN ( TO_DATE('01-jul-2004','dd-mon-yyyy'))
   TABLESPACE old_as_dirt,
PARTITION sorta_new
   VALUES LESS THAN ( TO_DATE('01-oct-2009','dd-mon-yyyy'))
   TABLESPACE newer,
PARTITION really_new
   VALUES LESS THAN ( TO_DATE('01-jan-2012','dd-mon-yyyy'))
   TABLESPACE newest
);


-- 
-- Create local prefixed index
--

CREATE INDEX i_archives_l ON archive_test ( dusty,vol ) 
LOCAL ( 
PARTITION i_otd_one TABLESPACE i_otd_one,
PARTITION i_oad_two TABLESPACE i_oad_two,
PARTITION i_nwr_three TABLESPACE i_nwr_three,
PARTITION i_nwst_four TABLESPACE i_nwst_four
);

The last partition of our table is set to accept all data through 01/01/2012 so archiving data simply involves converting the desired partition to a stand-alone table, preferably stored on a different diskgroup or array than the current production data. [Sometimes a new partition is created prior to archiving the old partition (or partitions) to keep data flowing into the partitioned table. We'll presume we have enough 'room' to avoid creating a new partition at archive time.] For the sake of illustration let's put the destination tablespace, ARCHIVED_TS, in a separate ASM diskgroup (doing this allows for the movement of the diskgroup to another physical server for use by a separate Oracle instance). To archive the partition REALLY_OLD to a stand-alone table named REALLY_OLD_TBL:

--
-- Create empty table matching partition definition
--
create table really_old_tbl
( 
        dusty date,
        vol varchar2(60),
        info number
) tablespace archived_ts;   -- Tablespace created in separate ASM diskgroup or on separate storage

--
-- Check row count in desired partition
--

select count(*)
from archive_test partition(really_old);

--
-- Move partition data to stand-alone table
--
alter table archive_test
exchange partition really_old with table really_old_tbl with validation;

--
-- Verify all rows written to destination table
--

select count(*)
from really_old_tbl;

--
-- Drop now-empty partition presuming row counts match
--
alter table archive_test drop partition really_old;

The data is now archived to a separate table and will no longer be available in ARCHIVE_TEST; this, however, makes partition QUITE_OLD the first partition resulting in any DUSTY value less than the upper partition limit being stored there, including values which should have been in REALLY_OLD. This may not be an issue as values that old may no longer be generated but it is an aspect to consider when archiving older data from a partitioned table.

A second method is available for those not using partitioning which involves creating an archive table from the source table by selecting the desired data (this will also work for partitioned tables and may be the option of choice if a single archive table is desired as the above illustrated method creates a new table for each partition to be archived):

--
-- Create table and copy data
--
create table really_old_tbl
tablespace archived_ts
as select *
from archive_test
where dusty 

The data is now archived to a separate table. Changing the create table statement to an insert statement can allow for 'newer' archived data to be stored in the same archive table; again a similar condition exists as any data within the archived range can still be inserted into the source table as no date limits may exist to restrict inserts. A trigger can be used to restrict such inserts as shown below:

SQL> create or replace trigger ins_chk_trg
  2  before insert on archive_test
  3  for each row
  4  declare
  5          mindt date;
  6  begin
  7          select max(dusty) into mindt from really_old_tbl;
  8          if :new.dusty 
SQL> insert into archive_test
  2  values (to_date('&mindt;', 'RRRR-MM-DD HH24:MI:SS') - 1, 'Test value x', -1,to_date('&mindt;', 'RRRR-MM-DD HH24:MI:SS') );
old   2: values (to_date('&mindt;', 'RRRR-MM-DD HH24:MI:SS') - 1, 'Test value x', -1,to_date('&mindt;', 'RRRR-MM-DD HH24:MI:SS') )
new   2: values (to_date('1999-03-08 14:17:40', 'RRRR-MM-DD HH24:MI:SS') - 1, 'Test value x', -1,to_date('1999-03-08 14:17:40', 'RRRR-MM-DD HH24:MI:SS') )
insert into archive_test
            *
ERROR at line 1:
ORA-20987: Data (07-MAR-99) outside of acceptable date range
ORA-06512: at "BLORPO.INS_CHK_TRG", line 6
ORA-04088: error during execution of trigger 'BLORPO.INS_CHK_TRG'


SQL>

Such a trigger can be used on partitioned and non-partitioned tables to police the inserts and reject those bearing dates present in the archive table. As the archive table data increases (due to subsequent inserts) the trigger will recognize the new maximum and use it to reject inserts.

Lest we forget the external utilities both exp/imp and expdp/impdp can be used to archive data; the QUERY option to both exp and expdp allows extraction of specific data from a given table so that only the oldest data will be exported. Oracle recommends using a parameter file when using the QUERY option to avoid operating system specific escape characters. Additionally expdp allows for one query per table and multiple table:query pairs when specified with the schema.table:query format. A sample parameter file is shown below:


TABLES=employees, departments
QUERY=employees:'"WHERE department_id > 10 AND salary > 10000"'
QUERY=departments:'"WHERE department_id > 10"'
NOLOGFILE=y
DIRECTORY=dpump_dir1
DUMPFILE=exp1.dmp



This creates tables with the source names and a limited subset of the source data which can be imported into a different schema or different database. The imported tables can be renamed with the usual command (in releases 10g and later):

rename employees to employees_arch;
rename departments to departments_arch;

or in 11gR2 by using the REMAP_TABLE parameter to impdp:


...
REMAP_TABLE=employees:employees_arch
REMAP_TABLE=departments:departments_arch
...


[REMAP_TABLE will fail if the source table has named constraints in the same schema as those constraints will need to be created when the destination table is created. Constraints named SYS% will be created without error and the table or tables will be remapped.]

The final step in this process is to delete the now-archived data from the source table, as illustrated in the previous example for non-partitioned tables.

If you're using a release older than 10g the process is a bit more time consuming, involving creating a new table with the desired name from the imported table then copying any index/constraint definitions to the new table, finally dropping the imported table once you're certain the 'renamed' table has all necessary indexes and constraints in place.

Archiving older data is not a terribly difficult task (at least in an Oracle database) but it does take planning and attention to detail to ensure all of the desired data is properly archived and available for the end users. Maintaining the archived table (or tables) also takes planning as applications may need to be written to directly access the archive and, in the case of multiple archive tables, be 'smart' enough to be able to access the newer additions as they arrive. Remember, too, that the specifications for the archiving revolve around local, state, federal (in the U.S.) and possibly international regulations and the archiving scheme must be flexible enough to provide the required 'window' of access. It's also true that archived data may outlast the regulations which established it (unless legal issues preclude maintaining the archive beyond the prescribed date range); in such cases a sound storage strategy is a must and it's not unusual for archived data to go from Tier II (slower, cheaper disk) storage to Tier I (tape) as long as the data is still accessible as access speed is not a requirement for archived data.

"Sherman, set the WAYBAC machine for ..."

Apparently there is still confusion over which Oracle feature provides high-availability and which provides disaster recovery. This DBA seems to believe that Data Guard is a high-availability (HA) solution; I don't consider it so as we'll soon discover. Let's define what high-availability is then see which product and/or feature satisfies that definition.

To be a high-availability solution it must provide relatively uninterrupted access to the production system by implementing a mechanism where failures are handled in a transparent manner (the user community is unaware of failures which could affect access).

Data Guard, in all of its glory, does not provide such access in my opinion, although the Oracle documentation says otherwise; Real Application Clusters does, as does an older Oracle product called FailSafe and an even older product that was cumbersome to configure and use, Oracle Parallel Server. Still there are DBAs in the workforce who firmly believe that Data Guard is a valid high-availability solution, even knowing that a failover involves time where users have no access the database. [Apparently my idea of HA and Oracle's differs.] Given the criteria listed above Data Guard does not, in my mind, fit the bill. So why do some DBAs consider it high-availability? Let's see what Data Guard does do and maybe we'll see why I don't consider it that way.

Data Guard provides a mechanism whereby Oracle will keep one or more databases synchronized with the primary database. For a physical standby configuration in release 10.2 three protection modes are available

Maximum Protection
Maximum Availability
Maximum Performance

Maximum Protection mode guarantees that the standby will be in 'lock step' with the primary as all transactions are written to both the primary redo logs and the standby redo logs with the caveat that if Oracle cannot write a transaction to the standby redo logs the primary will suspend activity until the error causing the write issue is corrected. No transasction can commit until all local and remote redo has been written successfully. This ensures a seamless cutover should a disaster strike, but it also inconveniences production users should problems in standby redo log writes occur. Maximum Availability mode works like Maximum Protection mode until a standby redo log write problem occurs, when Oracle switches to Maximum Performance mode until the standby redo log write issue is resolved, at which time the standby redo log writes catch up with the primary. Maximum Performance mode allows a transaction to commit after successfully writing the redo entries to the local redo logs regardless of whether the standby redo log writes have completed. [In 11.2 a snapshot mode is available which can be converted to a physical standby at any time, and allows for read/write access to the data. Also available in that release is support for redo apply to a physical standby database open for read access.] A logical standby configuration is also available which relies upon log shipping to the standby where Log Miner is used to extract and apply DDL and DML changes, although there are a few data types (listed in the online documentation) which won't replicate in such a setup. For the purposes of this discussion only the physical standby configuration will be considered as it will replicate all changes made to the primary thus providing a byte-for-byte replica of the primary.

A physical standby can provide (depending upon the protection mode) an exact 'point in time' copy of the primary so that no transactions are 'pending' due to archive log transfers. [In Maximum Performance mode, if standby redo logs are not configured then the standby is synchronized to the last log transferred from the primary leaving a gap of several minutes worth of transactions at the standby site.] This does NOT provide a high-availability configuration as failover tasks consume time and take the database out of service until the failover is complete. Since high-availability is defined as relatively uninterrupted access to the database even during failure of some resources Data Guard cannot, and should not, be used if high-availability (meaning no downtime as RAC provides it) is desired or required. It is a Disaster Recovery (DR) solution and DR and HA are not the same in my book. [Golden Gate provides both DR (with Active Data Guard) and real-time replication solutions through the same interface, neither of which are high-availability offerings even though many 'experts', and Oracle Corporation, offer the product as a high-availability configuration.]

Real Application Clusters, or RAC as it's known industry-wide, is an HA solution as it provides the uninterrupted access required. Unless this is a single-node RAC (a configuration available in release 11.2 which provides for expansion and is primarily designed for development and testing purposes) this option is configurable to transparently failover to a known good node should one node fail, the key term being 'transparently'. No user interaction or intervention is necessary as RAC seamlessly transfers work to a good node and continues without inconveniencing the users. The database is available as long as at least one node remains operational; that, of course, could slow down transactional activity depending on the available memory on that node but there is no loss of service. Contrast that to Data Guard, where the primary database is no longer functioning and a secondary database, in a physically separate location, must be converted from being the standby to being the primary before users can resume work. Also add the time to reconfigure the old primary to being the new standby and it's clear this is not high-availability.

As another aspect of this discussion a RAC configuration involves one database and two or more clustered instances accessing that database, and Data Guard involves two or more separate databases, usually found in two or more physical locations. Yes, the tnsnames.ora files can be configured to 'fail over' to the first active production site so that users need not reconfigure SQL*Net to access the former standby database should it be needed but that isn't the issue with Data Guard; the issue is the failover time required to exchange the roles of primary and standby which interrupts service until the transition is complete. Improvements in Data Guard may have decreased the downtime considerably although I would have a difficult time recommending Data Guard as an HA offering.

Don't get the wrong idea about Data Guard; it's an excellent technology that provides data protection in the event of a catastrophic disaster at the primary data center and it is often used in conjunction with RAC to provide an environment resilient to node failure and complete disaster. If you want HA (in my opinion) then you need to consider RAC as the 'out of the box' solution provided by Oracle as it handles node failures with grace (and possibly style) and keeps the work flowing seamlessly. Know that data protection and high-availability are different, but compatible, areas which need to be considered when constructing a robust database configuration and that the former cannot replace the latter (and, again, this is my opinion).

[The Oracle documentation, at first blush, agrees with my definition but later on in the depths of the HA discussion clearly states, without question, that Oracle considers Data Guard a high-availability solution. Far be it from me to argue with Oracle.]

Data Guard and RAC are both well-tested and reliable options to consider when designing and implementing a fault-tolerant configuration, but of the two only RAC, in my estimation, provides high-availability.

Unless you like explaining to upper management why your 'HA solution' required an outage.

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.

It is possible to run afoul of an ORA-04031 error for no discernable reason (at least at the time the error presents itself). Being that ORA-04031 errors can have a multitude of causes and, as a result, a multitude of solutions it's difficult to address the entire situation in one post. One particular situation, which presented two possible initial interpretations, will be discussed including the investigation and eventual solution. So, let's dig in and see what brought forth such an investigation and where it led.

ORA-04031 errors are not usually written to the alert log; the exception to this is if a background database process is affected. From 10gR1 onwards trace files are written to the user_dump_dest; these files provide information for Oracle support in diagnosing these errors. And those same trace files can be a help to you in discovering why your database is suddenly throwing ORA-04031 errors.

Since 10gR1 Oracle has subdivided the shared pool into subheaps (actually since 9i but the algorithm has been expanded and improved from 10gR1 onwards), the number of which is determined by the SGA size and the number of processors available. An ‘extra’ subheap, subheap 0, may also be available should there be free shared pool space unallocated to any of the other subheaps (found in the shared pool reserve, usually configured to be 10% of the shared pool allocation); this subheap contains memory available for allocation to any of the active subheaps in the shared pool. Since subheap 0 does not appear in any of the SGA stat reports no unallocated memory exists; this is not to say these subheaps do not have free memory in them, just that no additional memory is available for subheap expansion.

The trace files report. in this instance, that subheap 7 is the subheap generating the ORA-04031 errors:

HEAP DUMP heap name="sga heap(7,0)"  desc=380079e88

The subheap affected is the first number in the parenthesised list with the second number indicating the sub-subheap (and, since that is 0 the subheaps are not further subdivided). Using Tanel Poder's sgastatx.sql script the overall subheap allocations can be displayed; notice that subheap 7 has a smaller allocation than the rest:

SQL> @sgastatx %

-- All allocations:

SUBPOOL                             BYTES         MB                            
------------------------------ ---------- ----------                            
shared pool (1):                318767456        304                            
shared pool (2):                352326152        336                            
shared pool (3):                318767528        304                            
shared pool (4):                352321896        336                            
shared pool (5):                318767672        304                            
shared pool (6):                503317024        480                            
shared pool (7):                302046408     288.05                            
shared pool (Total):           2466314136    2352.06                            

8 rows selected.

-- Allocations matching "%":
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%%%')

SUBPOOL                        NAME                       SUM(BYTES)         MB 
------------------------------ -------------------------- ---------- ---------- 
shared pool (1):               free memory                  57472568      54.81 
                               db_block_hash_buckets        53327376      50.86 
                               library cache                43470152      41.46 
...
shared pool (2):               free memory                  77810608      74.21 
                               db_block_hash_buckets        53327576      50.86 
                               library cache                43899072      41.87 
                               sql area                     22086320      21.06 
...
shared pool (4):               free memory                  92167560       87.9 
                               db_block_hash_buckets        53327576      50.86 
                               library cache                42164432      40.21 
                               Checkpoint queue             21498432       20.5 
...
shared pool (5):               free memory                  64019296      61.05 
                               db_block_hash_buckets        53327888      50.86 
                               library cache                37621344      35.88 
...
shared pool (6):               free memory                 230536936     219.86 
                               db_block_hash_buckets        57521680      54.86 
                               library cache                39577624      37.74 
                               sql area                     22639112      21.59 
...
shared pool (7):               db_block_hash_buckets        53327376      50.86 
                               library cache                40609216      38.73 
                               free memory                  39128512      37.32 
                               sql area                     24575688      23.44 
                               Checkpoint queue             21760608      20.75 
                               FileOpenBlock                18539544      17.68 
                               ASM extent pointer array     15474656      14.76 
                               ASH buffers                  10485760         10 
                               kglsim heap                   5306112       5.06 
                               CCursor                       4617920        4.4 
                               trace buffer                  4210688       4.02 
                               KCB Table Scan Buffer         4198400          4 
                               PCursor                       4048272       3.86 
                               event statistics per sess     3804800       3.63 
                               XDB Schema Cac                3671336        3.5 
                               Sort Segment                  3594240       3.43 
                               private strands               3434496       3.28 
                               Heap0: KGL                    3413608       3.26 
                               parameter table block         2479640       2.36 
                               simulator hash buckets        2404928       2.29 
                               transaction                   2336176       2.23 
                               PX subheap                    2291080       2.18 
                               sessions                      2264240       2.16 
                               dbwriter coalesce buffer      2105344       2.01 
                               object queue                  1864800       1.78 
                               KTI-UNDO                      1831024       1.75 
                               enqueue                       1336592       1.27 
                               state objects                 1313720       1.25 
                               KGLS heap                     1273704       1.21 
                               kglsim object batch           1078056       1.03 
                               row cache                     1073184       1.02 
                               FileIdentificatonBlock        1037624        .99 
                               partitioning d                 949920        .91 
                               krbmror                        946400         .9 
                               procs: ksunfy                  752928        .72 
                               MTTR advisory                  673920        .64 
                               object queue hash buckets      673056        .64 
                               buffer handles                 609104        .58 
                               db_files                       599104        .57 
                               kglsim hash table bkts         598016        .57 
                               call                           553608        .53 
                               obj stat memo                  426816        .41 
                               type object de                 371224        .35 
                               DML lock                       351232        .33 
                               ksfqpar                        332072        .32 
... 

1602 rows selected.

SQL>

The free memory in subheap 7 is reported as 37.32 MB where the free memory in the remaining 6 subheaps ranges from around 55MB to almost 220MB:

shared pool (1):               free memory                  57472568      54.81
shared pool (2):               free memory                  77810608      74.21
shared pool (3):               free memory                  59792512      57.02
shared pool (4):               free memory                  92167560       87.9
shared pool (5):               free memory                  64019296      61.05
shared pool (6):               free memory                 230536936     219.86
shared pool (7):               free memory                  39128512      37.32

Once memory is allocated to a subheap it cannot be reallocated to another subheap which may need it, thus subheap 7 cannot benefit from the ‘excess’ space in subheap 6 and throws the ORA-04031 error when it tries to expand after consuming the 37+ MB of free space it has available. If there is over 37 MB of free space left why is the allocation failing? Let's look at V$SHARED_POOL_RESERVED and report on the total number of ORA-04031 errors generated since startup and the size of the last failing expansion attempt:

SQL> select request_failures "ORA-04031 Count",
   2        last_failure_size "Size",
   3        to_Char(sysdate, 'DD-MON-RRRR HH24:MI:SS') curr_dt
   4 from v$shared_pool_reserved
   5 /

ORA-04031 Count       Size CURR_DT
--------------- ---------- --------------------
          10871       4192 07-JUN-2010 14:17:34

[The data in these columns is available whether or not shared_pool_reserved_size is set, so should you have this parameter set to 0 you need not worry as the above query will still return the desired results.] Notice the last failing allocation was for just over 4 KB of memory; since ASMM is in use memory is allocated in granules of 4 MB or 16 MB in size (depending upon the size of the SGA) and in this case the granule size is 16 MB. And since the failing allocation is just over 4 KB it points to a lack of available resource rather than fragmentation of the shared pool.

If ASMM is in use (sga_target and sga_max_size are set) then increasing the value for sga_target may correct the situation by allocating more memory to the shared pool and the shared pool reserve. If manual shared memory management is in place then increasing the shared_pool_size and bouncing the database would possibly solve the problem.

Yet another option exists, which can be used in conjunction with increasing the shared pool size: setting an undocumented parameter, _kghdsidx_count, to a lower value to reduce the number of shared pool subheaps. Since this is not a dynamic parameter it will need to be set in the init.ora file or with the

alter system set "_kghdsidx_count"=[some number] scope=spfile;

statement and then 'bounce' the database. Of course one should not fiddle with undocumented parameters without Oracle Support's blessings so if this situation ever affects you an SR should be created to ensure you're performing correct and supported actions to resolve the issue. As a side note Oracle support would suggest such an adjustment in 9i databases when similar situations arose.

Oracle also states that Bugs 4467058, 5552515, and 6981690 can also throw ORA-04031 errors; these bugs still plague 10.2.0.4 (if you're still on this release) as they are fixed in 11.1 and/or 11.2. Bug 7340448 affects 10.2.0.4 as well, increasing the shared pool memory growth from ‘create table … as select …’ statements and use of the REGEXP_LIKE functionality. These bugs are described in My Oracle Support document 396940.1.

ORA-04031 errors can be frustrating to investigate as what seems like the correct route to a solution sometimes ends up at a dead end. There are plenty of resources for this error, though (check google.com and see how many results come back), thus plenty of help is available for those who take the time to carefully examine all of the evidence presented to them via trace files and data dictionary views.

The answer is out there.

Maybe.

Occasionally certain objects in a database can fail to function, such as packages, procedures, triggers, functions, indexes, synonyms and views. Modifying any of the underlying dependent objects those items rely upon can change the status of such objects from 'VALID' to 'INVALID' or 'UNUSABLE'. Short of attempting to access such objects and failing miserably how does one discover and correct such problems? The solution is fairly simple: ask the database.

Oracle provides, in the *_OBJECTS views, a column named STATUS which lists the status (obviously) of the object in question. When everything is right and proper that status should be 'VALID'; of course no database is problem-free or immune to code changes so there are times when various objects may no longer be usable. Finding these objects is half of the battle; let's look at a query to do that:

SQL> select object_name, object_type, status
  2  from user_objects
  3  where status  'VALID'
  4  order by created;


OBJECT_NAME                         OBJECT_TYPE         STATUS
----------------------------------- ------------------- -------
OWA_SYLK                            PACKAGE             INVALID
SCHEMA_ACCESS                       PROCEDURE           INVALID
CHECK_SAL                           FUNCTION            INVALID
RAISE_SAL                           PROCEDURE           INVALID
STRINGC                             FUNCTION            INVALID
JOB_PKG                             PACKAGE             INVALID
DATES_PKG                           PACKAGE             INVALID
EMP_VW                              VIEW                INVALID
VIEW_EMP_DEPT                       VIEW                INVALID
GET_EMPNAME                         FUNCTION            INVALID
GET_SAL                             PROCEDURE           INVALID
PROJECT                             SYNONYM             INVALID

12 rows selected.

SQL>

Notice the objects are ordered by their creation date; this allows the query to be used to write a dynamic script to recompile the objects and avoid dependency invalidations in the process:

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status  'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status  'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter FUNCTION GET_EMPNAME compile;
alter FUNCTION STRINGC compile;
alter PACKAGE DATES_PKG compile;
alter PACKAGE JOB_PKG compile;
alter PACKAGE OWA_SYLK compile;
alter PROCEDURE GET_SAL compile;
alter PROCEDURE RAISE_SAL compile;
alter PROCEDURE SCHEMA_ACCESS compile;
alter SYNONYM PrOJECT compile;
alter VIEW EMP_VW compile;
alter VIEW VIEW_EMP_DEPT compile;

12 rows selected.

SQL>

But, wait, there's a neat little script that Oracle has provided to do the same job: utlrp.sql, located in the $ORACLE_HOME/rdbms/admin directory. It calls the UTL_RECOMP package and recompiles all invalid objects in the database (or tries to). It also reports how many of the recompiled objects generated errors and, if this number is larger than you might expect (yes, you may have invalid objects which cannot be 'fixed') then you run the first query listed and see which objects are affected. You can then use the second query to generate a dynamic list, modify that script to include a 'SHOW ERRORS [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION| JAVA SOURCE | JAVA CLASS} [schema.]name]' command after each compile statement and discover why each remaining invalid object would not successfully compile (full syntax is used for the illustrated 'show errors' invocations):

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status  'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status  'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter PROCEDURE RAISE_SAL compile;

SQL>

Generating a more detailed error message for each compile:

SQL> alter FUNCTION CHECK_SAL compile;

Warning: Function altered with compilation errors.

SQL> show errors function check_sal
Errors for FUNCTION CHECK_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/30     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL> alter PROCEDURE RAISE_SAL compile;

Warning: Procedure altered with compilation errors.

SQL> show errors procedure raise_sal
Errors for PROCEDURE RAISE_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/20     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL>

So we're missing a table these objects depend upon, and until that table is replaced they will remain invalid, so we can stop trying to compile them.

In most cases views won't need to be recompiled as select access to invalid views automatically performs that action; of course if the situation is like that shown above nothing will make the invalid view usable.

Unusable indexes are treated a bit differently, as they can't be recompiled; they need to be rebuilt. Normally the database would be shutdown and then started in restricted mode to allow the rebuild to commence unhindered (rebuilding indexes really shouldn't be done when users are actively accessing the database as it consumes resources and can cause exceptional delays for other processes while the rebuild of each index is taking place). A similar query to the invalid objects SQL can find the unusable indexes:

SQL> select index_name, status
  2  from user_indexes
  3  where status  'VALID';

no rows selected

SQL>

Had there been any unusable indexes the following query will generate the necessary executable statements:

select 'alter index '||index_name||' rebuild tablespace '||tablespace_name||';'
from user_indexes
where status  'VALID';

Spool that output to a file, verify it wrote correctly (the default line size may be a bit short for some resulting lines, so you need to check that each alter statement is, indeed, on a single line) then prepare to execute the script after the database is in restricted mode. Log the execution of the script so any resource-related errors can be addressed before it's run again (usually, though, one run is sufficient).

Fixed views are a different story, as they're based upon memory and internal disk structures. If any of these are declared INVALID the only recommended action to be taken is to contact Oracle Support as you cannot recompile such views. It's likely that you'll be told to shutdown and startup the database, but do NOT proceed with that action until told to do so by, you guessed it, Oracle Support.

So, finding and correcting invalid database objects is fairly straightforward; it does require attention to detail, however, to ensure that all objects which can be successfuly recompiled/rebuilt are again in a usable state. Practice on a test database is recommended so that if and when this process is required on a production system it's been tested and re-tested and the method is properly defined and documented.

Of course, if it ain't broke ...