Using ROWID for processing IUD where possible

Oracle Database Tips by Donald Burleson

Anytime you can process by using the row rowid in an Oracle table you will get the best performance. This is especially true in IUD situations. Time reductions for some massive UPDATEs were reduced from 12-13 hours to 6 or less as reported by several sites using ROWID processing. By combining bulk bind and ROWID even greater improvements should be realized.

The easiest to see is a bulk delete type operation. Essentially you ?chunk? (to use John Beresniewicz's technical terminology) through the table or tables committing, releasing cursors then resuming where we left off. Figure 14 shows a simple delete procedure using this logic.

CREATE OR REPLACE PROCEDURE delete_it (
rows      IN NUMBER,
tab_name IN VARCHAR2,
delete_date   IN
DATE)                                             
AS
    cur      
INTEGER;                                            
    ret       INTEGER;    
    row_count NUMBER;                                        
    com_string    VARCHAR2(100);                                
    trunc_date    DATE;
BEGIN
 com_string :=                                             
  'SELECT count(1) row_count FROM '||tab_name;        

 cur := DBMS_SQL.OPEN_CURSOR;                              
 DBMS_SQL.PARSE(cur,com_string,dbms_sql.v7);
 DBMS_SQL.DEFINE_COLUMN(cur, 1, row_count);               
 ret := DBMS_SQL.EXECUTE(cur);
 ret := DBMS_SQL.FETCH_ROWS(cur);
 DBMS_SQL.COLUMN_VALUE(cur, 1, row_count); 
                          
 DBMS_SQL.CLOSE_CURSOR(cur);
 DBMS_OUTPUT.PUT_LINE('Count='||TO_CHAR(row_count));
 row_count:=(TRUNC(row_count/rows)+1)*rows;
 DBMS_OUTPUT.PUT_LINE('New Count='||TO_CHAR(row_count));
 trunc_date:=TRUNC(delete_date);
 com_string:=
  'DELETE FROM '||tab_name||'
 WHERE entry_ts<'||chr(39)||trunc_date||chr(39)||'
 and rownum<='||rows;
 DBMS_OUTPUT.PUT_LINE(com_string);
 FOR i IN 0..row_count LOOP
IF MOD(i,rows)=0 THEN
    DBMS_OUTPUT.PUT_LINE('i='||TO_CHAR(i));
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur,com_string,dbms_sql.v7);
    ret := DBMS_SQL.EXECUTE(cur);
    DBMS_OUTPUT.PUT_LINE('Rows Deleted:'||TO_CHAR(ret));
    COMMIT;
    DBMS_SQL.CLOSE_CURSOR(cur);
END IF;
  END LOOP;
END;
/

Some things to note about the script in figure 14:

In order to use SELECT in DBMS_SQL you must use the standard format not the embedded format. To pull the information retrieved by the SELECT into the procedure you use the DBMS_SQL.DEFINE_COLUMN and DBMS_SQL.COLUMN_VALUE procedures.

The line ?row_count:=(TRUNC(row_count/rows)+1)*rows;? ensures you loop through the processing an even multiple of the value of the ?rows? input value, otherwise you might miss the last few records that are smaller than ?rows? in size.

The code loops through the specified table deleting ?rows? number of rows from the table each loop. At the conclusion of each set of ?rows? rows of data the transaction is committed and the cursors released thus forcing a switch of rollback segments and hopefully avoiding the ORA-01555 error.

Of course, what happens if we want more complex operations than just a straight ?delete it all?? The next procedure script shows how to do a conditional update, and by example, virtually any other complex piece of redo generating transaction.  Look at Figure 15.

CREATE OR REPLACE PROCEDURE update_it (rows IN NUMBER, tab_name IN VARCHAR2) AS  cursor_name INTEGER;
  ret      INTEGER;
  rowcount    NUMBER:=1;
  maxrows NUMBER;
  temp_id  ROWID;
  i     INTEGER:=1;
  CURSOR proc_row(row NUMBER, maxr NUMBER) IS
SELECT idrow FROM temp_tab WHERE numrow BETWEEN row and maxr;
  sql_com VARCHAR2(100);
  new_date    DATE;
  maxcount NUMBER;
BEGIN
  sql_com:='TRUNCATE TABLE temp_tab';
  cursor_name:=DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_name,sql_com,dbms_sql.v7);
  ret:=DBMS_SQL.EXECUTE(cursor_name);
  DBMS_SQL.CLOSE_CURSOR(cursor_name);
  sql_com:='INSERT INTO temp_tab SELECT rownum, rowid FROM '||tab_name;
  cursor_name:=DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_name,sql_com,dbms_sql.v7);
  ret:=DBMS_SQL.EXECUTE(cursor_name);
  DBMS_SQL.CLOSE_CURSOR(cursor_name);
  SELECT MAX(numrow) INTO maxcount FROM temp_tab;
  maxrows:=rowcount+rows;
  new_date:=sysdate+2;
  LOOP
DBMS_OUTPUT.PUT_LINE('Rowcount:'||TO_CHAR(rowcount)||' Maxrows:'||TO_CHAR(maxrows));
OPEN proc_row(rowcount,maxrows);
FETCH proc_row into temp_id;
LOOP
EXIT WHEN proc_row%NOTFOUND;
    sql_com:='UPDATE '||tab_name||'
        SET entry_ts='||chr(39)||new_date||chr(39)||'
        WHERE rowid='||chr(39)||temp_id||chr(39);
    cursor_name:=DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_name,sql_com,dbms_sql.v7);
    ret:=DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
    rowcount:=rowcount+1;
    FETCH proc_row INTO TEMP_ID;
    DBMS_OUTPUT.PUT_LINE(to_char(rowcount));
END LOOP;
CLOSE proc_row;
COMMIT;
    maxrows:=rowcount+rows;
    IF rowcount=maxcount+1
THEN
    EXIT;
END IF;
  END LOOP;
END;
/

Figure 15: Example procedure to perform conditional update in batch mode.

Some things to note about Figure 15:

This script depends on a work table that can be reused by any number of different transactions of this type. The table acts as a repository for the target tables ROWID values (ROWID is a unique identifier for each row in a table and is an Oracle internal column for each table). Also placed in the table is the pseudo column ROWNUM that is generated as requested by a select statement. The set of ROWNUM varies according to the select and is not fixed. However, ROWNUMs for a given select are in sequence, which is what we are looking for.

First, the script creates a complete set of ROWNUM, ROWID entries for the target table. In the example script in Figure 15, all of the ROWIDs for the table are gathered, however, by simply adding whatever WHERE clause is required to the line:

sql_com:='INSERT INTO temp_tab SELECT rownum, rowid FROM '||tab_name;

The WHERE clause could even be passed into the procedure as a VARCHAR2 variable.

Once we have this ?indexed? set of ROWIDs (the ROWNUM pseudo column provides a 1 to n correlation to the selected ROWIDs) we can now keep track of the ?record? where we left off processing to do our commit and cursor release so we can resume processing at this point.

The procedure accepts a rows and table name argument  well as the conditional date argument, loads the temp_tab with ROWNUM and ROWID data and using a standard cursor loop construct uses the rows value to ?chunk? through the table performing sets of updates followed by commits and cursor releases, again also avoiding ORA-01555 errors in the process.

Proper use of data types

Improper use of datatypes can result in implicit type conversions. Implicit type conversions result in the statement not being able to use indexes thus forcing a full table scan. Always use %TYPE and %ROWTYPE to capture the types from either the specific table or cursor being used.

Be sure loop logic is correct

Using DBMS_PROFILER the developer can easily see how many times a specific set of statements is executed. A review of loop logic for nested loops is imperative to ensure redundant operations are not occurring inside your packages. Look at the procedure in Figure 16.

CREATE OR REPLACE PROCEDURE update_test_table1(
 new_owner VARCHAR2, old_owner VARCHAR2, new_date DATE, new_ddl DATE)
AS
CURSOR get_owner_rec (old_owner VARCHAR2) IS
SELECT owner, created, last_ddl_time
 FROM test_table
 WHERE owner=old_owner
 FOR UPDATE;
rec1 get_owner_rec%ROWTYPE;
BEGIN
 FOR rec1 IN get_owner_rec(old_owner) LOOP
  UPDATE test_table
    SET owner=new_owner
  WHERE CURRENT OF get_owner_rec;
 END LOOP;
 FOR rec1 IN get_owner_rec(new_owner) LOOP
  UPDATE test_table
   SET created=new_date
  WHERE CURRENT OF get_owner_rec;
 END LOOP;
 FOR rec1 IN get_owner_rec(new_owner) LOOP
  UPDATE test_table
   SET last_ddl_time=new_ddl
  WHERE CURRENT OF get_owner_rec;
 END LOOP;
END;
/

Figure 16: Example Multiple LOOP Code

When executed, the above code performs three full loops through the TEST_TABLE table to perform its updates taking 1:20:05 minutes for 63,104 records updating a total of 30,156 rows. If we modify the procedure to make a single UPDATE pass as shown in Figure 17 performance gains are realized.

CREATE OR REPLACE PROCEDURE update_test_table2(
  new_owner VARCHAR2, old_owner VARCHAR2, new_date DATE, new_ddl DATE)
AS
CURSOR get_owner_rec (old_owner VARCHAR2) IS
SELECT owner, created, last_ddl_time
 FROM test_table
 WHERE owner=old_owner
 FOR UPDATE;
rec1 get_owner_rec%ROWTYPE;
BEGIN
 FOR rec1 IN get_owner_rec(old_owner) LOOP
  UPDATE test_table SET owner=new_owner,
                        created=new_date,
                        last_ddl_time=new_ddl
 WHERE CURRENT OF get_owner_rec;
 END LOOP;
END;
/

Figure 17: Replacing Multiple LOOPs with a cursor FOR loop

The alteration shown in Figure 17 will result in the same 30,156 rows being updated in 31.07 seconds, a nearly 60% savings in time. Beginning PL/SQL programmers may create similar code to the first example because of linear thinking instead of thinking about how many operations can be performed in parallel at one time.

Use of ROWID showed a reproducible 10% gain in processing speed by rewriting the above code as shown in Figure 18.

create or replace procedure update_test_table3(new_owner varchar2, old_owner varchar2, new_date date, new_ddl date)
as
cursor get_owner_rec (old_owner varchar2) is
select rowid, owner, created, last_ddl_time
 from test_table
 where owner=old_owner;
rec1 get_owner_rec%ROWTYPE;
begin
 for rec1 in get_owner_rec(old_owner) loop
  update test_table set owner=new_owner,
                        created=new_date,
                        last_ddl_time=new_ddl
 where rowid=rec1.rowid;
 end loop;
end;
/

Figure 18: Using ROWID to Speed Processing

Using ROWIDs processing time was a consistent 27 seconds on the average vice the 30 second average of the code using the FOR UPDATE and WHERE CURRENT clauses.

Learn More about Oracle Tuning: