ROWIDs for PL/SQL Performance

Using a ROWID is the quickest way to access a row of data. If you are planning to retrieve some data, process it, then subsequently update the row in the same transaction, you can improve performance by using the ROWID.

Considerations
Setup
Test
Cleanup

Considerations

Consider the following before using ROWIDs:

ROWIDs are the fastest way to access a row of data, but if you can do an operation in a single DML statement, that is faster than selecting the data first, then supplying the ROWID to the DML statement.
If rows are moved, the ROWID will change. Rows can move due to maintenance operations like shrinks and table moves. As a result, storing ROWIDs for long periods of time is a bad idea. They should only be used in a single transaction, preferably as part of a SELECT ... FOR UPDATE, where the row is locked, preventing row movement.

Setup

The example in this article relies on the following test table.

CREATE TABLE rowid_test AS
SELECT *
FROM all_objects;
ALTER TABLE rowid_test ADD (
 CONSTRAINT rowid_test_pk
 PRIMARY KEY (object_id)
);
EXEC DBMS_STATS.gather_table_stats(USER, 'rowid_test', cascade => TRUE);

Test

The following code times how long it takes to update each row in the test table using a separate SELECT ... FOR UPDATE.

SET SERVEROUTPUT ON
DECLARE
 TYPE t_id_tab IS TABLE OF rowid_test.object_id%TYPE;
 
 l_id_tab t_id_tab;
 l_rowid ROWID;
 l_start NUMBER;
BEGIN
 SELECT object_id
 BULK COLLECT INTO l_id_tab
 FROM rowid_test;
 
 l_start := DBMS_UTILITY.get_time;
 FOR i IN l_id_tab.first .. l_id_tab.last LOOP
 SELECT rowid
 INTO l_rowid
 FROM rowid_test
 WHERE object_id = l_id_tab(i)
 FOR UPDATE;
 
 UPDATE rowid_test
 SET object_name = object_name
 WHERE object_id = l_id_tab(i);
 END LOOP;
 
 DBMS_OUTPUT.put_line('Primary Key (' || l_id_tab.count || ' rows): ' ||
 (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 
 l_start := DBMS_UTILITY.get_time;
 FOR i IN l_id_tab.first .. l_id_tab.last LOOP
 SELECT rowid
 INTO l_rowid
 FROM rowid_test
 WHERE object_id = l_id_tab(i)
 FOR UPDATE;
 
 UPDATE rowid_test
 SET object_name = object_name
 WHERE rowid = l_rowid;
 END LOOP;
 
 DBMS_OUTPUT.put_line('Rowid (' || l_id_tab.count || ' rows) : ' ||
 (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Primary Key (72699 rows): 501 hsecs
Rowid (72699 rows) : 448 hsecs
PL/SQL procedure successfully completed.
SQL>

As you can see, performing the update using the ROWID is measurable quicker than using the primary, even if the index blocks are cached. This is because the index search is unnecessary if the ROWID is specified.

Cleanup

Don't forget to clean up the test table.

DROP TABLE rowid_test PURGE;

For more information see:

ROWID Pseudocolumn

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)