Re: The Perfomance Problem while deleting records in Oracle 8

Although the delete might be able to be improved, you should also consider that 10 minutes is 10*60 = 600 seconds. Hence it takes 600/4000 = 0.15 seconds to delete each record (and 2 index records). This is about 6.7 deletes per second.

If you were only deleting one record the performance problem might not even be noticed.

On the other hand, just to experiment with something that might be similar, I created a table with 300,000 rows as follows: (I suspect this table is simpler than yours. The machine I ran this test on is a 400mhz AMD with 128mbyte ram running NT.)

create table abcd (a number, b number, c number, d number); SQL> declare
  2 i pls_integer;
  3 begin
  4 for i in 1..300000 loop
  5 insert into abcd values (abcdseq.nextval, abcdseq.nextval, abcdseq.nextval,abcdseq.nextval);
  6 commit;
  7 end loop;
  8 end;

It took about 15 minutes to get the table constructed.

Then I put an index on column a and a function-based index (mod(a,75)). These took about 45 seconds each to contruct.

Analyzing the table with compute statistics took 1 minute and 40 seconds.

Deleting one row using the index on column a took over 0.3 seconds.

Deleting 4000 rows took about 25 seconds as the following shows:

SQL> delete from abcd where mod(a,75)=0;

4000 rows deleted.

 real: 24605

Execution Plan

   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=156 Card=3001 Bytes=
          69023)

   1    0   DELETE OF 'ABCD'
   2    1     TABLE ACCESS (FULL) OF 'ABCD' (Cost=156 Card=3001 Bytes=
          69023)

That comes to about 0.006 seconds to perform each deletion. Which is about 167 deletes per second.

I suppose if the index were used it would have taken longer than 10 minutes since each deletion using the index was about 0.3 seconds.

Frank Hubeny

"Kozlitin V.V." wrote:

> Hi all.
>
> I have the perfomance problem - the deleting time of 4000 records from
> table,
> that contain about 300000 records exceeds 10 minutes.
>
> The table has 2 index (on date and on internal_code)
> Server configuration: PII-450, 96 RAM, 9G HDD, Oracle 8.0 on Solaris 7
>
> Any ideas?
>
> Vladimir
> E-mail: kozlitin_at_menatep.vrn.ru
Received on Wed Sep 15 1999 - 23:19:22 CDT