Re: select question

music4 wrote:

> 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

Slow is a totally meaningless term.

How many rows are being updated?
How long is it taking?
What would be considered acceptable?
How many rows in the table?
Is there an index on the field referenced in the WHERE clause? Are you using RBO or CBO?
If CBO does the optimizer have current statistics?

My guess is an index on msg_id would solve the problem if one is not already there and being used by the optimizer. Run explain plan to find out.

Daniel Morgan Received on Tue Mar 18 2003 - 00:12:00 CST