Using Rowids

So you can get a rowid value into your PL/SQL program, but what use can you then make of it? One potentially very useful application of rowids is in repeating access to a given database row. Recall the example from the previous section in which we retrieved the salary for a specific employee. What if we later want to modify that salary? One solution would be to issue an UPDATE statement with the same WHERE clause as the one we used in our original SELECT:

DECLARE employee_rowid UROWID; employee_salary NUMBER;BEGIN --Retrieve employee information that we might want to modify SELECT rowid, salary INTO employee_rowid, employee_salary FROM employee WHERE last_name='Grubbs' AND first_name='John'; /* Do a bunch of processing to compute a new salary */ UPDATE employee SET salary = employee_salary WHERE last_name='Grubbs' AND first_name='John';END;

While this code will certainly work, it has the disadvantage of having to repeat the same access path for the UPDATE as was used for the SELECT. Most likely, one or more indexes will be consulted in order to determine the rowid for the employee row in question. But those indexes were just consulted for the SELECT statement, so why go through all the trouble of looking up the same rowid twice? Why indeed! Because we retrieved the rowid in our SELECT statement, we can simply supply that rowid to the UPDATE, thus bypassing the need to do any kind of index lookup:

DECLARE employee_rowid UROWID; employee_salary NUMBER;BEGIN --Retrieve employee information that we might want to modify SELECT rowid, salary INTO employee_rowid, employee_salary FROM employee WHERE last_name='Grubbs' AND first_name='John'; /* Do a bunch of processing to compute a new salary */ UPDATE employee SET salary = employee_salary WHERE rowid = employee_rowid;END;

If employee is a regular, heap-organized table, the rowid in the UPDATE statement's WHERE clause points directly to the location of the row on disk. If employee is an index-organized table, that may or may not be the case depending on how volatile the table is; nevertheless, the rowid still represents the fastest way of accessing a given row.

An often better way to achieve the same effect as using ROWID in an UPDATE or DELETE statement is to use an explicit cursor to retrieve data, and then use the WHERE CURRENT OF CURSOR clause to modify or delete it. See Chapter 14 for detailed information on this technique.

12.3.2.1 Do rowids ever change?

Do rowids ever change? That's a good question. If you're going to retrieve a rowid and use it later on, you need to know whether rowids ever expire. Physical rowids, used for standard, heap-organized tables, never change. Only if you delete a row and reinsert it—as you might when doing an export followed by an import—will you get a new rowid for a row. But we just said that physical rowids never change! Are we contradicting ourselves? By no means. When you delete a row and reinsert it, you are really inserting a new row with the same values, and the new row will get a new rowid.

Logical rowids, used in index-organized tables, are not as long-lived as physical rowids. Additionally, logical rowids can change when you change a row's primary key. We're not in the habit of writing applications that change primary keys, so in actual practice this doesn't seem like much of a limitation. It is, however, something to keep in mind when you are writing applications that make use of rowids, especially if any of your application tables are index-organized.

Everything is a tradeoff. The use of rowids can make your applications more efficient, but the tradeoff is that you now have a few more things to think and worry about.