Q: Best way to delete common data from two large tables in oracle? I am trying to delete data from table A where matching records from table B. both A, B tables are large tables each is of size around 80GB. Could you please suggest me the best approach to complete the deletion of data from table A? The sql which I am using is:
PROCEDURE del_procedure (col_value_from_B      INTEGER)
   IS
      sql_stmt   VARCHAR2 (2000);
      col_val_from_A    INTEGER := 0;     

   BEGIN
      SELECT MAX (col1) into col_val_from_A               FROM table_B b
          WHERE b.col < col_value_from_B;      

      sql_stmt :=
            'DELETE FROM table_A a'
         || 'WHERE a.col1 <= '
         || col_val_from_A
         || ' and rownum<= 500000 ';

      LOOP
         EXECUTE IMMEDIATE sql_stmt;

         IF SQL%ROWCOUNT = 0
         THEN
            EXIT;
         END IF;

         COMMIT;
      END LOOP;

   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLCODE || SQLERRM);
         RAISE_APPLICATION_ERROR (-20002, SQLCODE || SQLERRM);
   END col_val_from_A;


A: Unless there's a jolly good reason* I'd just do:
DELETE FROM table_A a
WHERE a.col1 <= (SELECT MAX (col1)
                 FROM   table_B b
                 WHERE  b.col < col_value_from_B);

*which does not include "this statement causes the UNDO tablespace to run out!"; that just means you need to increase your UNDO tablespace accordingly. (The only exception to this, IMHO, is if it's a one-off or rarely performed process. In your case, given you're writing a procedure for it, I'm guessing that it's going to be done regularly.)
