Deleting data from Big Table in Oracle taking longer execution time

Question

I have a situation where I need to delete the data from Log table from my Oracle Database. The table contains around 3159037 rows with CLOB content, now as a maintenance activity I need to purge old data by keeping last 6 months from current date. I have designed the PL/SQL program to achieve this however during my test I found that even for deleting 1000 records, the procedure is taking around 22 seconds to complete its execution. Now lets assume we have more than 100000+ records to delete it will take huge amount of time to complete its execution.

Can anyone suggest the better way to approach this problem.

Below is my current script with FORALL and BULK COLLECT functionality to get performance benefit.

 CREATE OR replace PROCEDURE Integration_data_purge
authid current_user
AS
  c_limit CONSTANT PLS_INTEGER DEFAULT 100;
  CURSOR int_cur IS
    SELECT a.ROWID AS a_rowid,
           b.ROWID AS b_rowid
    FROM   integration_log a,
           integration_errors b
    WHERE  a.error_log_id = b.error_rec_id(+)
           AND Trunc(a.insert_date) < Trunc(SYSDATE) - 180;
  TYPE int_aat
    IS TABLE OF int_cur%ROWTYPE INDEX BY PLS_INTEGER;
  l_integration_log INT_AAT;
  --l_count     INTEGER := 1;
  start_time        NUMBER;
  end_time          NUMBER;
BEGIN
    start_time := dbms_utility.get_time;

OPEN int_cur;

LOOP
    FETCH int_cur bulk collect INTO l_integration_log limit c_limit;

    --DBMS_OUTPUT.put_line ('Retrieved in RUN '|| l_count ||' = ' ||
    -- l_integration_log.COUNT);
    EXIT WHEN l_integration_log.count = 0;

    --if l_integration_log.count <> 0 then
    forall indx IN 1 .. l_integration_log.count
      DELETE FROM integration_log
      WHERE  ROWID = L_integration_log(indx).a_rowid;

    --DBMS_OUTPUT.put_line ('Total rows deleted from INTEGRATION_LOG = ' ||
    --SQL%ROWCOUNT);
    forall indx IN 1 .. l_integration_log.count
      DELETE FROM integration_errors
      WHERE  ROWID = L_integration_log(indx).b_rowid;
--DBMS_OUTPUT.put_line ('Total rows deleted from INTEGRATION_ERRORS = ' ||
--SQL%ROWCOUNT);
--l_count := l_count + 1;
--end if;
--commit;
END LOOP;

CLOSE int_cur;

end_time := dbms_utility.get_time;

dbms_output.Put_line('Execution Completed : '
                     || To_char(( end_time - start_time ) / 100)
                     || ' Seconds');
END;

If table integration_errors has a foreign key to table integration_log and you added ON DELETE CASCADE when you defined the foreign key, then deleting a row from integration_log will also delete all related rows in table integration_errors. — Abra
Foreign keys slow down all operations. I wouldn't suggest them for simple log tables. Moreover, "on delete cascade" runs delete for each row in parent table, so it will be slow-by-slow design. — Sayan Malakshinov

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2020-07-24T08:29:35

Rather a poor design. Try this one

DELETE FROM integration_errors WHERE err_rec_id in (select error_log_id from integation_log where insert_date < sysdate - 180) ;

DELETE from integation_log where insert_date < sysdate - 180;

Don't use trunc(insert_date) unless you have function-based index. Disable the foreign-key constraint before you delete the data and enable it again after you deleted it.

Consider partitioning, in newer Oracle you can also partition by ref-key

Deleting data from Big Table in Oracle taking longer execution time

1 Answers