Re: Oracle very slow.
From: Gert Rijs <nospampleasegem_at_wirehub.nl>
Date: 1998/01/21
Message-ID: <01bd26aa$d77f4560$0100007f_at_gertrijs>#1/1
Date: 1998/01/21
Message-ID: <01bd26aa$d77f4560$0100007f_at_gertrijs>#1/1
[posted and mailed to Jeff]
I did not see your original posting, but if you want to select one row
using pl/sql and later on delete it you could use a where-current-of
delete.
something like:
declare
cursor c1 is
select process_date from prc_buffer1 where rownum = 1 for update;
ws_process_date date;
begin
open c1;
fetch c1 into ws_process_date;
....
delete from prc_buffer1 where current of c1;
close c1;
end;
gert
-- reply address is altered to keep the spam down remove the nospamplease part to reply... Jeff Wyant <wyant_at_wcoil.com> wrote in article <6a3p6u$6g7$0_at_208.10.2.134>...Received on Wed Jan 21 1998 - 00:00:00 CET
> > I've done some more research on my problem and I think I know where my
> > problem lies, but I don't know how to fix it. The way our system is
setup we
> > have four database tables that we call buffer tables. These four tables
are
> > where the records from the shop floor are first written. There are then
four
> > stored procedures that read a single record from each of the tables and
> > processes it. As part of this to insure I have a single record I use
> > rownum=1. I then need to delete this record. I am using a date time
field
> > that has an index on it and the rownum=1 like:
>
> Delete from prc_buffer1where process_date = ws_process_date
> and rownum =1.
>
> When there are more than a couple hundred records this delete seems to
slow
> way down. I believe it is doing a table scan to find the record. Is there
> another way to do this. I need to make sure I get only one record and
that
> this is the record that is deleted. I cannot be sure there are not two
records
> with the same date & time. The only other field is the actual data record
> which is up to 255 characters. Thanks for your help.
>
>
>