469,087 Members | 1,298 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,087 developers. It's quick & easy.

Performance for deleting multiple records

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;
Jul 19 '05 #1
1 7660
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;

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Bob Darlington | last post: by
4 posts views Thread by Vadivel Kumar | last post: by
3 posts views Thread by bharadwajrv | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.