Thanks for the question, Girish.

Asked: July 11, 2016 - 4:23 am UTC

Last updated: January 18, 2019 - 2:49 pm UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I have below question on FORALL

1)

forall 1 .. l_var.count
 delete tab1 where id=l_var(i);
forall 1 .. l_var.count 
 delete tab2 where id=l_var(i);
forall 1 .. l_var.count 
 delete tab3 where id=l_var(i);

forall 1 .. l_var.count
execute immediate 'begin
 delete tab1 where id=:1;
 delete tab2 where id=:1;
 delete tab3 where id=:1;
end;' using l_var(i);

In above two code snippets which is good from performance perspective ?
Is second approach recommended ?

Thanks,
Girish

and Connor said...

For me, less dynamic SQL is always a good thing...and in terms of timing, they are very similar, so I'd recommend (1)


SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from t1;

Table created.

SQL> create table t3 as select * from t1;

Table created.

SQL>
SQL> create index ix1 on t1 ( object_id );

Index created.

SQL> create index ix2 on t2 ( object_id );

Index created.

SQL> create index ix3 on t3 ( object_id );

Index created.

SQL>
SQL> set serverout on
SQL> declare
  2    type numlist is table of number(10) index by pls_integer;
  3    n numlist;
  4    ts timestamp;
  5  begin
  6    dbms_random.seed(0);
  7
  8    select trunc(dbms_random.value(1,100000)) bulk collect into n
  9    from dual connect by level <= 1000;
 10
 11    ts := systimestamp;
 12    forall i in n.first .. n.last
 13      execute immediate 'begin
 14      delete from t1 where object_id = :1;
 15      delete from t2 where object_id = :1;
 16      delete from t3 where object_id = :1;
 17      end;' using n(i);
 18
 19    dbms_output.put_line(sql%rowcount);
 20    dbms_output.put_line(systimestamp-ts);
 21  end;
 22  /
1000
+000000000 00:00:00.324000000

PL/SQL procedure successfully completed.

SQL>
SQL>

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from t1;

Table created.

SQL> create table t3 as select * from t1;

Table created.

SQL>
SQL> create index ix1 on t1 ( object_id );

Index created.

SQL> create index ix2 on t2 ( object_id );

Index created.

SQL> create index ix3 on t3 ( object_id );

Index created.

SQL> set serverout on
SQL> declare
  2    type numlist is table of number(10) index by pls_integer;
  3    n numlist;
  4    ts timestamp;
  5  begin
  6    dbms_random.seed(0);
  7
  8    select trunc(dbms_random.value(1,100000)) bulk collect into n
  9    from dual connect by level <= 1000;
 10
 11    ts := systimestamp;
 12    forall i in n.first .. n.last
 13      delete from t1 where object_id = n(i);
 14    forall i in n.first .. n.last
 15      delete from t2 where object_id = n(i);
 16    forall i in n.first .. n.last
 17      delete from t3 where object_id = n(i);
 18
 19    dbms_output.put_line(sql%rowcount);
 20    dbms_output.put_line(systimestamp-ts);
 21  end;
 22  /
949
+000000000 00:00:00.207000000

PL/SQL procedure successfully completed.

SQL>
SQL>

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Girish Jahagirdar, July 11, 2016 - 8:09 am UTC

Excellent ! Thanks much for help

July 12, 2016 - 1:28 am UTC

glad we could help

bulk collect formal

A reader, January 18, 2019 - 4:40 am UTC

Hi Team,

how to write insert else update in bulk collect forall statement

Regards,
Akhlesh

January 18, 2019 - 2:49 pm UTC

You mean a forall merge?

create table t (
  c1 int primary key,
  c2 int
);

insert into t values ( 1, 1 );
commit;

declare
  type tab is table of t%rowtype index by pls_integer;
  rec tab;
begin

  rec(1).c1 := 1;
  rec(1).c2 := 100;
  
  rec(2).c1 := 2;
  rec(2).c2 := 2;
  
  forall i in 1 .. rec.count 
    merge into t
    using ( select rec(i).c1 c1, rec(i).c2 c2 from dual ) r
    on   ( t.c1 = r.c1 )
    when not matched then
      insert values ( r.c1, r.c2 )
    when matched then 
      update set c2 = rec(i).c2;
  
end;
/

select * from t;

C1   C2    
   1   100 
   2     2

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

bulk collect formal

More to Explore

PL/SQL demos

PL/SQL docs

Question on multiple DML in FORALL

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

bulk collect formal

More to Explore

PL/SQL demos

PL/SQL docs