SQL Performance (MOSC)

MOSC Banner

unable to delete 790000 rows from GL tables

edited Sep 24, 2013 10:31PM in SQL Performance (MOSC) 3 commentsAnswered
  Hello ,
 I want to delete 7906658 rows by one one rows from  GL table based on where GLDCT = 'IM' AND GLU = 0 AND GLAA = 0;

03:19:06 SQL> select count(*) from testdta.f0911 where GLDCT = 'IM' AND GLU = 0 AND GLAA = 0;

  COUNT(*)
----------
   7906658

Current size of undotbs is 33 gb and autoextend on next 5 m maxsize unlimited
08:10:44 SQL> set serveroutput on
08:10:59 SQL> declare
08:11:14   2    VCOUNT number :=0;
08:11:14   3    cursor c1 is select * from testdta.f0911 where GLDCT = 'IM' AND GLU = 0  AND GLAA = 0;
08:11:14   4    begin
08:11:14   5   for c1rec in c1 loop
08:11:14   6    VCOUNT :=VCOUNT+1;
08:11:14   7  COMMIT;
08:11:14   8  delete from testdta.f0911 where GLDCT = 'IM' AND GLU = 0 AND GLAA = 0;
08:11:14   9    end loop;
08:11:14  10      DBMS_OUTPUT.PUT_LINE('THE NUMBER OF  ROWS Deleted  ARE :-     '||TO_CHAR(VCOUNT));

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center