Straight SQL always put perform PL/SQL?

From: <dshproperty_at_gmail.com>
Date: Mon, 10 Dec 2007 06:51:11 -0800 (PST)
Message-ID: <1ad08016-0850-4030-acc6-98f08516c993@d21g2000prf.googlegroups.com>

http://tkyte.blogspot.com/2006/10/slow-by-slow.html In there Tom Kytes says:
* You should do it in a single SQL statement if at all possible.

• If you cannot do it in a single SQL Statement, then do it in PL/SQL.

I was a true beliver of that until this case. We have a huge table, and we need to update it with
values from another table.

Let me give you an example
create table test (id number,col1 varchar2(20)) pctfree 99; (this is only for creating a huge table with lots of columns) declare
v_number number :=1;
begin
loop
insert into test values (v_number, 'a'); v_number := v_number+1;
exit when v_number > 1000000;
end loop;
commit;
end;

create table test1 as select * from test where id < 400000; update test1 set col1='b';
commit;

create index test_ind on test(id);
create index test1_ind on test1(id);
analyze table test compute statistics;
analyze table test1 compute statistics;
SQL> update test

     set col1=(select col1 from test1
     where test.id=test1.id )
     where exists
     (select 1 from test1
     where test.id=test1.id);

399999 rows updated.
Elapsed: 00:04:15.01
SQL> declare
  2 v_count number;
  3 cursor test_curs is
  4 select test.id,test.col1 from test,test1   5 where test.id=test1.id;
  6 begin
  7 for test_cur in test_curs
  8 loop
  9 update test
 10 set col1=test_cur.col1
 11 where id=test_cur.id;
 12 end loop;
 13 commit;
 14 end;
 15 /
PL/SQL procedure successfully completed. Elapsed: 00:04:30.82

Here the difference is not huge yet, but in our case, single SQL will take an hour to finish, while
PL/SQL only take 5 min. The bigger the table test, the huger the difference.

And the reason is simple, as there will be 40% of rows needs to be updated, Oracle will pick full
table scan of table test(a huge table). While in PL/SQL, "UPDATE TEST SET COL1=:B2 WHERE ID=:B1"
will be able to use index.

Any comments will be highly appreciated Received on Mon Dec 10 2007 - 08:51:11 CST