Re: select question

music4 wrote:

> Once again, let me make my problem clear.
>
> I use OCI in C++ language to develop my application on Sun Solaris platform.
>
> I the select statement, I select "rowid" field. And in update statement, I
> just use rowid as WHERE clause. So that's no index problem.
>
> On my server, the test result shows that the application can select and
> update 30-40 records per second. And select operation is fast, and most time
> spend on update operation. What's the reason why I want to avoid the update
> operation.
>
> Do you have any more idea?
>
> Thanks,
> 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
> >

That may be what you are doing but it is not what you posted:

> 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;

And it should be noted that rowid is not a field and based on the example, above, I have copied from your original post, might not refer to the row you think when you attempt your update. I would suggest that you change your SELECT to SELECT FOR UPDATE.

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