Home » SQL & PL/SQL » SQL & PL/SQL » commit the records (9.0.4.0)
commit the records [message #310997] Thu, 03 April 2008 03:44 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I wrote one delete command .This command deletes 20 millions of records.But I want to give "commit" after deleting every 100000 records. How to give "commit" for this requirement.
please guide me.

Thank you.
Re: commit the records [message #311004 is a reply to message #310997] Thu, 03 April 2008 03:57 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
What you require is batch deleting, which cannot be done without Pl/Sql.

[Updated on: Thu, 03 April 2008 03:58]

Report message to a moderator

Re: commit the records [message #311008 is a reply to message #310997] Thu, 03 April 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You had a bad idea, why do you want to do this?

Regards
Michel
Re: commit the records [message #311018 is a reply to message #311004] Thu, 03 April 2008 04:35 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

Using Pl/SQL how to write the code.Can you please guide me.

Thank you.
Re: commit the records [message #311024 is a reply to message #311008] Thu, 03 April 2008 04:41 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

this is the requirement.it's not my idea.So please guide me.

Thank you.
Re: commit the records [message #311025 is a reply to message #311024] Thu, 03 April 2008 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the percentage of rows you want to delete?

By the way, I think the need is to delete the rows not to commit each X deleted rows.

Regards
Michel
Re: commit the records [message #311026 is a reply to message #311025] Thu, 03 April 2008 04:48 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

27 percentage of rows only have to delete.
Re: commit the records [message #311030 is a reply to message #311024] Thu, 03 April 2008 04:53 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Its a generic code. Adapt it to your needs. Michels warning remains.

DECLARE
  x  NUMBER := 0;
   CURSOR c1 IS 
     SELECT ....;
BEGIN
  FOR i IN c1 LOOP
    DELETE FROM .....;
  
    x := x + 1;
    
    IF x = 1000 THEN
      COMMIT;
      
      x := 0;
    END IF;
  END LOOP;
  
  COMMIT;
END;
/


Commits within the loop is a bad idea. Be prepared for ORA-01555.

Have you considered CTAS?
Re: commit the records [message #311033 is a reply to message #311025] Thu, 03 April 2008 04:59 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

i wrote pl/sql blkock like
 Begin
 Loop
 delete from     emp ewhere    exists    
(select 1   from   dept d where  d.dept_no   = e.dept_no                    
and    d.d_type    = 'SIP'   
and    d.status      = 'C'                     
and    d.create_date < (select v.join_date -4  
from   vadjoin v)) and rownum<=100000
delete from emp e
where not exists (select 'X' from emp e
                  where e.dept_no      = d.dept_no);
  if SQL%rowcount=0 then
  exit;
  end loop;
commit;
  end;

But I am getting errors as
ERROR at line 15:
ORA-06550: line 13, column 41:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 4, column 65530:
PL/SQL: SQL Statement ignored
ORA-06550: line 18, column 7:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
if

please correct and guide me.
Thank u

[Updated on: Thu, 03 April 2008 05:25] by Moderator

Report message to a moderator

Re: commit the records [message #311045 is a reply to message #311030] Thu, 03 April 2008 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A row by row delete will be very slow.

You can use a loop on:
delete .... and rownum < 100000
commit
until there is no more row to delete
Of course, you may have 1555 error.
Of course, you may have consistent problem as your process is no more transactional.

Regards
Michel
Re: commit the records [message #311051 is a reply to message #311045] Thu, 03 April 2008 05:23 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Further Adding To Michel's Reply:-

Why don't you calculate 27% of your rows in a variable and then

delete from <table name> where rownum<=variable

And then commit

Previous Topic: Creating Job Thruough DBMS_SCHEDULER
Next Topic: performance in Context switch
Goto Forum:
  


Current Time: Sun Dec 11 02:40:10 CST 2016

Total time taken to generate the page: 0.11942 seconds