8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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
Consider the following before using ROWIDs:
ROWID
s 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 theROWID
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 aSELECT ... 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:
Hope this helps. Regards Tim...