How to delete large number of records from a table faster in plsql?

Question

Table #1:

buck number(10), 
sname varchar(20),
... 
total of 20 columns

Table #2:

sname varchar(20), 
sdiv varchar(10),
... 
total of 15 columns

Table1 has 30M rows
Table2 has 10M rows

I want to delete records in table1.sname where

table1.sname = table2.sname and sdiv = 'tre'

select count(1) 
from table1 
where table1.sname = table2.sname 
  and sdiv = 'tre';    -- 3.3M records

declare 

cursor cur is 
select distinct t.buck from table1 t, table2 s 
where t.sname = s.sname and s.sdiv = 'tre'; 

begin 

for i in cur 

loop 

delete table1 t 
where t.buck = i.buck and t.sname in (select s.sname from table2 s where s.sdiv = 'tre'); 
dbms_output.put_line(i.buck || ' buck records deleted'); 

end loop; 

end; 
/

In the above code, I am deleting buck wise... because each buck has 92k records... so that the database will not hang.

But my query is long running.. even after 42 mins it is not getting completed... and the database is getting stuck.

Is there any way I can optimise it?

Please use internationally understood numbers - thousands, millions, billions — marc_s
– marc_s, Commented Jul 2 at 14:24
Work with your DBAs, find out what is blocking/locking your table when you attempt to execute your delete. — Andrew
– Andrew, Commented Jul 2 at 14:29
@marc_s Thanks for the suggestion... I have modified it now. — Dark3963
– Dark3963, Commented Jul 2 at 15:33

d r · Accepted Answer · 2025-07-03 07:42:04Z

It would be easer if there were some sample data and expected result provided.
Anyway, with the blind guessed sample data like this:

--  S a m p l e    D a t a :
Create Table tbl_1 (buck Number(10), sname VarChar2(20), some_col VarChar2(24));

Insert Into tbl_1 
Select 101, 'abc', 'some 101' FRom Dual Union All
Select 101, 'def', 'some 101' FRom Dual Union All
Select 101, 'ghi', 'some 101' FRom Dual Union All
--
Select 102, 'abc', 'some 102' FRom Dual Union All
Select 102, 'def', 'some 102' FRom Dual Union All
Select 102, 'ghi', 'some 102' FRom Dual Union All
--
Select 103, 'abc', 'some 103' FRom Dual Union All
Select 103, 'def', 'some 103' FRom Dual Union All
Select 103, 'ghi', 'some 103' FRom Dual Union All
--
--
Select 999, 'jkl', 'some 999' FRom Dual Union All
Select 999, 'mno', 'some 999' FRom Dual Union All
Select 999, 'prs', 'some 999' FRom Dual;

Create Table tbl_2 (sname VarChar2(20), sdiv VarChar2(10), some_other_col VarChar2(12));

Insert Into tbl_2 
Select 'abc', 'xyz', 'some a' FRom Dual Union All
Select 'abc', 'tre', 'some tre' FRom Dual Union All
Select 'def', 'vuw', 'some d' FRom Dual Union All
Select 'ghi', 'tre', 'some g' FRom Dual Union All
Select 'jkl', 'pqr', 'some j' FRom Dual;

--  B e f o r e :
Select * From tbl_1 Order By buck, sname;

BUCK	SNAME	SOME_COL
101	abc	some 101
101	def	some 101
101	ghi	some 101
102	abc	some 102
102	def	some 102
102	ghi	some 102
103	abc	some 103
103	def	some 103
103	ghi	some 103
999	jkl	some 999
999	mno	some 999
999	prs	some 999

If I got it right the rows from the tbl_1 which are in bold letters above should be deleted cause in tbl_2 snames 'abc' and 'ghi' have the sdiv = 'tre'.

Maybe you don't need the procedure at all - try to do it using MERGE.

      MERGE INTO tbl_1 t
        USING ( select Distinct t1.sname 
                from   tbl_1 t1 
                inner join tbl_2 t2 ON (t2.sname = t1.sname And t2.sdiv = 'tre')
              ) x ON(x.sname = t.sname)
      WHEN MATCHED THEN 
        UPDATE SET t.some_col = 'delete this row'
        DELETE WHERE t.some_col = 'delete this row';

--  A f t e r :
Select * From tbl_1 Order By buck, sname;

BUCK	SNAME	SOME_COL
101	def	some 101
102	def	some 102
103	def	some 103
999	jkl	some 999
999	mno	some 999
999	prs	some 999

fiddle

...

OR if you want to delete all rows for buck related to sdiv = 'tre'

      MERGE INTO tbl_1 t
        USING ( select Distinct t1.buck 
                from   tbl_1 t1 
                inner join tbl_2 t2 ON (t2.sname = t1.sname And t2.sdiv = 'tre')
              ) x ON(x.buck = t.buck)
      WHEN MATCHED THEN 
        UPDATE SET t.some_col = 'delete this row'
        DELETE WHERE t.some_col = 'delete this row';

--  A f t e r :
Select * From tbl_1 Order By buck, sname;

BUCK	SNAME	SOME_COL
999	jkl	some 999
999	mno	some 999
999	prs	some 999

fiddle

Collectives™ on Stack Overflow

How to delete large number of records from a table faster in plsql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related