Home » SQL & PL/SQL » SQL & PL/SQL » To Delete 25 lacs Records
To Delete 25 lacs Records [message #428376] Wed, 28 October 2009 02:54 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have a table which contains more than 50 lacs of records in that i want to delete more than 25+ lacs records for that i have wrote the below block but for process its running for much more time can any one tune my block to process it with less amount of time.

Declare 

cursor p43_msg_ack_mnt is 
select mam_sys_gen_id from p43_msg_ack_mnt where 
create_date < '31-dec-2007' and rownum < 50000;

type typ_p43 is table of number;
typp43 typ_p43;

begin

open p43_msg_ack_mnt ;

loop

fetch p43_msg_ack_mnt bulk collect into typp43 limit 20000;
   if ( typp43.COUNT > 0) then

       forall i in typp43.first .. typp43.last

        EXECUTE IMMEDIATE ' delete from p43_msg_ack_mnt where mam_sys_gen_id = :bv ' USING IN typp43(i);
           
        end if ;
            
          exit when p43_msg_ack_mnt%notfound ;  

End loop;

End;
Re: To Delete 25 lacs Records [message #428380 is a reply to message #428376] Wed, 28 October 2009 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't know that '31-dec-2007' is not a date but a string. I think you will delete some rows you don't want to delete (like '01-Apr-2009').

Either do it in a sinble delete, or use the standard copy what you want to keep in a new table, truncate the current, copy back what you save (both copy using direct path of course and parallelism if you can).

Regards
Michel
Re: To Delete 25 lacs Records [message #428391 is a reply to message #428380] Wed, 28 October 2009 03:37 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
My requirement is i should not create backup table.
Re: To Delete 25 lacs Records [message #428393 is a reply to message #428391] Wed, 28 October 2009 03:39 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
When you can do anything in SQL why do you want to do it in plsql and that too using dynamic queries??
Re: To Delete 25 lacs Records [message #428396 is a reply to message #428391] Wed, 28 October 2009 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My requirement is i should not create backup table

I thought your requirement was to do it and to do it efficiently.
I never heard a requirement that was do it slowly (actually I did).

Regards
Michel
Re: To Delete 25 lacs Records [message #428428 is a reply to message #428393] Wed, 28 October 2009 06:11 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
But i gave simple SQL statement it is running for long time.
Re: To Delete 25 lacs Records [message #428433 is a reply to message #428428] Wed, 28 October 2009 06:17 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You didn't give a simple SQL statement.

You gave complicated PL/SQL code that contains two of the worst things considering performance:

1) Dynamic SQL (Execute Immediate)
2) A row-by-row processing loop.

A simple SQL would be :

delete from p43_msg_ack_mnt 
 where create_date < to_date('31-dec-2007','dd-mon-yyyy');
Previous Topic: SQL code
Next Topic: query related Pivot function of oracle
Goto Forum:
  


Current Time: Sat Dec 03 01:16:11 CST 2016

Total time taken to generate the page: 0.05449 seconds