Re: Question about select and update

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

I would assume the COMMIT; is what is slowing it down.

Since you are implimenting a queue, why not use it as a queue? How is the ID generated? If it is monotonically increasing (a sequence #), then just keep track of the last ID you processed. Think of the ticket line at a deli counter. The only issues then are:

 when the sequence number ID wraps around, but that limit can be made nearly unreachable.

 how to maintain the "last ID processed" between restarts.

HTH,
 ed

--
Ed Prochak
Magic Interface, Ltd.
440-498-3700(office)
Computer consulting, database and web services.