Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT
BULK COLLECT [message #208606] Mon, 11 December 2006 09:10 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
I have to delete large amount of records from ten tables (data warehouse) and I have problem with rollback segment: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TS'.
I'm using BULK COLLECT method to reduce number of records to be looked up in the cursor (v_bulk_limit = 20000). This process will be slower than simply doing an SQL delete.
Is there any other way to speed up process and avoid rollback problem?


declare
v_bulk_limit PLS_INTEGER := 20000;

TYPE del_claim_record IS RECORD (
SERVICE_ID DBMS_SQL.number_table,
ITEM_NO DBMS_SQL.number_table
);
del_record_obj del_record;

CURSOR c_del_record
IS
SELECT service_id, item_no
from serv_extract_table
where
(status_cd = 'D' and process_ind <> 'Y'
and service_date < '01-DEC-2006')
or (status_cd = 'P' and process_ind = 'Y'
and service_date < '01-DEC-2006');

BEGIN

OPEN c_del_record ;

LOOP
FETCH c_del_record
BULK COLLECT INTO del_record_obj.service_id, del_record_obj.item_no LIMIT v_bulk_limit;

begin

FORALL i IN del_record_obj.service_id.first .. del_record_obj.service_id.last
DELETE serv_extract_table
WHERE service_id = del_record_obj.service_id (idx)
AND item_no = del_record_obj.item_no (idx);

end;

begin
FORALL i IN del_record_obj.service_id.first .. del_record_obj.service_id.last
DELETE from req_table
where service_id = del_record_obj.service_id (idx)
and item_no = del_record_obj.item_no (idx);

end;

end;
/

Re: BULK COLLECT [message #208626 is a reply to message #208606] Mon, 11 December 2006 09:59 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member



Well I think you have first do search on this forum

http://www.orafaq.com/forum/?SQ=beeab575b52afa59b5eb4e96bb8a4d38&t=search&srch=huge+delete&btn_submit=Search&field=al l&forum_limiter=6&search_logic=AND&sort_order=DESC&author=

also

http://asktom.oracle.com/pls/ask/f?p=4950:8:5200313302144283370::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330

There's alot of good hints/info on this

[Updated on: Mon, 11 December 2006 10:01]

Report message to a moderator

Previous Topic: Limit at the names of Tables?
Next Topic: MeSSaGe from a Trigger
Goto Forum:
  


Current Time: Sat Dec 03 20:29:52 CST 2016

Total time taken to generate the page: 0.03310 seconds