Examples of Oracle deleting large amounts of data

First, the introduction
Never thought that deleting data from a table would be a problem. However, when the amount of data in a table is very large, deleting data can be a big problem.

This only describes the resolution process and does not involve the writing of SQL and stored procedures. The method is very simple, master detour.

Second, the scene
Run a production library for more than a year, about 50,000 data per hour, today found that there are one weeks of program problems, a full one weeks of data is duplicated. Duplicate data needs to be selected for deletion.

Iii. the process of settlement
A

Deleting duplicate data is simple, and using ROWID to eliminate it, the first idea is to use a SQL to solve the problem. Write it out quickly and validate it on the test library.

Open SQL Plus and execute delete SQL. The results were executed more than a day, and the SQL plus connection to the server was disconnected because it was too long.

I don't know if the connection timeout caused the SQL to run out, or if the connection disconnect transaction did not commit and rolled back. Specifically to check how to control the connection daze time, Oracle has a parameter can be controlled, idle_time, but the information found that long query, the connection will not be disconnected (it turns out that is not the case, a bit strange, the execution time is very long delete and long query is not the same??? Modify Idel_time will have a certain impact on the system operation, I decided or not to change it, to find ways to optimize the removal method.

Two

The next way to think about it is to find the ROWID of duplicate data records, save them to a temporary table, and then use the stored procedure to delete every 10,000 entries, to prevent excessive undo data, to improve efficiency, and to avoid connection timeouts and transaction rollback.

The result was also executed more than a day, no results, and the connection timed out.

Three

The execution of the day stored procedure will show the information of the Dbms_output.put_line output in the process, and a table is built to record the debug information during the execution of the stored procedure. It was found that the rowid of raw duplicate data was found and inserted into the temporary table for a very long time.

Reduce the scope of the lookup of duplicate data, and adjust it to remove it sequentially by looking for each hour, using the bulk operation (Buld collect Into,forall). Tested on the production library, about 5 minutes of execution completed, the effect is good.

Write a stored procedure, followed by an hourly deletion of the stored procedure.

Four

The result was no success, and the connection timed out. This connection timeout seems to be a real problem, possibly due to a connection timeout that caused the operation to fail. But I have a commit operation in each small stored procedure, that should also be able to complete some data deletion operation? In fact, no data has been deleted. I don't have a problem with this question.

The final solution is to use a simple and crude approach:

exec delete_by_hour (' 2016101400 ');

exec delete_by_hour (' 2016101401 ');

exec delete_by_hour (' 2016101402 ');

......

Wrote a number of such invocation procedures, copied to the Clipboard, and then glued to SQL Plus, which executed obediently one after another, and finally successfully removed all data, taking 13 hours.