-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDelete-Millions-of-Rows-Fast-with-SQL.sql
127 lines (101 loc) · 4.05 KB
/
Delete-Millions-of-Rows-Fast-with-SQL.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
Skip to content
Search or jump to…
Pull requests
Issues
Marketplace
Explore
@nkhlbr
nkhlbr
/
Delete-Millions-of-Rows-Fast-with-SQL
1
00
Code
Issues
Pull requests
Actions
Projects
Wiki
Security
Insights
Settings
Delete-Millions-of-Rows-Fast-with-SQL/Delete Millions of rows via batch process.txt
@nkhlbr
nkhlbr Add files via upload
Latest commit 8a4ac9f on Sep 3, 2020
History
1 contributor
83 lines (60 sloc) 3.48 KB
/*
-- *********************************************************************************************
-- Description : Delete the data selectively in the DW2_Ext_HIST table.
--
-- Arguments : row_cnts1 - contains no. of records in DW2_Ext_HIST table before deletion
--row_cnts2 - contains no. of records in DW2_Ext_HIST table after deletion
--li_Months - set the limit on of months for archive data
--li_DeleteCnt - Total rows deleted
--li_MinBatch - Minimum DW_ETLNUM
--li_MaxBatch - Maximum DW_ETLNUM
--li_batch_DW_ETL - No.of counts in a batch
--
-- Modification History
-- Programmer Date Description
-- -------------------- ---------- ---------------------------------------------------------------
-- N Barua 04/12/2020 Original Script.
-- N Barua 04/12/2020 Modified the script and added batch variable li_batch_DW_ETL
-- *********************************************************************************************
-- Copyright NB
-- *********************************************************************************************
*/
--Execute the code block in a separate window to increase the lines in DBMS Output
--SET SERVEROUTPUT ON SIZE 250000
--SET LINESIZE 500
--SET TRIMSPOOL ON
DECLARE
row_cnts1 NUMBER := 0;
row_cnts2 NUMBER := 0;
li_Months NUMBER := 6;
li_DeleteCnt NUMBER := 0;
li_MinBatch NUMBER := 1;
li_MaxBatch NUMBER := 90;
li_batch_DW_ETL NUMBER := 7;
BEGIN
---------------------------------No. of records before deletion----------------------------------------------------------------------------------------------------------
SELECT COUNT(*) INTO row_cnts1
FROM DW2_Ext_HIST;
DBMS_OUTPUT.PUT_LINE ('Total count of the records before deletion: ' || ' ' || row_cnts1);
---------------------------------Selective Deletion of records----------------------------------------------------------------------------------------------------------
FOR del_rec IN li_MinBatch..(li_MinBatch + li_batch_DW_ETL)
LOOP
DELETE FROM
DW2_Ext_HIST DEDH1
WHERE DEDH1.DW_ETLDATE <= ADD_MONTHS(SYSDATE, -li_Months)
AND DEDH1.DW_ETLNUM <= li_MaxBatch;
--AND ROWNUM < li_RowNum;
li_MinBatch := li_MinBatch + li_batch_DW_ETL; --loops per batch of li_batch_DW_ETL records
EXIT WHEN li_MinBatch > li_MaxBatch;
li_DeleteCnt:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE ('Rows Deleted: '|| li_DeleteCnt); --Displays total rows deleted
DBMS_OUTPUT.PUT_LINE ('Iteration Number: ' || del_rec); -- Current Iteration Number
DBMS_OUTPUT.PUT_LINE ('Min Batch Number in a iteration: ' || li_MinBatch); --Minimum record
DBMS_OUTPUT.PUT_LINE ('Counts in a Batch per increment: ' || li_batch_DW_ETL); --Total count in a batch
--COMMIT;
END LOOP;
---------------------------------No. of records after deletion----------------------------------------------------------------------------------------------------------
SELECT COUNT(*) INTO row_cnts2
FROM DW2_Ext_HIST;
DBMS_OUTPUT.PUT_LINE ('Total count of the records after deletion: ' || row_cnts2);
END;
© 2021 GitHub, Inc.
Terms
Privacy
Security
Status
Docs
Contact GitHub
Pricing
API
Training
Blog
About
Loading complete