Oracle procedure optimization

Question

I have a table in Oracle, which gets populated daily with approx. 350k records. I've created a procedure to keep only 2 dates in the table, the moment when there are records with a third distinct date inserted, it will delete the records with the Minimum date.

The solution below works, but it's taking too long to execute, since there are 1M records in the table:

CREATE OR REPLACE PROCEDURE DELETE_PREV_DT
 
 AS

nCount NUMBER;
tablename  varchar2(50);

BEGIN
  FOR aRow IN (SELECT *
                 FROM TTTAAAA
                 )
  LOOP
  tablename := 'TTTAAAA';
  
  
    EXECUTE IMMEDIATE 'SELECT COUNT(DISTINCT DATE_ACCUMULATED) FROM ' || tablename
      INTO nCount;

    IF nCount > 2 THEN
      EXECUTE IMMEDIATE 'DELETE FROM ' || tablename ||
                           ' WHERE DATE_ACCUMULATED = (SELECT MIN(DATE_ACCUMULATED) ' ||
                                                 'FROM ' || tablename || ')';
    END IF;
  END LOOP;
END;
/

Can someone provide a modification so this procedure can run faster?

Answer 1

Row-by-row promises to be slow-by-slow, along with context switching because of dynamic SQL.

How about such an approach? Sort dates, remove ones that aren't in the top 2.

SQL> select * from test order by datum, id;

        ID DATUM
---------- ----------
         1 21.08.2020
         2 21.08.2020
         3 21.08.2020
         4 22.08.2020
         5 22.08.2020
         6 23.08.2020
         7 23.08.2020
         8 24.08.2020

8 rows selected.

SQL> delete from test t
  2  where t.datum in (select x.datum
  3                    from (select a.datum,
  4                                 dense_rank() over (order by a.datum desc) rn
  5                          from test a
  6                         ) x
  7                    where x.rn > 2
  8                   );

5 rows deleted.

SQL> select * from test order by datum, id;

        ID DATUM
---------- ----------
         6 23.08.2020
         7 23.08.2020
         8 24.08.2020

SQL>