[pl / SQL] four cases of deleting table data

Time:2020-8-16

Actual cases:There are several different scenarios with different deletion strategies.

[pl / SQL] four cases of deleting table data

First:Delete department 10 data from 1 million data.
Analysis: the order of magnitude is relatively small. You can use delete and where to delete directly.

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.

Recommended Today

JSP page ie cannot open Internet site Resolution of terminated operations

The reason for the coincidence is that all kinds of factors contributed to the event, which is indispensable. Several factors are: ie in the system is IE6 (I’m not sure about the difference between the specific small versions), JSP source code written by JSP page and automatically generated by development tools, and the use of […]