Re: select / from / where / for

On Tue, 28 Apr 1998 16:21:00 -0500, Martin Meadows <mmeadows_at_indy.net> wrote:

>Hi. I have another select statement I don't understand. Can someone
>explain:
>
>select 'X'
>from payroll_transaction_master
>where rowid = chartorowid (:ptm_row_id)
>for update of ptm_emp_#;
>
>?
>
>Baffled,
>Martin Meadows

This statement selects a record from the payroll_transaction_master table using a rowid received from a paramter (:ptm_row_id). Rowids are unique row identifiers in a table. If you know a record's rowid, then you can select it and get faster performance than an index. However, I don't consider it a good programming practice - I'm not sure about this, but I think Oracle doesn't guarantee that this number will remain constant (imagine if the block in which the record lies gets transferred to another location on the disk or even to another disk, or a different tablespace).

As the "for update of ptm_emp_#", this statement causes the selected row to be immediately locked, so that no one can obtain an exclusive lock on this row. You should do this whenever you select records that you intend to update or delete later in your transaction. If you don't use the "for update" clause, then it is possible for another concurrent transaction to update or delete the record you selected immediately after you select it.

Hope this helps,

Nuno Guerreiro Received on Thu Apr 30 1998 - 04:22:06 CDT