Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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:

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...

Back to the Top.