Re: Straight SQL always put perform PL/SQL?

On Dec 10, 3:51 pm, dshprope..._at_gmail.com wrote:
> 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

1 No version
2 analyze table is obsolete since 9i.
dbms_stats is needed
3 the id column is null allowed.
As the subquery can result in null this will taken into account when the execution plan is determined
4 using CBO, an IN subquery quite often produces better results 5 post the explain plan.

--
Sybrand Bakker
Senior Oracle DBA