Use of ROWID Vs PK in Updates/Deletes

[engineer2100]

Hi all,

Does anyone know of any side effects of using ROWID instead of Primary Key values to update a table?

The reason is that, I am writing a small generic application to update data from any table that the user wants. Since a table might or might not having PK's I want to use ROWID for Updates and/or Deletes.

Regards
Engi.

 

[SantaMufasa]

Engi,

Yes, there is a side-effect of using ROWID (versus anything else) to update a table: It is remarkably fast. If you know the ROWID of the row you wish to update (or delete), use that as your "WHERE..." token. There is no "down-side" for using ROWID, where all other issues are equal.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:38 (31Jan05) UTC (aka "GMT" and "Zulu"),
@ 12:38 (31Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[carp]

I guess it depends on how you are going to find the ROWID! If you are working from a cursor that includes the ROWID as part of its populating query, you should be OK.

 

[SantaMufasa]

Absolutely correct (to Carp's observation)...thus my qualification, "...If you know the ROWID...". I often SELECT the ROWID along with other data of primary interest just so I can use the ROWID instead of some other WHERE token. So, using a PL/SQL cursor and updating or deleting via "...WHERE CURRENT OF..." is the most efficient method for a PL/SQL block to perform updates.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:48 (31Jan05) UTC (aka "GMT" and "Zulu"),
@ 12:48 (31Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[engineer2100]

Mufasa! my guru,

Thanks for info. Yeah actually I was looking for down-side of using ROWID over PKs. So what you say is that its perfectly ok to use ROWID.

Carp, I have an SP that takes care of building the Insert & Update statements and execute it against the database. I am not fetching ROWID into a cursor.

-Engi

 

[SantaMufasa]

Actually, Engi, if you are using a PL/SQL cursor, by definition, you have ROWID implicitly available for UPDATE and DELETE...WHERE CURRENT OF <cursor_name>. That is what Carp is emphasising. "UPDATE...WHERE CURRENT OF..." means "UPDATE...WHERE ROWID = <current ROWID of CURSOR row>.

If this brief explanation leaves you with additional question, please re-post questions here.

Cheers,

 

[engineer2100]

No. Its crystal clear.

I agree to the point that I was confused by Carp's reply but there was a reason. I didnot see your reply before I hit the submit button for my re-post.

Thanks to you Mufasa and to you too Carp.

-Engi

 

[carp]

BTW, if you are looking for a downside of using a ROWID, don't ask Oracle. When you build an index, it is so that Oracle can easily find the ROWID of the row you are seeking. So - as long as you already know what the ROWID is, you are in good company using that information to find the row to work on. However, if your procedure is doing a "SELECT rowid FROM some_table WHERE some_condition_is_true" as a separate query, you are probably killing your performance. On the other hand, if you are pulling the ROWID as part of an overarching query, or using the WHERE CURRENT OF approach referenced by SantaMufasa, then you will probably be just fine - no downside.

 

[engineer2100]

Thanks Carp. I will surely take care of what Mufasa and you told me to do.

-Engi