Oracle 将批量收集返回临时表

亲爱的AskTom团队，

我真的很喜欢dlm语句期间 “返回批量收集到” 的功能，一步做两件事:

Dml语句本身
存储将要更改的表中的值以进行进一步处理

不过，想象一下，我将删除百万行以及对PGA的影响。
因此，临时表进入我的脑海-将更改的值存储为临时表而不是集合。

所以我的例子:

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(*)
---------
     1459

以下不起作用:

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 ignored

所以，我的问题是，有没有可能使用临时表而不是收集？
还是像第一句话一样使用集合的一些技巧，但性能好，对PGA的影响最小？

谢谢，

杜桑

临时表的行为就像常规表-您不能像这样将DML语句中的行返回到它中。

要使用它，首先将行插入到其中，然后使用GTT中的数据删除。例如:

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

如果要坚持使用集合，则可以通过使用具有限制的批量集合来控制获得的行数。为此，您需要声明一个显式游标，并通过它循环一次获取N条记录。

这看起来像:

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;

在以下位置阅读有关此的更多信息:

https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall

这些方法中哪一种 “更好” 取决于你的目标是什么。