Are made before the delete to delete a few data, no contact with such a large number of deleted, now a little helpless, trouble bosses help
There is a table for: INTERFACE_FROM_LOG, has more than 1000 W data
To: REQUEST_TIME this time field is less than the 2020-01-01 this condition to delete
Could you tell me how to write big specific SQL the?

CodePudding user response:

Bosses, for help

CodePudding user response:

Didn't understand, what is the relationship between more than one thousand, as the delete

CodePudding user response:

I novice small white, LZ try this line not line, a timebox delete
The delete from interface_from_log where request_time between to_date (' 2018-01-01 00:00:00 ', 'yy - mm - dd hh24: mi: ss') and to_date (' 2020-01-01 ', 'yy - mm - dd hh24: mi: ss')

CodePudding user response:

If direct delete, cause memory still occupied, this how to solve?

CodePudding user response:

Partial deletion went, every time delete line 10000, delete completed immediately perform a commit;

CodePudding user response:

Simple and crude, according to the time to add partition, need to delete data, directly deleted partitions,

CodePudding user response:

Give you an idea, if you remove most of the data in the table, can CTAS new table of the need to keep the data inserted into a new table, and then rename way switch,

CodePudding user response:

Don't delete
1, the CREATE TABLE INTERFACE_FROM_LOG_BAK AS SELECT * FROM INTERFACE_FROM_LOG WHERE REQUEST_TIME & gt;='2020-01-01'; Do you have any questions - select see retain data
2, the DROP TABLE INTERFACE_FROM_LOG;
3, CREATE TABLE INTERFACE_FROM_LOG AS SELECT * FROM INTERFACE_FROM_LOG_BAK;

CodePudding user response:

Too much rollback segments will not use the delete data and line by line operation is slow, even by the host's commit delete or slow, bypassing the delete directly

CodePudding user response:

If long-term have this operation demand or considering building partition table is better, as you delete selected conditions

CodePudding user response:

reference data in your pocket, 9/f, reply:

too much rollback segments will not use the delete data and line by line operation is slow, even by the host's commit delete or slow, directly to bypass the delete the

Bosses means to keep need to investigate to first and then into a temporary table, then delete the original table, reading back in to keep the original form

CodePudding user response:

Using a temporary table to record the primary key, then use the cursor circulation data of temporary tables to delete the source table, every time is to delete a record, a certain number commit once, so as not to take up

CodePudding user response:

Create a temporary table with structure, greater than or equal to 20200101 data to the temporary table, check the store data is correct, the original truncate table, put a temporary table data back to the original table, less than three months of data quickly, operating behind this list will be a lot faster

CodePudding user response:

1, remove a large amount of data, is the best way to drop partition or truncate partition to operate;
2, according to the meaning of the building Lord, should be to remove most of the data in a table, is to delete all data before January 1, 2020, in fact, if server performance can directly delete from INTERFACE_FROM_LOG where REQUEST_TIME & lt; '2020-01-01';
Otherwise, you have to think of some way to, such as:
The alter table INTERFACE_FROM_LOG rename to INTERFACE_FROM_LOG_bak;
The create table INTERFACE_FROM_LOG as select * from the from INTERFACE_FROM_LOG where REQUEST_TIME & gt;='2020-01-01';
The key to see how many data delete,

CodePudding user response:

1, can I will be greater than the first time in a new form, and then delete this table, then the new table name to the table,
2, if it is in accordance with the time partition, I write anonymous block, delete data partition line,
If these two methods can't, you can delete it,

CodePudding user response:

You this table well suited to use partition table,
Don't trouble to himself,

CodePudding user response:

Partition table how to delete the data and the partition table is not the same

CodePudding user response:

Or advice to time or you can directly use the interval partitioning,,,, maintenance is very convenient,

The delete method is easy to create performance problems, imagine one day suddenly delete failed, to roll back,,,

CodePudding user response:

 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;