select question

Dear Oracle Experts,

I have this problem for a long time. I use Oracle 8.1.5, since license is cheap.

My application have a busy loop to read from database, just like following:

//=====================

loop begin

select msg_id, ... from a_table where status = 0; // msg_id will be stored in a variable id

update a_table set status = 1 where msg_id = id;

hand the message to other threads to handle.

end loop
//=====================

The update statement make me very headachy. Because update operation is very slow. But the update is necessary, otherwise select will read record that have been read out.

The msg_id field is a auto-count field. So I am considering to use range of msg_id to avoid duplicated selection, so that get rid of the update. But I am not sure if it works.

If you have any idea on this problem, please help me!

Thanks in advance!
evan Received on Mon Mar 17 2003 - 20:57:39 CST