Question about deleting lots of data

[gandolf989]

Is it better to createa PL/SQL collection, then use a FORALL to felete the data? Or is it better to not use the collection? The DBA's are telling me to delete 10,000 rows at a time.

DECLARE
   TYPE t_my_type IS TABLE OF slegs.slegs_rec_id%TYPE
      INDEX BY BINARY_INTEGER;
   v_mycollection t_my_type;
BEGIN
   DBMS_OUTPUT.ENABLE(1000000);
   LOCK TABLE slegs IN EXCLUSIVE MODE NOWAIT;
   LOOP
      SELECT DISTINCT slegs_rec_id
        BULK COLLECT INTO v_grp_cl_n
        FROM slegs
       WHERE grp_cl_n NOT IN (
         SELECT DISTINCT grp_cl_n
           FROM tfer);

      IF v_mycollection.COUNT > 0
      THEN
         FORALL i IN v_mycollection.FIRST..v_mycollection.LAST
            DELETE slegs
             WHERE slegs_rec_id = v_mycollection(i);

         DBMS_OUTPUT.PUT_LINE(
            'Records deleted from slegs: ' || 
            SQL%ROWCOUNT );
         COMMIT;
      ELSE
         DBMS_OUTPUT.PUT_LINE(
	    'There are no more slegs records to delete!');
         EXIT;
      END IF;
   END LOOP
   COMMIT;
END;
/

BEGIN
   DBMS_OUTPUT.ENABLE(1000000);
   LOCK TABLE slegs IN EXCLUSIVE MODE NOWAIT;
   LOOP
      DELETE slegs
       WHERE grp_cl_n NOT IN (
         SELECT DISTINCT grp_cl_n
           FROM tfer) AND
         ROWNUM <10001;

      DBMS_OUTPUT.PUT_LINE(
         'Records deleted from slegs: ' || 
         SQL%ROWCOUNT );

      COMMIT;

      IF SQL%ROWCOUNT = 0
      THEN
         EXIT;
      END IF;
   END LOOP
   COMMIT;
END;
/

[DaPi]

I can see no reason at all to do it the first way. Especially as you are reselecting the rows for the delete by an index, I presume! (rowid would be better, but even so . . ).

The DBA's presumably based the 10'000 row limit on the size of the RBS available. Better do as they say, or Mr.H will be after you
P.S. I think you meant to put a LIMIT on the BULK COLLECT?

Won't you loose the table lock on the inner commit? I'm not sure what to do about it!

(Shouldn't say this, but this is probably a case where "fetch across commit" would be OK, because the delete can be restarted if it crashes out with an ORA-1555.)

BTW: has anyone checked out the effect of DISTINCT in

WHERE grp_cl_n NOT IN (
         SELECT DISTINCT grp_cl_n . . .

It's not logically needed: does it run faster, slower or just the same with/without it (my guess is that the CBO makes it irrelevant - will check when I'm connected to a db again).

[Mr.Hanky]

Sheet, I'm after him anyway.

[Mr.Hanky]

Originally posted by gandolf989
[B]Is it better to createa PL/SQL collection, then use a FORALL to felete the data? Or is it better to not use the collection? The DBA's are telling me to delete 10,000 rows at a time.

"FELETE" is that anything like Delation?

[gandolf989]

Originally posted by Mr.Hanky
"FELETE" is that anything like Delation?

You are the last person who should admonish me for poor grammar.

[slimdave]

Originally posted by Mr.Hanky
"FELETE" is that anything like Delation?

Hopefully it's not a typo for "fellate"

[gandolf989]

I just got back from vacation, so I'm not really back into the swing of things yet. The table lock is unecessary given the numerous commits. That is an interesting point about selecting on the index. I could also load up the collection with everything that I want to delete then have some logic delete from the collection 10000 rows at a time with a forall. i.e.

FORALL i IN v_mycollection.1..v_mycollection.10000
DELETE slegs
 WHERE slegs_rec_id = v_mycollection(i);

I did run the first set of code and it deleted 800,000 rows in 10 minutes. I'm not sure how long that should have taken, but this should not need to get run very often. Mostly I just ant to look at the options now, so when it comes up again, I will already know the best way of deleting the rows. Thanks DaPi. You aren't so cranky after all.

[gandolf989]

Originally posted by slimdave
Hopefully it's not a typo for "fellate"

That's fellated harrassment!!!

[Mr.Hanky]

Ask Freud what he thinks.

[gandolf989]

Originally posted by Mr.Hanky
Ask Freud what he thinks.

I did and he mentioned something about you having an abnormal fixation on developers.