How to quickly delete many records from a SQL database?

Question

We have a table with approximately 1.5 million records. This table has many FK relationships from different tables.

The problem is that 1 million records are simply duplicates that need to be deleted. We are trying to delete 1000 records at a time, but this is a very slow process.

What I mean is to copy temporary entries that should remain in the new table. Truncate existing ones and copy the entries that should remain. With the restoration of the primary key and all relations with other tables. Therefore, on the client side, you do not see any difference.

Not sure if it is effective or not.

If I wanted to see the main implementation, so that I can follow and apply to my case. If not, I would like to see an effective way to do this.

thanks

Answer 1

Our company has a bunch of temporary data stored in databases. When we need to remove a bunch of them, we break it into several hundred lines and delete their pieces at a time. We have an application whose sole purpose is to repeat several such queries over and over:

with topFew as (select top 100 * from table) delete topFew 

I suggest you hack something simple like this, and just let it work for hours. Go work on something else while it is being processed.

Answer 2

Delete efficiency can be improved by joining the table yourself using rowid. It can even be optimized using volumetric collection and FORALL

  DECLARE limit_in integer; CURSOR C1 is Select min(b.rowid) from table_name a, table_name b where a.primary_key = b.primary_key; TYPE C1_rec IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER; C1_record C1_rec BEGIN limit_in:=10000 --- Can be changed based on performance OPEN C1; LOOP FETCH C1 BULK COLLECT INTO C1_record LIMIT limit_in; FORALL indx in 1..c1_record.count DELETE FROM table_name where row_id = C1_record(i); commit; END LOOP; END; 

The table to be deleted has child tables. Thus, the restriction will be violated.

So, before executing the above code snippet, this is the best option to change the foreign key constraint to REMOVE CASCADE. We cannot change the restriction to add the delete cascade. Thus, the foreign key must be reset and recreated to remove the cascade

  ALTER child_table ADD CONSTRAINT fk_name foreign_key (C1) references parent_table (C2) on delete cascade; 

Delete cascade will also clear your child tables.