The ROWID variable can improve PL/SQL programs that retrieve records from the database, perform manipulation on the column values, and then complete with an UPDATE to the retrieved record. When retrieving each record, the ROWID can be added to the selected column list. When updating each record, the ROWID can be used in the predicate clause. The ROWID is the fastest access path to a record in a table, even faster than a unique index reference.
The performance improvement of using the ROWID is illustrated in the following example. The example retrieves each of the 25,000 employee records, calculates a new salary for each employee, and then updates the employees’ salary. The actual salary calculation is not shown in this example. The first PL/SQL code segment shows the timing results with the UPDATE using the EMPLOYEE_ID column, which has a unique index on the column:
The following output shows the timing of two executions of the preceding code segment:
In the following procedure, the same functionality is maintained while changing the UPDATE to perform the UPDATE based on the ROWID. This involves adding the ROWID in the SELECT statement and changing the UPDATE predicate clause.
The following output shows the timing of two executions of the preceding code segment:
As evidenced from the timings, execution is faster using the ROWID. The first PL/SQL code segment UPDATE statement retrieves the result by using the index on EMPLOYEE_ID to get the ROWID and then goes to the table to search by ROWID. The second PL/SQL code segment UPDATE statement goes directly to the table to search by ROWID, thus eliminating the index search. The performance improvement increases when more records are involved and when the index used does not refer to a unique index.
TIP
Use the ROWID variable to enhance performance when SELECTing a record in a PL/SQL program unit and then manipulating the same record in the same PL/SQL program unit.
Leave a Reply