Oracle Delete very slow

I suppose, both tables lack of the "right" indexes (on ID_LAY and ID_R_LAY).
Could you provide us the corresponding SQL plans (explain plan..)?!

Can you describe doc and prod tables? How may records are in there, how many % of the records are you deleting? It may be faster to create a new table based on what is remaining.

The Tables have 100 million rows and I'm deleting 30 million rows.

What do you think if I use this procedure?

DECLARE
       count  NUMBER  := 0;
       total  NUMBER  := 0;
       CURSOR del_record_cur IS
         SELECT rowid
         FROM   DOC.T_LAY
         WHERE  ID_LAY iN (100,101);
     BEGIN
       FOR rec IN del_record_cur LOOP
         DELETE FROM DOC.T_LAY
           WHERE rowid = rec.rowid;
         total := total + 1;
         count := count + 1;
         IF (count >= 2000) THEN
           COMMIT;
           count := 0;
         END IF;
       END LOOP;
       COMMIT;
       DBMS_OUTPUT.PUT_LINE('deleted ' || total || ' rows from DOC.T_LAY');
     END;
     /

Select allOpen in new window

My fear is getting the error:
ORA-01555 Snapshot Too Old

2000 is not too much for one commit, set it to 20000 or more I would think. The check can be (count = 20000) instead.

You could also exit the procedure after one commit, time one run, wait a few seconds (tbd) and run again. With a 20000 commit limit, you have to run 1500 times.

Hi Gerwin Jansen,
Could you rewrite my procedure with exit the procedure after one commit and wait a few seconds (tbd)?

Why don't you just put this one into a DBMS_JOB or DBMS_SCHEDULER?!

declare

  cursor del_record_cur is
    select rowid
      from DOC.T_LAY
     where ID_LAY in (100, 101)
       and rownum <= 20000;

begin

  for rec in del_record_cur loop
    delete from DOC.T_LAY
     where rowid = rec.rowid;
  end loop;

  commit;

end;

Select allOpen in new window

BUT, this this doesn't solve the core problem why this SQL is running "too slow"! What are the indexes on these tables?! If an appropriate index (if any) exists, what about the statistics?! Are they up to date?! ... In the end, we DO NEED more information ;-)

Below  describe and indexes:

CREATE TABLE DOC.T_LAY
(
  ID_DOC_LAY          INTEGER,
  ID_DOC              INTEGER,
  ID_FOR              INTEGER,
  ID_LAY            INTEGER
)

CREATE INDEX DOC.IDX_T_LAY  ON DOC.T_LAY
(ID_DOC)

CREATE UNIQUE INDEX DOC.PK_T_LAY ON DOC.T_LAY
(ID_DOC_LAY)

You might try creating indexes on id_lay since that's what your where clause filters on.

One other important thing to check: are either of these tables "master" tables that have dependent records in detail tables that are related by a foreign key to these tables?  If yes, that could be with the majority of time is being spent.