Deleting billions of records from Oracle Table containing trillions of records

Delete (or update) large number of rows is pain and take lot of time.

The most effective way to manage it is to use the updatable join views

What is requested, but should be no problem is a unique index on your delta table containing the id to be deleted.

Also while using this approach be sure, that the large table has no unique index on the id column. There are some technical issues if both tables have unique index on the join column - see below.

Than use following query (if you want to use sub-partition wise) delete

delete from (select delta.id, big.id big_id from delta join big subpartition (SYS_SUBP220880)on delta.id = big.id  )

Oracle uses the hash join of both tables which is in your case the only possibility to manage your scale. You may dedploy parallel option, do not forget to enable it.

This is the expected execution plan:

-------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |-------------------------------------------------------------------------------------------------------|   0 | DELETE STATEMENT              |       |   100K|  1757K|   875   (2)| 00:00:01 |       |       ||   1 |  DELETE                       | BIG   |       |       |            |          |       |       ||*  2 |   HASH JOIN                   |       |   100K|  1757K|   875   (2)| 00:00:01 |       |       ||   3 |    TABLE ACCESS FULL          | DELTA |   100K|   488K|    47   (3)| 00:00:01 |       |       ||   4 |    PARTITION COMBINED ITERATOR|       |   783K|  9947K|   825   (1)| 00:00:01 |   KEY |   KEY ||   5 |     TABLE ACCESS FULL         | BIG   |   783K|  9947K|   825   (1)| 00:00:01 |    65 |    65 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------    2 - access("DELTA"."ID"="BIG"."ID")

Note - in case that more than one joined tables are key preserved, see the documentation Note on Updatable Views

For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.