Forum Stats

  • 3,826,184 Users
  • 2,260,605 Discussions
  • 7,896,809 Comments

Discussions

Deletion on more than 7m records

curious_mind
curious_mind Member Posts: 255 Bronze Badge

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------------------------------

Tagged:

Comments

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,470 Red Diamond
    edited Jan 19, 2021 5:27PM

    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.

    curious_mind
  • Dear DBA Frank
    Dear DBA Frank Member Posts: 177 Bronze Badge

    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.

    curious_mind