Delete logic is taking a very long time to process in Oracle-CodePudding

I am trying to use the following statement for the Delete process and it has to delete around 23566424 Rows, but oracle takes almost 3 hours to complete the process and we have already created an index on " SCHEDULE_DATE_KEY" but still, the process is very slow.Can someone advise on how to make Deletes faster in oracle

DELETE
FROM
  EDWSOURCE.SCHEDULE_DAY_F
WHERE
  SCHEDULE_DATE_KEY >
  (
    SELECT
      LAST_PAYROLL_DATE_KEY
    FROM
      EDWSOURCE.LAST_PAYROLL_DATE
    WHERE
      CURRENT_FLAG = 'Y'
  );

CodePudding user response：

I don't think any index will help here, probably Oracle will decide the best approach is a full table scan to delete 20M rows from 300M. It is deleting at a rate of over 2000 rows per second, which isn't bad. In fact any additional indexes will slow it down as it has to delete the row entry from the index as well.

A quicker approach could be to create a new table of the rows you want to keep, something like:

create table  EDWSOURCE.SCHEDULE_DAY_F_KEEP
as 
select * from  EDWSOURCE.SCHEDULE_DAY_F
where SCHEDULE_DATE_KEY <=
  (
    SELECT
      LAST_PAYROLL_DATE_KEY
    FROM
      EDWSOURCE.LAST_PAYROLL_DATE
    WHERE
      CURRENT_FLAG = 'Y'
  );

Then recreate any constraints and indexes to use the new table.

Finally drop the old table and rename the new one.

CodePudding user response：

You can try testing a filtered table move. This has an online clause. So you can do this while the application is still running.

Note 12.2 and later the indexes will remain valid. In earlier versions you will need to rebuild the indexes as they will become invalid. Good Luck

Move a Table Create and populate a new test table.

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 100;
COMMIT;

Check the contents of the table.

SELECT COUNT(*) AS total_rows,
       MIN(id) AS min_id,
       MAX(id) AS max_id
FROM   t1;

TOTAL_ROWS     MIN_ID     MAX_ID
---------- ---------- ----------
       100          1        100

SQL>

Move the table, filtering out rows with an ID value greater than 50.

ALTER TABLE t1 MOVE ONLINE
  INCLUDING ROWS WHERE id <= 50;

Check the contents of the table.

SELECT COUNT(*) AS total_rows,
       MIN(id) AS min_id,
       MAX(id) AS max_id
FROM   t1;

TOTAL_ROWS     MIN_ID     MAX_ID
---------- ---------- ----------
        50          1         50

SQL>

The rows with an ID value between 51 and 100 have been removed.

As mentioned above if maybe best to PARTITION the table abs drop a PARTITION every N number of days as part of a daily task.