Want to do A test for cleaning up the database, clean up the table A table rather than B and C table data, the three tables has A same field ids family bond, I wrote A stored procedure, the general idea is to delete data query come out first in the cursor, and then open the cursor loop delete A where Anderson, D=cursor variable. ID, once every 5000 submitted, the three table data in 300 million or so, ask you do you have any other ideas? Or in what way can query maximum efficiency to execute queries that step?

CodePudding user response:

Put the cursor inside, the feeling is better than to put a temporary table, then add a flag field, batch delete, and change the flag values

CodePudding user response:

Put the cursor in the efficiency is not too high, the temporary table is a good plan, marked deleted or can be,

CodePudding user response:

Want to delete the data quantity is how many? To preserve the data how many?
If you remove the amount of reserved than larger, can consider to delete all insert again after practice

CodePudding user response:

The big table can't make partition table? Deleted partitions high efficiency a lot

CodePudding user response:

Still depends on the amount of data, b and c table if it is a big table, b and c is small, can be used to cut a table processing
Queries in different conditions, generate build temporary table, then funnel
For example,
Select * from a where not the exists (select 1 from b where Anderson, d=b.i d) and not the exists (select from 1 c where Anderson, d=c.i d) and mod (Anderson, d, 10)=0.

CodePudding user response:

This is the main is to see you delete the amount of data, or keep the amount of data,
Less to keep the amount of data, it is suggested that the result data to a new watch move back,

In principle is not recommended for large data using the cursor, the efficiency will be much lower than the SQL method,