Oracle DELETE busting TEMP space

Question

I have a table with 17 billion rows. I want to delete some of those, present in another table.

I tried a delete statement, parallelized, that did not complete because the temp space wasn't enough.

delete /* + PARALLEL(a, 32) */
  from a
 where (a.key1, a.key2) in
         (select /*+ PARALLEL(b, 16) */
                 key1,
                 key2
            from b);

Then I tried create table as select that also failed because of the same reason.

create table a_temp parallel 32 nologging as
       select /* + PARALLEL (a, 32) */
              key1,
              key2,
              rest_of_data
         from a
        where (a.key1, a.key2) not in
                (select key1, key2 from b);

A regular (without PARALLEL) delete was taking more than one day so I had to terminate it.

Is there a way to free temp space as it is not needed anymore, during delete execution?

Is there another way I could do this?

EDIT: B has 173 million records, and almost 16 billion records have to be deleted (almost the whole table). There are no indexes on the table.

EDIT2: The explain plan for the create table is as follows:

CREATE TABLE STATEMENT, GOAL = ALL_ROWS         6749420 177523935   10828960035
 PX COORDINATOR                 
  PX SEND QC (RANDOM)   SYS :TQ10001    6740915 177523935   10828960035
   LOAD AS SELECT (HYBRID TSM/HWMB) USER    A_TEMP          
    OPTIMIZER STATISTICS GATHERING          6740915 177523935   10828960035
     MERGE JOIN ANTI NA         6740915 177523935   10828960035
      SORT JOIN         6700114 17752393472 745600525824
       PX BLOCK ITERATOR            45592   17752393472 745600525824
        TABLE ACCESS FULL   USER    A   45592   17752393472 745600525824
      SORT UNIQUE           40802   173584361   3298102859
       PX RECEIVE           5365    173584361   3298102859
        PX SEND BROADCAST   SYS :TQ10000    5365    173584361   3298102859
         PX BLOCK ITERATOR          5365    173584361   3298102859
          TABLE ACCESS FULL USER    B   5365    173584361   3298102859

Thanks in advance

Answer 1

I made it work, using a different solution.

I created manually the a_temp table and did an insert with an APPEND PARALLEL hint. The temp space wasn't exceeded and the inserts performed perfectly.

Here is the code:

create table a_temp(..);

insert /* + APPEND PARALLEL(a_temp, 32) */
  into a_temp(...)
select /* + PARALLEL(a, 32) */
       (...)
  from a
 where not exists
       (select /* + PARALLEL(b, 16) */
               '1'
          from b
         where a.key1 = b.key1
           and a.key2 = b.key2)