Re: Performance for deleting multiple records

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?

rrkapoor_at_hotmail.com (rishi) wrote in message news:<c43d208f.0406021300.7610401_at_posting.google.com>...
> 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','rrkapoor_at_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;
Received on Thu Jun 03 2004 - 05:43:03 CEST