spool /home/oracle9/MIE/CDR-25JUN2007.lst


CREATE OR REPLACE PROCEDURE P_DEL28SEP2006 as

cursor s1 is SELECT rowid, t.* /*+ parallel(tel_calldtl partition(bl_period_0625),4) */ 
from tel_calldtl partition(bl_period_0625) t
where year_month_date= '200706'
and bill_period='25' and source in ('SMJ', 'SBH', 'SWK');
  
i number := 0;

begin
  for c1 in s1 loop
      delete from TEL_CALLDTL partition(bl_period_0625)
             where rowid = c1.rowid;

      i := i + 1;              -- Commit after every 1000 records
      if i > 1000 then
         commit;
         i := 0;
      end if;

  end loop;
  commit;
end;
/

exec P_DEL28SEP2006;

spool off

DECLARE

       count  NUMBER  := 0;
       total  NUMBER  := 0;

       CURSOR del_record_cur IS
         SELECT rowid
         FROM   <OWNER>.<TABLE_NAME>
         WHERE  <YOUR_CRITERIA>

     BEGIN
       FOR rec IN del_record_cur LOOP
         DELETE FROM <OWNER>.<TABLE_NAME>
           WHERE rowid = rec.rowid;

         total := total + 1;
         count := count + 1;

         IF (count >= 1000) THEN
           COMMIT;
           count := 0;
         END IF;

       END LOOP;
       COMMIT;
       DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from <OWNER>.<TABLE_NAME>.');
     END;
     /

SQL> drop table delete_Test;

Table dropped.

SQL> create table delete_test (id_col  number,grp number, col_1 varchar2(20));

Table created.

SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> set timing on
SQL> delete delete_test;

100000 rows deleted.

Elapsed: 00:00:04.09
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> set timing off

SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> set timing on
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.06
SQL> set timing off
SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> set timing on
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.08
SQL> set timing off

SQL> delete delete_test;

100000 rows deleted.

Elapsed: 00:00:04.02
SQL> 
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> 
SQL> set timing off
SQL> 
SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> 
SQL> set timing on
SQL> 
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:51.02
SQL> 
SQL> set timing off
SQL> 
SQL> insert into delete_test select lvl, mod(lvl,1000),'Row '||lvl 
  2  from (select level lvl from dual connect by level <= 100000);

100000 rows created.

SQL> 
SQL> set timing on
SQL> 
SQL> begin
  2    for rec in (select distinct grp from delete_test) loop
  3      delete delete_Test where grp = rec.grp;
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.03
SQL> 
SQL> set timing off
SQL> 
SQL> drop table delete_test;

Table dropped.