Performance for deleting multiple records

[rishi]

Looking for tips on how to improve performance on deleting records.
In our database we do dataloads daily that require us to purge
millions of records a day so any improvement in speed would be
welcomed.
CREATE OR REPLACE PROCEDURE ETL_CUSTATTRIB_STGTOTRG_ALT1v2 AS
TYPE cust_t IS TABLE OF customer_master.customer_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE attrib_t IS TABLE OF attribute_master.attribute_id%TYPE
INDEX BY BINARY_INTEGER;
attrib_id attrib_t;
cust_id cust_t;
i INTEGER;
insertCount INTEGER := 0;
recordCount INTEGER := 0;
msg VARCHAR(512);
startTime DATE;
endTime Date;
totalTime NUMBER;
msg_err VARCHAR(40);
hrs NUMBER;
mins NUMBER;
secs NUMBER;
cursor etl_cust_att_c1 is
select /*+ USE_HASH(customer_master, etl_cust_attributes) */
cm.customer_id, am.attribute_id
from attribute_master am, etl_cust_attributes eca, customer_master
cm
where eca.alt_customer_id = cm.alt_customer_id
and am.name = eca.attribute_name
order by cm.customer_id;
begin
SELECT SYSDATE INTO startTime FROM DUAL;
OPEN etl_cust_att_c1;
LOOP
EXIT WHEN etl_cust_att_c1%NOTFOUND;
COMMIT;
FETCH etl_cust_att_c1 BULK COLLECT INTO cust_id, attrib_id LIMIT
5000;

FORALL i IN 1..cust_id.COUNT
delete from cust_attributes_old where customer_id = cust_id(i) and
attribute_id = attrib_id(i);

insertCount := insertCount + SQL%ROWCOUNT;
recordCount := recordCount + cust_id.COUNT;

END LOOP;
COMMIT;
SELECT SYSDATE INTO endTime FROM DUAL;
totalTime := (endTime - startTime);
hrs := totalTime*24;
mins := (hrs-FLOOR(hrs))*60;
secs := (mins-FLOOR(mins))*60;
msg := utl_tcp.crlf ||'Total Rows Loaded = '||recordCount||
utl_tcp.crlf ||'Total Rows Deleted = '||insertCount||
utl_tcp.crlf ||'Session Start Time = '||TO_CHAR(startTime,'DAY
MON DD HH24:MI:SS YYYY')||
utl_tcp.crlf ||'Session Completion Time =
'||TO_CHAR(endTime,'DAY MON DD HH24:MI:SS YYYY')||
utl_tcp.crlf ||'Session Elapsed Time = '||FLOOR(hrs)||':'||
FLOOR(mins)||':'||FLOOR(secs)||' (H:M:S)'||
utl_tcp.crlf ||utl_tcp.crlf ||'Table Details:
CUST_ATTRIBUTES'||
utl_tcp.crlf ||'Rows Deleted Delete Throughput'||
utl_tcp.crlf ||'------------- -------------------'||
utl_tcp.crlf
||insertCount||' '||ROUND(insertCount/((endTime-startTime)*86400));
send_mail('Oracle ETL','r*******@household.com;','CUST_ATTRIBUTES
ALTI COMPLETED',msg,msg_err);
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS DELETED = '||insertCount);
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS LOADED= '||recordCount);
CLOSE etl_cust_att_c1;
end;

[Romeo Olympia]

Delete millions of records a day... This is a recipe for really,
really slow performance. And that's not just slow for your particular
delete job but potentially for your whole system as well.

There must be a better way to "remove" all those rows. Have you
considered partitioning?

rr******@hotmail.com (rishi) wrote in message news:<c4*************************@posting.google.c om>...

Looking for tips on how to improve performance on deleting records.
In our database we do dataloads daily that require us to purge
millions of records a day so any improvement in speed would be
welcomed.
CREATE OR REPLACE PROCEDURE ETL_CUSTATTRIB_STGTOTRG_ALT1v2 AS
TYPE cust_t IS TABLE OF customer_master.customer_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE attrib_t IS TABLE OF attribute_master.attribute_id%TYPE
INDEX BY BINARY_INTEGER;
attrib_id attrib_t;
cust_id cust_t;
i INTEGER;
insertCount INTEGER := 0;
recordCount INTEGER := 0;
msg VARCHAR(512);
startTime DATE;
endTime Date;
totalTime NUMBER;
msg_err VARCHAR(40);
hrs NUMBER;
mins NUMBER;
secs NUMBER;
cursor etl_cust_att_c1 is
select /*+ USE_HASH(customer_master, etl_cust_attributes) */
cm.customer_id, am.attribute_id
from attribute_master am, etl_cust_attributes eca, customer_master
cm
where eca.alt_customer_id = cm.alt_customer_id
and am.name = eca.attribute_name
order by cm.customer_id;
begin
SELECT SYSDATE INTO startTime FROM DUAL;
OPEN etl_cust_att_c1;
LOOP
EXIT WHEN etl_cust_att_c1%NOTFOUND;
COMMIT;
FETCH etl_cust_att_c1 BULK COLLECT INTO cust_id, attrib_id LIMIT
5000;

FORALL i IN 1..cust_id.COUNT
delete from cust_attributes_old where customer_id = cust_id(i) and
attribute_id = attrib_id(i);

insertCount := insertCount + SQL%ROWCOUNT;
recordCount := recordCount + cust_id.COUNT;

END LOOP;
COMMIT;
SELECT SYSDATE INTO endTime FROM DUAL;
totalTime := (endTime - startTime);
hrs := totalTime*24;
mins := (hrs-FLOOR(hrs))*60;
secs := (mins-FLOOR(mins))*60;
msg := utl_tcp.crlf ||'Total Rows Loaded = '||recordCount||
utl_tcp.crlf ||'Total Rows Deleted = '||insertCount||
utl_tcp.crlf ||'Session Start Time = '||TO_CHAR(startTime,'DAY
MON DD HH24:MI:SS YYYY')||
utl_tcp.crlf ||'Session Completion Time =
'||TO_CHAR(endTime,'DAY MON DD HH24:MI:SS YYYY')||
utl_tcp.crlf ||'Session Elapsed Time = '||FLOOR(hrs)||':'||
FLOOR(mins)||':'||FLOOR(secs)||' (H:M:S)'||
utl_tcp.crlf ||utl_tcp.crlf ||'Table Details:
CUST_ATTRIBUTES'||
utl_tcp.crlf ||'Rows Deleted Delete Throughput'||
utl_tcp.crlf ||'------------- -------------------'||
utl_tcp.crlf
||insertCount||' '||ROUND(insertCount/((endTime-startTime)*86400));
send_mail('Oracle ETL','r*******@household.com;','CUST_ATTRIBUTES
ALTI COMPLETED',msg,msg_err);
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS DELETED = '||insertCount);
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS LOADED= '||recordCount);
CLOSE etl_cust_att_c1;
end;