Actual cases:There are several different scenarios with different deletion strategies.
The second type:Delete all the data of 60 million. You can delete it with delete and where. However, considering the efficiency problem, we directly use truncate syntax: truncate table name
The third kind: delete department 10 from the data of 60 million, and delete can be used, but the efficiency of deletion is very low. You must cycle 60 million times, judge each time, and then have effect.
It needs to be quoted hereMerge Into, which is equivalent to that after incremental synchronous insertion,Incremental update data, which also includes deletion。
Grammatical format: merge (not all databases are common)
Merge into target table using (increment)
On (match field)
When matched then update set — there is no need to add a table name between update and set
When not matched then insert value — there is no need to add the into table name between insert and values
Q: Can’t delete statement be used directly?
A: No, because when matching with matching fields, weYou must use the update statement。
Q: Why, in general, is this better than delete plus where?
A: (my personal understanding, which may be corrected later) because I am connected with myself.
Department 10 is regarded as a whole. When it is matched to department 10 in this table, it is a bit like index. If the match is successful, it will be deleted directly.
MERGE INTO EMP E USING (SELECT * FROM EMP WHERE DEPTNO = 10) S ON (S.EMPNO = E.EMPNO) WHEN MATCHED THEN UPDATE SET E.COMM = E.COMM DELETE WHERE 1 = 1;
The fourth kind: the data volume of its own department 10 is also large, accounting for more than 95% of the total table. If it is deleted directly, it will take a long time to execute.The idea is: let’s create a temporary table, and thenSelect only Department 20, Department 30 and department 40That’s fine.