Home » SQL & PL/SQL » SQL & PL/SQL » Deletion of records taking too much time
Deletion of records taking too much time [message #270487] Thu, 27 September 2007 03:25 Go to next message
donind
Messages: 95
Registered: February 2007
Member


Hi,

Deletion of a records taking too much time.

Table1 (col1,col2)
Total number of records in Table1 is 100000.
The count of result set is 25000.

delete from table1 where col1<200;



This piece of code is taking too much of time.

so i modified to the below way

for i in (select rowid from table1 where col1<200)
loop
delete from table1 where rowid=i.rowid;
commit;
end loop;



Let me know is there any better way to delete records faster.

Thanks in advance.
Re: Deletion of records taking too much time [message #270490 is a reply to message #270487] Thu, 27 September 2007 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would be extremely suprised if your changes made the deletion faster - in fact, I'd want to see traces of the two tests before I'd believe it.

Post an explain plan of the deletion using the single SQL.

Are there any FK constraints or triggers on this table?

{removed request for info that the Op provided and I just didn't see}

[Updated on: Thu, 27 September 2007 03:33]

Report message to a moderator

Re: Deletion of records taking too much time [message #270496 is a reply to message #270487] Thu, 27 September 2007 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It will be much worst for you and others.
And in addition you will likely get ORA-1555 error.

Regards
Michel

[Updated on: Thu, 27 September 2007 03:47]

Report message to a moderator

Re: Deletion of records taking too much time [message #270498 is a reply to message #270496] Thu, 27 September 2007 03:48 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

I request you to suggest me. If there is any best way.

Thanks in advance
Re: Deletion of records taking too much time [message #270503 is a reply to message #270498] Thu, 27 September 2007 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Direct SQL is a quickest way.
Investigate what JRowbottom told you.

Regards
Michel
Re: Deletion of records taking too much time [message #270766 is a reply to message #270487] Fri, 28 September 2007 00:04 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Here is the other way i found in deletion of records.


create table table1 asselect mod(RowNum,3)*100 as col1  from all_objects;

begin    
Loop     
   delete from table1 where col1<200 and RowNum <= 100; 
       exit when SQL%RowCount = 0; 
       commit;    
 end Loop;    
commit;
end;



Just let me know is this a best way for deletion of records.


Thanks
Re: Deletion of records taking too much time [message #270776 is a reply to message #270766] Fri, 28 September 2007 00:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In my opinion, the best way to do that is the very first DELETE statement you wrote in this topic, so - let me repeat it for you:
delete from table1 where col1 < 200;


All your LOOP solutions with a COMMIT within will *probably* end up with the 'snapshot too old' error (ORA-01555) (as you were already told).

What you might also try is to use CTAS (CREATE TABLE AS SELECT ... ) principle and create a table with data set which you'd like to keep. Then, truncate the original table and - once it is empty - return "saved" data set back (and drop that auxiliary table). This *might* help if number of records you want to keep is much smaller than number of records that you'd want to delete.
Re: Deletion of records taking too much time [message #270849 is a reply to message #270766] Fri, 28 September 2007 03:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you've found another method that will be slower than a straight delete, and which will eventually raise ORa-1555 if you keep doing it for long enough.

If you want any more help, please answer these questions:

1) How long is it taking to do this delete? I just did a little test and deleting 25,000 rows took about 6 seconds:
create table delete_test (col_1  number, col_2 varchar2(500));

insert into delete_test (select level, rpad('A',500,'B') from dual connect by level <= 100000);

select count(*) from delete_test;

  COUNT(*)
----------
    100000

set timing on
delete delete_test where col_1 <= 25000;

25000 rows deleted.

Elapsed: 00:00:06.02


2) Are there any FK constraints to this table? Unindexed foreign keys will require a full table scan of the FK table for each row deleted from the primary key table, and will slow things up a lot

3) Are there any ON DELETE triggers on this table.
Previous Topic: Accessing collection elements without Looping
Next Topic: something about user_constraints and foreign key
Goto Forum:
  


Current Time: Thu Dec 08 14:42:35 CST 2016

Total time taken to generate the page: 0.11042 seconds