Single table close to the 1 billion line, want to direct execution truncate, clear all of the data and how to perform to avoid error, check some information said the index could be a problem, consult everybody!

CodePudding user response:

Can consider to delete the reconstruction,

PS: there is no question of direct truncate, feel free to do it,

CodePudding user response:

Your data is no longer, make sure the good, if truncate reduction very difficult, if not open file generally the DBA can not recover, if the data is sure don't feel free to delete it

CodePudding user response:

refer to the original poster lzhw8811282 response:

single table close to the 1 billion lines, want to direct execution truncate, clear all of the data and how to perform to avoid error, check some information said the index could be a problem, consult everybody!

Determine if the data is useless or to good backup, truncate, single table no partition, the index also won't have a problem,

CodePudding user response:

Question is the partition table table, check the said data partition table straight truncate leads to index

CodePudding user response:

reference data in your pocket 2 floor response:

do you have the data is no longer, make sure the good, if truncate reduction very difficult, if not open archive generally the DBA can not recover, if the data is sure don't feel free to delete it

partition table can truncate directly

CodePudding user response:

reference 5 floor lzhw8811282 reply:

Quote: refer to the second floor data pocket response:

Your data is no longer, make sure the good, if truncate reduction very difficult, if not open file generally the DBA can not recover, if the data is sure don't feel free to delete it

partition table can truncate it directly

Should be no problem, there is a problem is also a global index, the data is lost, such a big deal to rebuild the bai, should be built,

CodePudding user response:

Don't drop the data table, and then reconstructed

CodePudding user response:

refer to 7th floor weg910 response:

don't drop the data table, reconstruction

table data all don't, consider drop reconstruction, thank you

CodePudding user response:

reference data in your pocket 2 floor response:

do you have the data is no longer, make sure the good, if truncate reduction very difficult, if not open archive generally the DBA can not recover, if the data is sure don't feel free to delete it

Ha ha, you can rest assured truncate, if delete wrong, can look for us, as long as does not cover, still can

CodePudding user response:

Loop delete

 declare cursor ID_KEY_CURSOR is 
The SELECT t.s enddate senddate from ICDRPT. SUM_PROVINCE_BUSINESS_H15 t where substr (t.s enddate, 1, 8) & lt; '20190101'. 
The begin 
For V_TO_DEL ID_KEY_CURSOR in loop 
The DELETE FROM ICDRPT. SUM_PROVINCE_BUSINESS_H15 
WHERE ICDRPT. SUM_PROVINCE_BUSINESS_H15. SENDDATE=V_TO_DEL. SENDDATE; 
If the mod (ID_KEY_CURSOR % Rowcount, 5000)=0 
Then commit; 
end if; 
end loop; 
commit; 
end;