ROWIDs for PL/SQL Performance
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
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
ROWIDto the DML statement.
- If rows are moved, the
ROWIDwill 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.
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);
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.
Don't forget to clean up the test table.
DROP TABLE rowid_test PURGE;
For more information see:
Hope this helps. Regards Tim...