Deletion on more than 7m records

curious_mind

Hi All,

I have a requirement where i need to drop somewhere around 7.2 millions records based on one particular condition which is mentioned below in the cursor, please suggest if there is any better way to do it.

set timing on;

set serveroutput on;

DECLARE

CURSOR c1 IS SELECT dstr_alt_id FROM SEODS01.DSTR_ALT_ID DST 

WHERE length(DST.dstr_alt_id)=27;

v_cmit_nbr           NUMBER(8):=50000;

TYPE ARRAY IS TABLE OF c1%ROWTYPE;

v_stg_array      ARRAY;

tot_cnt number;

BEGIN

---------27 length dstr_alt_id Record Deletion started--------------------------------

DBMS_OUTPUT.PUT_LINE('Deletion of records where length of dstr_alt_id is 27 in DSTR ALT ID table started:');

OPEN c1;

              LOOP

                FETCH C1 BULK COLLECT INTO V_STG_ARRAY LIMIT v_cmit_nbr;

                DBMS_OUTPUT.PUT_LINE('Deleting ' ||V_STG_ARRAY.count|| ' records');

                FORALL i IN 1..v_stg_array.COUNT

                DELETE FROM SEODS01.DSTR_ALT_ID WHERE dstr_alt_id=v_stg_array(i).dstr_alt_id;

                COMMIT;

                EXIT WHEN c1%NOTFOUND;

              END LOOP;

              CLOSE C1;

DBMS_OUTPUT.PUT_LINE('Deletion of records where length of dstr_alt_id is 27 in DSTR ALT ID table started completed: ');

---------27 length dstr_alt_id Record Deletion completed------------------------------

Solomon Yakobson

Plain

DELETE EODS01.DSTR_ALT_ID
  WHERE length(DST.dstr_alt_id)=27
/

will be faster that PL/SQL. If you don't have enough UNDO you could use

DECLARE
   V_CNT NUMBER := 50000; -- change it to desired value
BEGIN
    LOOP
      DELETE EODS01.DSTR_ALT_ID
       WHERE length(DST.dstr_alt_id)=27
         AND ROWNUM <= V_CNT; 
      EXIT WHEN SQL%ROWCOUNT < V_CNT;
    END LOOP:
END;
/

However there might be a faster way. You are deleting 7.2 milliom rows. How many rows will be left in the table. If it is less than number of deleted rows you could:

CREATE TABLE SEODS01.DSTR_ALT_ID_TEMP
  AS
    SELECT  dstr_alt_id
      FROM  SEODS01.DSTR_ALT_ID DST
      WHERE nvl(length(DST.dstr_alt_id),0) != 27
/
TRUNCATE TABLE SEODS01.DSTR_ALT_ID
/
INSERT
  INTO SEODS01.DSTR_ALT_ID
  SELECT  *
    FROM  SEODS01.DSTR_ALT_ID_TEMP
/
DROP TABLE SEODS01.DSTR_ALT_ID_TEMP PURGE
/

You could make it even faster if you:

CREATE TABLE SEODS01.DSTR_ALT_ID_TEMP
-- add same table properties as SEODS01.DSTR_ALT_ID has (e.g. tablespace, partitioning, etc.)
  AS
    SELECT  dstr_alt_id
      FROM  SEODS01.DSTR_ALT_ID DST
      WHERE nvl(length(DST.dstr_alt_id),0) != 27
/
DROP TABLE SEODS01.DSTR_ALT_ID PURGE
/
ALTER TABLE SEODS01.DSTR_ALT_ID_TEMP RENAME TO SEODS01.DSTR_ALT_ID
/
-- now you need to to recreate table SEODS01.DSTR_ALT_ID constraints, indexes and grants and recompilE invalid objects.

SY.

Dear DBA Frank

I agree with Solomon. Performance-wise, SQL is faster than PL/SQL. And to delete a large percentage of rows from a table, the CTAS (CREATE TABLE AS SELECT) method is the most effective.