Circular deletion of large table data (delete data before specified date and condition)

Time:2021-9-27

Circular deletion of large table data (delete data before specified date and condition)

DECLARE
  HOWMANY_10MINS NUMBER;
  --Specify a time node to filter data before deleting the time.
  --Please pay attention to the delete condition filter in loop, otherwise all items will be deleted!!!
  SPECIFY_TIME   DATE := TO_DATE('2021/07/30 23:59:59','yyyy/MM/dd hh24:mi:ss');
  BEGIN_TIME     DATE;
  END_TIME       DATE;
BEGIN
  SELECT (SPECIFY_TIME - TRUNC(MIN(T.COLLECTTIME), 'DD')) * 24 * 6 HOWMANY_10MINS
    INTO HOWMANY_10MINS
    FROM IMES_SAC1_MD.DEVICE_COLLECT_DATA T;
  FOR X IN 0 .. HOWMANY_10MINS LOOP
    BEGIN_TIME := SPECIFY_TIME - (X + 1) / 24 / 6;
    END_TIME   := SPECIFY_TIME - X / 24 / 6;
    --For the delete operation, please modify the delete table information and the where filter condition.
    DELETE FROM IMES_SAC1_MD.DEVICE_COLLECT_DATA T
     WHERE T.COLLECTTIME BETWEEN BEGIN_TIME AND END_TIME;
    /*    INSERT INTO TT VALUES (X, BEGIN_TIME, END_TIME, SYSDATE);*/
    COMMIT;
  END LOOP;
END;
/