Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect
Bulk collect [message #204910] Wed, 22 November 2006 10:03 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
How can I delete records on table using forall in bulk collect by matching the rowid?
Re: Bulk collect [message #204919 is a reply to message #204910] Wed, 22 November 2006 10:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just like you would with any other use of FORALL.
SQL> create table temp_table as select lvl from (select level lvl from dual connect by level <= 10);

Table created.

SQL> 
SQL> declare
  2  
  3    type ty_rowid is table of rowid index by binary_integer;
  4    t_rowid  ty_rowid;
  5    
  6    v_count   pls_integer;
  7  begin
  8  
  9    select count(*)
 10    into   v_count
 11    from   temp_table;
 12    
 13    dbms_output.put_line('Records before '||v_count);
 14  
 15    select rowid 
 16    bulk collect into t_rowid
 17    from temp_table;
 18  
 19    
 20    forall idx in t_rowid.first .. t_rowid.last
 21    delete temp_table where rowid = t_rowid(idx);
 22    
 23    select count(*)
 24    into   v_count
 25    from   temp_table;
 26    
 27    dbms_output.put_line('Records after '||v_count);  
 28  end;
 29  /
Records before 10
Records after 0

PL/SQL procedure successfully completed.


In this case certainly, it'd be quicker to just delete the rows rather than select their rowids and then delete them.
Re: Bulk collect [message #205815 is a reply to message #204919] Mon, 27 November 2006 14:37 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
This is work, but I have to delete records from multiple tables, and I have error:
and item_no = del_record_obj.item_no (idx);
*
ERROR at line 60:
ORA-06550: line 60, column 81:
PLS-00201: identifier 'IDX' must be declared

Here is my code:

declare

TYPE rowidarray IS TABLE OF ROWID
INDEX BY PLS_INTEGER;

TYPE del_claim_record IS RECORD (
row_id rowidarray,
SERVICE_ID DBMS_SQL.number_table,
ITEM_NO DBMS_SQL.number_table
);

del_record_obj del_record;

begin

SELECT row_id, service_id, item_no
BULK COLLECT INTO del_record_obj.row_id, del_record_obj.service_id, del_record_obj.item_no
from serv_extract_table;

FORALL idx IN 1 .. del_record_obj.row_id.COUNT
DELETE serv_extract_table
WHERE ROWID = del_record_obj.row_id (idx)
and service_id = del_record_obj.service_id (idx)
AND item_no = del_record_obj.item_no (idx);

DELETE from req_table
where service_id = del_record_obj.service_id (idx)
and item_no = del_record_obj.item_no (idx);

end;
/


Thanks.
Re: Bulk collect [message #205818 is a reply to message #205815] Mon, 27 November 2006 15:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The identifier IDX is only in scope for the piece of SQL inside the FORALL statement. it has no existance outside that statement.
Re: Bulk collect [message #206369 is a reply to message #204919] Wed, 29 November 2006 14:54 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
May I open another FORALL statement, like this:

declare

TYPE rowidarray IS TABLE OF ROWID
INDEX BY PLS_INTEGER;

TYPE del_claim_record IS RECORD (
row_id rowidarray,
SERVICE_ID DBMS_SQL.number_table,
ITEM_NO DBMS_SQL.number_table
);

del_record_obj del_record;

begin

SELECT row_id, service_id, item_no
BULK COLLECT INTO del_record_obj.row_id, del_record_obj.service_id, del_record_obj.item_no
from serv_extract_table;

begin

FORALL idx IN 1 .. del_record_obj.row_id.COUNT
DELETE serv_extract_table
WHERE ROWID = del_record_obj.row_id (idx)
and service_id = del_record_obj.service_id (idx)
AND item_no = del_record_obj.item_no (idx);

end;

begin
FORALL idx IN 1 .. del_record_obj.row_id.COUNT
DELETE from req_table
where service_id = del_record_obj.service_id (idx)
and item_no = del_record_obj.item_no (idx);

end;

end;
/

I have to delete million records from couple tables.
I tried those logic or cursor:

declare

v_bulk_limit PLS_INTEGER := 15000;

TYPE rowidarray IS TABLE OF ROWID
INDEX BY PLS_INTEGER;

TYPE del_claim_record IS RECORD (
row_id rowidarray,
SERVICE_ID DBMS_SQL.number_table,
ITEM_NO DBMS_SQL.number_table
);

del_record_obj del_record;

CURSOR c_del_record
IS

SELECT row_id, service_id, item_no
from serv_extract_table;

BEGIN

OPEN c_del_record ;

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

begin

FORALL idx IN 1 .. del_record_obj.row_id.COUNT
DELETE serv_extract_table
WHERE ROWID = del_record_obj.row_id (idx)
and service_id = del_record_obj.service_id (idx)
AND item_no = del_record_obj.item_no (idx);

end;

begin
FORALL idx IN 1 .. del_record_obj.row_id.COUNT
DELETE from req_table
where service_id = del_record_obj.service_id (idx)
and item_no = del_record_obj.item_no (idx);

end;

end;
/

What is better?

Thanks.
Re: Bulk collect [message #206446 is a reply to message #206369] Thu, 30 November 2006 01:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the Delete from SERV_EXTRACT_TABLE, why are you specifying other columns as well as the ROWID - ROWID is a unique record determiner.

To be honest, both of those will be slower than simply doing an SQL delete.
Currently you are doing a SELECT from SERV_EXTRACT_TABLE, followed by a DELETE from SERV_EXTRACT_TABLE, and a DELETE from REQ_TABLE.

It would almost certainly be quicker to do:
BEGIN
  DELETE req_table 
  WHERE  (service_id,record_no) IN (SELECT service_id,record_no FROM serv_extract_Table)

  DELETE serv_extract_Table;
END;
Re: Bulk collect [message #207230 is a reply to message #206446] Mon, 04 December 2006 15:05 Go to previous message
uicmxz
Messages: 48
Registered: July 2006
Member
The reason why I'm using BULK COLLECT, because I have to delete large amount of records from ten tables and I have problem with rollback segment: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TS'.

Now I'm trying this way:

declare
v_bulk_limit PLS_INTEGER := 15000;

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 service_date > '01-JAN-2004';

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;
/

Is there any other way to speed up process and avoid rollback problem?
Previous Topic: Hide a URL Redirect
Next Topic: The 2 queries that are supposed to return the same values, return different values:
Goto Forum:
  


Current Time: Sun Dec 11 08:03:33 CST 2016

Total time taken to generate the page: 0.15903 seconds