Pseudo Columns

Having spent a week attending instructor led training, I still have a lot of wisdom to share. I first want to talk about pseudo columns. They are like columns. But they are not actual columns in the database. An example is ROWNUM, which is a number representing the order that the row was selected in.

Another pseudo column is ROWID. This is a base 64 value. It is the fastest way to access a row in a table. If you specify FOR UPDATE OF in your explicit cursor definition, a lock will be placed on the rows that you are selecting for update. This has a number of benefits.

The FOR UPDATE OF clause can be combined with a subsequent WHERE CURRENT OF clause in a DML statement. Normally the DML will have a WHERE clause that you define. It causes the database to find the row(s) you want to update. However if you use a WHERE CURRENT OF clause, the database will immediately use the row specified by the record fetched from the cursor to do the DML.

The beauty of WHERE CURRENT OF is that the database does not need to determine which row to act upon. It already know the row that was selected in the cursor. The subsequent DML using WHERE CURRENT OF acts as fast as using the ROWID. And that, as I explained above, is the fastest way to access a row in the database.