Performance Optimization of Primary Night dimensional SQL

Recently, the unit moved from the National Convention Center to the clean-air Houshayu in Shunyi. Before the relocation, we encountered some thorny problems and some places worth using for reference.

This is an optimization of the night dimension program. The purpose of this night dimension is to delete 30 + tables of historical data every day, the main contradiction of which is a table of 50 million. The following is an introduction to the optimization of this table, which has gone through several stages.

Phase one:

Delete each table sequentially, for example, table An and BMagne B are table A child tables. Because the table has a primary and foreign key relationship, you need to delete table B first and then delete A. The deletion condition is to retrieve the record id corresponding to the historically expired data from table A, associate it with table B p_id and table An id, and perform deletion. The id field is the primary key of table A, using sequence assignment. P_id, id and c_date are all indexed. The total amount of data in A table is 20 million. The daily amount of data to be deleted in form An is 2 million, the total amount of data in form B is 50 million, and the amount of data to be deleted in form B is about 8 million per day. In order to reduce the pressure of UNDO and REDO, batch submissions are required. SQL is similar to the following

Delete from B where B.p_id in (select id from A where c_date