I need to delete approx. of 30 billion of rows from an Oracle table that has about 100 + billion of rows. I have all the IDs of the rows I need to delete in a temporary table. Currently I am using single delete statement as below, also using SUBPARTITION and created Index on the temp_table. However this took 4+ hrs to complete in PRODUCTION.
DELETE FROM table_name SUBPARTITION(subpartition_name) WHERE id IN (SELECT id FROM temp_table);
COMMIT;
Is there a way I can optimize this to run bit fast.
Just for a note :
DECLARE
vCT NUMBER(38) := 0;
BEGIN
FOR t IN (SELECT id FROM temp_table) LOOP
DELETE FROM table_name WHERE id = t.id;
......
......
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
I did checked, there are partitions and sub-partitions on the table which I am utilizing it, and there is no child table dependent on it.
Please suggest.
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 updateble join views
What is requested, but shold 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.
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")
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments