Re: select question

Have you looked into why your update is taking so long? If not, should do an "explain plan for" or similar type check to find why it's so slow.

Is this loop being run by multiple threads or just one? If multiple threads are executing this loop simultaneously you could have some issues with this unless there is some locking of some sort happening that is outside the scope of this code snippet.

"music4" <music4_at_163.net> wrote in message news:b56208$ar0_at_netnews.proxy.lucent.com...
> 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 - 21:46:38 CST