Re: Question about select and update

There might be still better ways, but try updating by rowid instead of by id column. Your select statement can select the rowid in addition to the necessary data, and the update can use the same value to locate the row without searching index or FTS. If you are selecting inside a cursor, see if WHERE CURRENT OF is supported for updating (maybe it only works inside PL/SQL).

hth
Martin Doherty

music4 wrote:

>Greetings,
>
>I am using OCI to build a oracle client application. There are two processes
>in my application, one keeps inserting data into a table called tb1, the
>other keeps reading data from the table tb1. My question is on the second
>processes.
>
>In second process, I have a thread looply "select id, some other fields from
>tb1". For each fetched row, I do a "update tb1 set msg_status=1 where id=the
>id just fetched", then send the row to other threads to handle.
>
>The reason I have to do the "update" is: If I don't do the "update", the row
>just be select out again and again, the "update" just make a mark on the
>row, so it will not be select out again.
>
>But now the problem is from the "update", because it's really slow
>(20-30msec), that cause my application can not meeting performance
>requirement.
>
>I am looking for a way: How can let oracle don't select a row that has been
>selected once already (by same thread)?
>
>Please help, I am really looking forwarding to hearing your response!
>
>Thanks in advance!
>
>Evan
>
>
>
>
Received on Wed Dec 11 2002 - 20:23:56 CST