Dear AskTom team,
I really like the feature "Returning bulk collect into" during dlm statement, when doing 2 things in one step:
Dml statement itself
Store values from the table that are going to be changed for further processing
Nevertheless, imagine that I am going to delete milions of rows and what is an impact on PGA.
So, temporary table comes into my mind - store changed values into temporary table instead of collection.
So my example:
create table t1 as select * from all_objects;
create table t_del (
OWNER VARCHAR2(128)
,OBJECT_NAME VARCHAR2(128)
,SUBOBJECT_NAME VARCHAR2(128)
,OBJECT_ID NUMBER
,DATA_OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(23));
/
create or replace force type obj_t_del as object (
OWNER VARCHAR2(128)
,OBJECT_NAME VARCHAR2(128)
,SUBOBJECT_NAME VARCHAR2(128)
,OBJECT_ID NUMBER
,DATA_OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(23));
/
create or replace force type tab_t_del as table of obj_t_del;
declare
l_tab_t_del tab_t_del;
begin
delete from t1
where OBJECT_TYPE='INDEX'
returning obj_t_del( OWNER
,OBJECT_NAME
,SUBOBJECT_NAME
,OBJECT_ID
,DATA_OBJECT_ID
,OBJECT_TYPE)
bulk collect into l_tab_t_del;
insert into t_del
select * from table(l_tab_t_del);
end;
/
select count(*) from t_del;
COUNT(*)
---------
1459following does not work:
create global temporary table t_del_temp (
OWNER VARCHAR2(128)
,OBJECT_NAME VARCHAR2(128)
,SUBOBJECT_NAME VARCHAR2(128)
,OBJECT_ID NUMBER
,DATA_OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(23))
on commit delete rows;
begin
delete from t1
where OBJECT_TYPE='INDEX'
returning obj_t_del( OWNER
,OBJECT_NAME
,SUBOBJECT_NAME
,OBJECT_ID
,DATA_OBJECT_ID
,OBJECT_TYPE)
bulk collect into t_del_temp;
insert into t_del
select * from t_del_temp;
end;
/
ERROR at line 10:
ORA-06550: line 10, column 22:
PLS-00403: expression 'T_DEL_TEMP' cannot be used as an INTO-target of a SELECT/FETCH statement
ORA-06550: line 10, column 32:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 2, column 2:
PL/SQL: SQL Statement ignoredSo, my question is, is it ever possible to use temporary table instead of collection?
Or some tip for using collection as in first statement, but with good performance and with minimal impact on PGA?
Thanks,
Dusan
A temporary table acts like a regular table - you can't return rows from a DML statement into it like that.
To use it, first insert the rows into it, then delete using the data in the GTT. For example:
begin
insert into t_del_temp
select owner
,object_name
,subobject_name
,object_id
,data_object_id
,object_type
from t1
where object_type = 'INDEX';
delete from t1
where t1.object_id in (
select d.object_id from t_del_temp d
);
end;
/If you want to stick with collections, you can control how many rows you get by using bulk collection with a limit. To do this, you'll need to declare an explicit cursor and loop through it fetching N records at a time.
Which looks something like:
open cur;
loop
fetch cur
bulk collect into arr
limit 100;
exit when arr.count = 0;
forall i in 1 .. arr.count
delete ...
end loop;
close cur;Read more about this at:
https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall Which of these methods is "better" depends on what your goals are.