Detach data using Partition Exchange

We have a core table t_ms_mdeia_task related to daily operations of an application. This table is a List partition made on a daily basis. The partition key is monthday, and the column value is similar to 'mmdd'. There are 366 partitions in total.

The daily data volume is more than 3 million, and the application requires only 31 days to be saved. All the previous data is migrated to the History Table for query. That is to say, we need to migrate data from the previous day to the hist table every day. Of course, the hist table is also partitioned.

The application side used the stored procedure to delete and insert large batches. For such operations, the process is very complex and affects the database performance.

View the submission process:

1. Create or replace procedure P_MS_MOVEMEDIATASK (
2. I _time DATE
3. )
4. AS
5. V_time DATE;
6. V_beginTime DATE;
7. V_monthday VARCHAR2 (4 );
8. V_month VARCHAR2 (2 );
9. V_count NUMBER;
10. V_ SQL VARCHAR2 (5000 );
11. V_ret VARCHAR2 (10 );
12. V_errMsg VARCHAR2 (500 );
13. V_total NUMBER;
14. BEGIN
15. V_beginTime:=SYSDATE;
16. -- Transfer Data 31 days ago
17. V_time:=I _time;
18. -- Retrieve the monthday of the transferred data
19. V_monthday:=To_char(V_time, 'mmdd ');
20. -- Use the month for transferring data to different tables by month
21. V_month:=To_char(V_time, 'mm ');
22. --
23. Select count (1)
24. INTO v_count
25. FROM T_MS_MEDIA_TASK T
26. WHERET. MONTHDAY=V_monthday;
27. V_total:=V_count;
28. WHILE (v_count>0) LOOP
29. -- Set the RESERVE79 field of the data to be transferred to 0.
30. UPDATE T_MS_MEDIA_TASK T
31. SETT. RESERVE79='0'
32. WHERET. MONTHDAY=V_monthday
33. AND ROWNUM<= 50000; -- up to 50000 entries can be submitted each time
35. -- Insert the data to be transferred into the history table and insert dynamic SQL statements to different historical tables on a monthly basis.
36. V_ SQL:='Insert into t_ms_media_task_his'| V_month
37. ...
38. ...
39. Execute immediate v_ SQL;
41. -- Delete transferred data
42. Delete from T_MS_MEDIA_TASK T
43. WHERET. MONTHDAY=V_monthday
44. ANDT. RESERVE79='0';
45. COMMIT;
46. -- Get the number of data records to be transferred
47. Select count (1)
48. INTO v_count
49. FROM T_MS_MEDIA_TASK T
50. WHERET. MONTHDAY=V_monthday;
51. End loop;
52. EXCEPTION
53. ...
54. END P_MS_MOVEMEDIATASK;
55. /

1 2 3 Next Page