DBA Hub

📋Steps in this guide1/4

ROWIDs for PL/SQL Performance

This article demonstrates how using ROWIDs in transactions can improve performance.

oracle miscconfigurationintermediate
by OracleDba
11 views
1

Considerations

Consider the following before using ROWIDs: - 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 the to the DML statement. - If rows are moved, the 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 , where the row is locked, preventing row movement.
2

Setup

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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);
3

Test

The following code times how long it takes to update each row in the test table using a separate . As you can see, performing the update using the is measurable quicker than using the primary, even if the index blocks are cached. This is because the index search is unnecessary if the is specified.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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>
4

Cleanup

Don't forget to clean up the test table. For more information see: - ROWID Pseudocolumn Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
DROP TABLE rowid_test PURGE;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!