Hi All:
I have seen some people using ROWID as a variable, and trying to
insert values into this column. I pulled out one of my Performance Tips#2,
and here it is:

PERFORMANCE TIPS# 2

ROWID is a pseudo column in a database table. It represents the
physical location of the record and is the FASTEST method of retrieving
records (faster than the use of an index). This ROWID column cannot be
hardcoded because the ROWID of a row will vary when the database is
reorganized. This column should be used to optimize our SQL statements.
Most of our PL-SQL programs consist of large percentage of PL-SQL
commands and a small percentage of SQL commands. But the SQL statements
typically consume majority of the processing time. The use of ROWID is one
of the most powerful optimization methods. Anytime a PL-SQL module
repetitively fetches a record and then manipulates that record via SQL, the
ROWID can be used. Since ROWID is the fastest access method, it should be
used whenever possible.
Here is an example:
DECLARE
CURSOR cur_salaryraise IS
SELECT * FROM pra_employee;
v_record_num PLS_INTEGER DEFAULT 0;

BEGIN
FOR rtc1 IN cur_salaryraise LOOP
v_record_num := v_record_num + 1; -- Counter
IF rtc1.dept_name = 'DATABASE PROGRAMMING' THEN
IF rtc1.salary > 30000 THEN
rtc1.salary := rtc1.salary * 1.05;
ELSE
rtc1.salary := rtc1.salary * 1.10;
END IF;
ELSE rtc1.dept_name = 'ANALYTICAL PROGRAMMING' THEN
IF rtc1.salary > 40000 THEN
rtc1.salary := rtc1.salary * 1.15;
ELSE
rtc1.salary := rtc1.salary * 1.20;
END IF;
END IF;

UPDATE pra_employee
SET salary = rtc1.salary
WHERE rowid = rtc1.ROWID;

DBMS_OUTPUT.PUT_LINE(..........................);
END LOOP;
COMMIT;
EXCEPTION
..........
......
END;
When looping through cursor records and then performing DML
statement on the selected tables, select the ROWID as an additional column
for FASTEST possible performance.



Ram Srinivasan
PRA International, Inc.
4105 Lewis & Clark Drive
Charlottesville, VA 22911-5801
Tel: (434) 951-3365
Web: www.praintl.com
Oracle LazyDBA home page